Install SSL Certificates in a Windows Server

Reading Time: 4 minutes

About SSL

SSL Certificates secure the web today using Transport Layer Security (TLS). This is a network protocol which encrypts the data between the web server and the visitor. Most websites today are using SSL, and you can see this every time a website starts with https:// instead of http://. This indicates the website is securely encrypting data between you and the server so that no attackers can easily sniff the network packets and capture your logins.

SSL protects the web today and is utilized by almost every corporation and business and acts first step in user security. SSL is a way to protect logins and forms that you enter from being intercepted unknowingly by a 3rd party on your network.  If your website does not incorporate SSL, we suggest implementing it as soon as possible, and you can use our guide to do it!

 

Generating the Certificate Request (CSR)

Before ordering your SSL, you will need to create a certificate request for the certificate authority to issue an SSL.

Step 1: To begin, the first thing you need to do is open IIS. If you do not have a shortcut for it, you can search your computer for inetmgr.exe and open it that way. From here you will click on your server name:

Step 2: Then double-click “Server Certificates“.
Step 3: Once you have done that, you are ready to create your certificate request.

On the right-hand side, select ‘Create Certificate Request

At this point, you will be asked for information about the certificate and the company requesting the certificate.
This info of your the company or org. is needed to fill out a CSR request.

Once you have filled this out, click Next.

It will bring you to the following screen:

Step 4: We suggest using the settings above, making sure the Bit Length is set to 2048 or higher. We like to go with 4096 and click Next.  On the subsequent screen, you need to specify a filename where your Certificate Request or CSR can be exported. For simplicity, we would like to export the CSR to C:\example.com.csr.txt

 

Ordering the SSL

At this point, you are ready to order your SSL certificate!

Step 1: Go ahead and go to your chosen SSL provider, whether it be GlobalSign, Liquid Web, or any numerous other certificate authorities.

Step 2: When you are signing up for the SSL, it will ask you for the CSR data we saved at C:\example.com.csr.txt – Copy and paste the contents into the certificate authorities website and it will generate all the same fields we entered in the previous steps.

Step 3: Finish your order, and they will provide you with a .crt certificate file. Download this file and copy it to your web server. For simplicity, copy it to C:\example.com.cer

Great! Now you have created a certificate request and completed it with the certificate authority and have your new SSL certificate ready to be installed.

 

Installing the Certificate in IIS

Step 1: Open up IIS/inetmgr.exe and navigate to the server as we did in the beginning.

Step 2: Navigate to Server Certificates. Now, instead of selecting ‘Create Certificate Request’ you will select ‘Complete Certificate Request

Step 3: It will prompt you for the location of the new certificate, which we saved at C:\example.com.cer and to make things easier on ourselves later, we will name the friendly name example.com-01 so that we know this is the first SSL for this domain in case we want to renew it later.  Once you hit OK, you should see your certificate in the list of server certificates in IIS.

Great! Now you have generated the certificate request, completed it, and installed your certificate on your web server. Now you need to bind the certificate to your website.

 

Binding the SSL Certificate to a Website

Step 1: In IIS, browse to Sites > example.com (where you want the SSL certificate installed).

Step 2: Right-click on your site and select ‘Edit Bindings‘ or if you click on the site, you will see Bindings on the right-hand side.

This will open a window that looks like the following:

Step 3: If you already have the https binding setup for your site, you will simply double-click on the https bindings and select the desired SSL certificate from the drop down. If you haven’t created an https entry in your bindings already, click Add. On the right-hand side and you will see the following window:

Step 4: First, set the Type to https so your website knows the request is for a secured URL.

You will want to set the IP Address as needed based on your host. In my case, All Unassigned.
Port should be automatically set to 443, if not, do so. (This is the port defined for secured communications.)

Step 5: Set the Host Name to example.com (your domain). In most cases, you will want to check Require Server Name Indication. In our case, we do not need it because this is the only certificate on this IP address. Select your SSL certificate from the drop-down!

Select OK and do it all again, this time instead of setting the host name to example.com, you will want to set it to www.example.com. This is because we only set it up for requests from https://example.com, but https://www.example won’t register as secured until we add the second binding entry.

Note:
If you are setting up a wildcard SSL, you will want to add a third entry for *.example.com so that it can secure any subdomain of your website.

Testing Your New SSL

First, you will want to access your domain at https://example.com and https://www.example.com to see if there are any errors. An easy way to tell if the certificate is functioning properly is to input your domain into SSL Shopper. Try it with and without the “www” to confirm both work. If everything is working, you should see several green checks and no errors. The certificate expiration date will be at least one year from the day you ordered the SSL originally.

That’s it! You have successfully installed a brand new SSL for your website that works both with www and without it. Congratulations! Now you can follow these steps to secure all of your websites and applications.

Liquid Web makes it easy to purchase new SSLs. Simply log in to manage.liquidweb.com, clicking Add, and select SSL Certificate.  Here you can simply input the CSR you generated in Step 1, and it will order an SSL and give you back the certificate file needed to complete the installation.

If you have a Core-Managed Windows server at Liquid Web, we can help you through this process and diagnose any issues you may have run into. We also assist with SSL on Self-Managed if they buy the SSL from Liquid Web.

 

Plesk to Plesk Migration

Reading Time: 9 minutes

Migrating from one Plesk installation to another is easy with the Plesk Migrator Tool! The Plesk team has done a great job creating an easy to use interface for migrating entire installations of Plesk to a new server.

If you need to move files, users, subscriptions, FTP accounts, mail and DNS servers setup through Plesk, this guide will help you successfully navigate the process and come out victorious!

We will be splitting this tutorial into three sections:

Continue reading “Plesk to Plesk Migration”

HIPAA Compliant Hosting Checklist

Reading Time: 5 minutes

HIPAA Compliance

In this guide, we outline the essential requirements for HIPAA compliant servers and how Liquid Web helps fulfill these necessities.

 

HIPAA, or the Health Insurance Portability and Accountability Act of 1996, was passed by Congress to protect sensitive user information related to health insurance. This act helps to reduce health care fraud and mandates a standard for handling confidential healthcare information for consumers and businesses.

HIPAA compliance protects this sensitive information and specifies proper guidelines and standards for handling health insurance data. HIPAA also establishes rules for handling, administering, and maintaining electronic servers as well as the hosting of this Protected Health Information. Read more here.

Key Terms and Important Information:

  • HIPAA – Health Insurance Portability and Accountability Act of 1996
  • PHI – Protected Health Information
  • Access Control – To limit who can log in or access sensitive PHI data. Access control helps provide accountability for authorized usage and access to servers with confidential information. HIPAA requires that all users are uniquely identifiable and that the server hosting PHI data is only accessible to specifically authorized users and entities.
  • Audit Control – To log and record hardware, software and procedural work done to maintain and repair HIPAA compliant servers and data centers. HIPAA requires accurate and uniquely accountable logs for the type of work performed, what was accessed, and by whom. This notation is closely related to access control by limiting maintenance to authorized and uniquely identifiable persons or entities, but also refers explicitly to logging any maintenance of physical hardware or server software.  
  • Facility Access Control – To limit physical access to the data center from unauthorized or unaccountable persons. This control makes sure that only designated workers have access to physical servers containing PHI. Liquid Web’s data centers are HIPAA compliant and properly limit access to all servers.

 

To be HIPAA compliant, you must have firewalls in place. Most of the time, compliant hosting will implement hardware, software, and application level firewalls to protect the server from unauthorized users. This security applies to Access Control as well as Transmission Security, which protects PHI from unauthorized access.

HIPAA regulations state the firewalls must be system-wide. The firewall implementations are part of the requirements for limiting access to personal information stored on the server. Firewalls that are properly setup will limit or prevent accessibility from anyone who should not have access, often using explicit whitelists and blacklists. This setup prevents unauthorized employees, clients, or hackers logging into servers with sensitive data.

To be allowed through the firewall your users must have a uniquely identifiable username or identification that has been explicitly allowed access permission.  At Liquid Web, our networking team is at hand to secure your server with hardware firewalls, while our support team is ready to protect sensitive PHI data with software firewalls.

 

HIPAA compliance requires that remote access to the server through an encrypted VPN tunnel. This VPN protects data entering into the tunnel with an encrypted session that lasts only as long as the session exists. Work done between the remote workstation and the server is protected from interception via this encryption. At Liquid Web, our VPN services are automatically encrypted in order to protect your data.

 

Password management is an essential part of HIPAA compliance. Safeguarding passwords and isolating them to identifiable users is integral to the protection of sensitive data. Using multi-factor authentication is highly recommended for this process.

Multi-Factor Authentication forces users logging into the secured server system to use both a password and another form of authentication, such as a mobile device, verifying their identity for granting intended access. Authenticating makes it much more difficult for hackers and unauthorized users to use stolen or brute force-acquired login credentials to access the server, as the user will have to do a secondary verification from a device that is unique to them.

Many companies utilize Google Authenticator which allows your users to have a phone app to use as their secondary verification method. Multi-Factor Authentication falls under Access Control.

 

If you want to be HIPAA compliant, your server cannot be on shared hosting. You must have a server that cannot be accessed by any other business or entities, which means it needs to be private or dedicated to your business. This isolated includes requiring a private IP address that is not used by another entity.

By running on shared hosting, you are breaking HIPAA compliance by allowing non-authorized users access to the server. Hosting with Liquid Web gives you your own private, dedicated server strictly used by your business.

HIPAA requirements for limiting user access and having proper authentication. The server itself must also exist within a HIPAA compliant data center. Liquid Web has a high-security, HIPAA compliant data center that all of our clients are hosted within, falling under Facility Access Control.

 

An SSL certificate must protect any part of your website where sensitive information can be accessed.  An SSL provides end-to-end encryption for the accessed data and logins used, to further protect access to the server. HIPAA defines PHI as Protected Health Information and anywhere that a user can access PHI must be protected with SSL.

For more information about SSL and how it works, click here.

 

A BAA is necessary for HIPAA compliant hosting as it designates the role of the hosting company and defines responsibility for different parts of HIPAA compliance. It does not resolve your business of its HIPAA related duties, but it represents the roles that your business and the hosting company partake.

This Business Associate Agreement allows a hosting company the necessary access to servers to maintain them, while still preventing any other businesses’ unauthorized access to Protected Health Information.

See our HIPAA BAA policy here.

 

HIPAA compliance requires that all Protected Health Information must have an exact backup ready for restoration. These backups must also be located offsite and not on your server for recovery in the event of disaster or server malfunction. At Liquid Web we have two solutions for this, Guardian and DPM Backups.

By having an offsite backup, you are protecting the Protected Health Information and ensuring that no data loss will occur on restoration. Fully restoration is often achieved with continuous backups notating any changes of information on the server.

Read more about our different backup services here.

 

To be HIPAA compliant, the appropriate methods are necessary for getting rid of hardware. This disposal usually requires that the data be wiped entirely and destroyed in a manner that will not allow for restoration.

Data destruction is typically peer-reviewed and documented to state precisely the method of destruction. This process is to prevent any future use of the hardware from being able to recover sensitive PHI data.  Often called Integrity Control it ensures that data is properly altered or destroyed.

 

All logins and maintenance must be fully documented. Any repairs on the physical servers must be logged, especially those related to the security of the server and who logs in to servers for software maintenance and reviews and applies to Audit Control.

At Liquid Web, all of our work is logged and appropriately recorded with HIPAA compliant standards.

 

HIPAA compliance is an integral part of your business. While it can be confusing, our technicians at Liquid Web can ensure you that your Protected Health Information is appropriately handled and follows HIPAA compliant standards. While we have only reviewed a portion of the requirements of HIPAA compliance, feel free to reach out to our HIPAA Specialists for more information about how we handle our data centers and servers.

If you would like to speak with a HIPAA Specialist, start here.

SQL Databases Migration with Command Line

Reading Time: 3 minutes

What if you have dozens of SQL databases and manually backing up/restoring each database is too time-consuming for your project? No problem! We can script out a method that will export and import all databases at once without needing manual intervention. For help with transferring SQL Logins and Stored Procedures & Views take a look at our MSSQL Migration with SSMS article.

1. Open SSMS (Microsoft SQL Server Management Studio) on the source server, log in to the SQL instance and open a New Query window. Run the following query:

SELECT name FROM master.sys.databases

This command will output a list of all MSSQL databases on your server. To copy this list out, click anywhere in the results and use the keyboard shortcut CTRL+A (Command + A for Mac users) to select all databases. After highlighting all the databases right click and select copy.

2. Open Notepad, paste in your results and delete all databases (in the newly copied notepad text) you do NOT wish to migrate, as well as deleting the following entries:

  • master
  • tempdb
  • model
  • msdb

These entries are the system’s databases, and copying them is not necessary. Make sure to delete everything except explicitly the databases you need to migrate.  You should now have a list of all required databases separated by a line. i.e.

  • AdventureWorks2012
  • AdventureWorks2014
  • AdventureWorks2016

3. Save this result on the computer as C:\databases.txt.

4. Create a new Notepad window, copy/paste the following into the document and save it as C:\db-backup.bat

mkdir %systemdrive%\dbbackups
for /F "tokens=*" %%a in (databases.txt) do ( sqlcmd.exe -Slocalhost -Q"BACKUP DATABASE %%a TO DISK ='%systemdrive%\dbbackups\%%a.bak' WITH STATS" )

5. Now that you’ve saved the file as C:\db-backup.bat, navigate to the Start menu and type cmd and right click on Command Prompt to select Run as Administrator.Type the following command:

cd C:\

And hit enter. Afterward, type db-backup.bat and hit enter once again.

At this point, your databases have begun exporting and you will see the percentage progress of each databases export (pictured below).

Command line shows the process of each database that is exported.

Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that may have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error by modifying the existing C:\databases.txt file to include only the failed databases and re-run db-backup.bat until all databases are successfully exported.

 

By now you have the folder C:\dbbackups\ that contains .bak files for each database you want to migrate. You will need to copy the folder and your C:\databases.txt file to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. The folder on the C drive of the destination server should be called C:\dbbackups . It’s important to accurately name the file as our script will be looking for the .bak files here. Be sure that the destination server has your C:\databases.txt file as well, as our script will be looking for the database names here.

 

1. Open a Notepad and copy/paste the following into the document and save it as C:\db-restore.bat

for /F "tokens=*" %%a in (C:\databases.txt) do (
sqlcmd.exe -E -Slocalhost -Q"RESTORE DATABASE %%a FROM DISK='%systemdrive%\dbbackups\%%a.bak' WITH RECOVERY"
)

2. Save the file as C:\db-restore.bat 

3. Navigate to the Start menu and type cmd.

4. Right click on Command Prompt and select Run as Administrator. Type the following command:

cd C:\

and hit Enter. Now type db-restore.bat and hit Enter.

Your databases have now begun importing. You will see the percentage of each databases restoration and the message “RESTORE DATABASE successfully processed” for each database that has been successfully processed.

Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error (you can change the batch file as necessary), modify C:\databases.txt to include only the failed databases and re-run db-restore.bat until all databases are successfully exported.

Congratulations, you have now backed up and restored all of your databases to the new server. If you have any login issues while testing the SQL connections on the destination server, refer to the Migrating Microsoft SQL Logins (anchor link) section of this article and follow the steps therein. To migrate views or stored procedures please refer to the Migrating Views and Stored Procedures section. Every SQL server will have it’s own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.

 

SQL Database Migration with SSMS

Reading Time: 9 minutes

Migrating MSSQL between servers can be challenging without the proper guidelines to keep you on track. In this article, I will be outlining the various ways to migrate Microsoft SQL Server databases between servers or instances. Whether you need to move a single database,  many databases, logins or stored procedures and views we have you covered!

There are many circumstances where you will need to move a database or restore databases. The most common reasons are:

 

  • Moving to an entirely new server.
  • Moving to a different instance of SQL.
  • Creating a development server or going live to a production server.
  • Restoring databases from a backup.

 

There are two main ways to move SQL databases. Manually with Microsoft SQL Server Management Studio (SSMS) or with the command line. The method you choose depends on what you need to accomplish. If you are moving a single database or just a few, manually backing up and restoring the databases with SSMS will be the easiest approach. If you are moving a lot of databases (think more than 10) then using the command line method will speed up the process. The command line method takes more prep work beforehand, but if you are transferring dozens of databases, then it is well worth the time spent configuring the script instead of migrating each database individually. If you aren’t sure which method to use, try the manual approach first while you get comfortable with the process. I recommend reading all the way through for a deeper understanding of the methodology.

 

Useful References for Terminology

SSMS – An acronym for Microsoft SQL Server Management Studio.

Source Server – The server or instance you are moving databases from or off.

Destination Server – The server or instance you are moving databases to.

 

Moving SQL databases with the manual method can be very easy. It is the preferred process for transferring a few or smaller databases. To follow this part of the guide, you must have MSSQL, and Microsoft SQL Server Management Studio (SSMS) installed.

 

1. Begin by logging into the Source server (the server you are moving databases from or off of). You will want to open Microsoft SQL Server Management Studio by selecting Start > Microsoft SQL Server >  Microsoft SQL Server Management Studio.

2.Log into the SQL server using Windows Authentication or SQL Authentication.

3. Expand the server(in our case SQL01), expand Databases, select the first database you want to move (pictured below).

Select your database within Microsoft SQL Server Management Studio.

4. Right click on your database and select Tasks then click Back Up.

Back up button in Microsoft SQL Server Management Studio.

5. From here you are now at the Back Up Database screen. You can choose a Backup Type such as Full or Differential, make sure the correct database is selected, and set the destination for the SQL backup. For our example, we can leave the Backup Type as Full.

6. Under Backup Type, check the box for “Copy-only backup.” If you are running DPM or another form of server backup, backing up without the Copy-Only flag will cause a break in the backup log chain.

7. You will see a location under Destination for the path of the new backup. Typically you will Remove this entry then Add a new one to select a folder that SQL has read/write access. Adding a new Backup Destination shows a path similar to the following:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\

This C:\ path is where your stored database backup is. Note this location for later reference, as this is the default path to stored backups and will have to have proper read/write access for SQL services.

Note:
Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.

8. Next, append a filename to the end of this path such as AdventureWorks2012-081418.bak – Be sure to end the filename with the extension .bak and select OKSet the file name with the .bak extension in Microsoft SQL Server Management Studio

10. Once you have pressed OK on the Select Backup Destination prompt, you are ready to back up the database! All you need to do now is hit OK, and the database will begin backing up. You will see a progress bar in the bottom left-hand corner, and when the backup is complete, a window will appear saying ‘The backup of database ‘AdventureWorks2012’ completed successfully.

Navigate to the destination path, noted earlier, (in this case C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\) you will see your newly created file (in this case AdventureWorks2012-081418.bak) – Congratulations! This file is the full export of your database and is ready to be imported to the new server.  If you have more databases, then repeat the steps above for each database you are moving. After copying all database process to the next step of restoring databases to the destination server.

 

You should now have a .bak file of all your databases on the source server. These database files need to be transferred to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. After copying a database you can store it on your destination server,  for our example, we have stored it on the C drive in a folder named C:\dbbackups .

1. Open Microsoft SQL Server Management Studio.

2. Log in to the SQL server using Windows Authentication or SQL Authentication.

3. Expand the server and right click on Databases and select Restore Database.

4. The Restore Database screen looks very similar to the Back Up Database screen.Under Source, you will want to select Device instead of Database. Selecting Device allows you to restore directly from a file. Once you’ve chosen Device, click the browse icon […]

5. Select Add, then navigate to the folder in which your .bak files lives. (In this case, C:\dbbackups).

6. Select the first database .bak you would like to restore and click OK.

Select the .bak file to import your database into the destination server via SSMS.

7. Click OK and now you are ready to import the database. Before importing, let’s take a look at the Options section on the left-hand side. Under Options, you will see other configurations for restoring databases such as Overwrite the Existing Database, Preserve the Replication Settings and Restrict Access to the Restored DatabaseIn this case, we are not replacing an existing database so I will leave all these options unchecked. If you wanted to replace an existing database (for example, the backed up database has newer data than on the destination server or you are replacing a development or production database) then simply select Overwrite the Existing Database.

Note:
Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.

8. Clicking OK  begins the restore process as indicated by the popup window that reads ‘Database ‘AdventureWorks2012′ restored successfully.’ You have migrated your database from the source to the destination server.

Repeat this process for each database that you are migrating. You can then update path references in your scripts/application to point to the new server, verify that the migration was successful.

 

After importing your databases if you are unable to connect using your SQL login, you may receive the error ‘Login failed for user ‘example.’ (Microsoft SQL Server, Error: 18456).‘ Because the database is in the Traditional Login and User Model, logins are stored separately in the source server and credentials are not contained within the database itself. From this point on, the destination server can be configured to use the Contained Database User Model which keeps the logins in your database and out of the source server. (You can read more about this here.)Until then, we will have to move and interact with the users as part of the Traditional model. Continue below to proceed with the migration of your SQL users.

Backing up and restoring the databases did move your SQL logins relation to the databases (your logins are still associated with the correct databases with the correct permissions) but the actual logins itself did not transfer to the new server.  You can verify this by opening SSMS on the destination server and navigating to Server > Security > Logins. You will notice that any custom SQL logins you created on the previous server did not transfer over here, but if you go to Server > Databases > Your Database (AdventureWorks2012 in this case) > Security > Users you’ll see the correct login associated with the database.

If you have one or two SQL users, you can just delete the user’s association to the database in Servers > Databases > AdventureWorks2012 > Security > Users, re-create the user in Server > Security > Logins and map it to the proper database.

If you have a lot of logins, you will have to follow an additional process outlined below. To migrate all SQL users, open a New Query window on the source server and run the following script:

SQL Login Script
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

This script creates two stored procedures in the source database which helps with migrating these logins. Open a New Query window and run the following:
EXEC sp_help_revlogin

This query outputs a script that creates new logins for the destination server. Copy the output of this query and save it for later. You will need to run this on the destination server.

Once you’ve copied the output of this query, login to SSMS on the destination server and open a New Query window. Paste the contents from the previous script (it should have a series of lines that look similar to — Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]) and hit Execute.

You have now successfully imported all SQL logins and can now verify that the databases have been migrated to the destination server by using your previous credentials.

Views and stored procedures will migrate with the database if you are using the typical SQL Tape backups. Follow the instructions below if you need to migrate views and stored procedures independently.

  1. Open Microsoft SQL Management Studio on the Source server.
  2. Log in to your SQL server.
  3. Expand the server and as well as Databases.
  4. Right click on the name of your database and go to Tasks > Generate Scripts.
  5. Click Next.
  6. We will change Script entire database and all database objects to Select specific database objects and only check Views and Stored Procedures.Transfer Stored Procedures and Views within Microsoft SQL Server Management Studio
  7. Click Next, notice the Save to File option. Take note of the file path listed. In my case, it is C:\Users\Administrator\Documents\script.sql – The path of saved views and stored procedures.
  8. Click Next >> Next >>Finish, and select C:\Users\Administrator\Documents\script.sql and copy it to the destination server.
  9. Go to the destination server, open SSMS and log in to the SQL server.
  10. Go to File > Open > File or use the keyboard shortcut CTRL+O to open the SQL script. Select the file C:\Users\Administrator\Documents\script.sql to open it.
  11. You will see the script generated from the source server containing all views and stored procedures. Click Execute or use the keyboard shortcut F5 and run the script.
Note:
Unfortunately, there is no built-in way to do this with the command line. There are 3rd party tools and even a tool by Microsoft called mssql-scripter for more advanced scripting.

You have now migrated the views and stored procedures to your destination server! Repeat this process for each database you are migrating. A little guidance goes a long way in database administration. Every SQL server will have it’s own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.

Looking for a High Availability, platform-independent SQL service that is easily scalable and can grow with your business? Check out our SQL as a Service product offered at Liquid Web. Speak with one of our amazing Hosting Advisers to find the perfect solution for you!