Clone a SQL Server login and password to a new server

By: Bhavesh Patel   |   Updated: 2017-01-26   |   Comments (10)   |   Related: > Security

Problem

I have a SQL Server 2014 instance ServerA and I need to setup a SQL Server login on ServerB with the same credentials as ServerA for testing.  I don't have the password, but I need to keep the password the same on both servers.  How can I clone a SQL Server login onto another server and keep the same password?

Solution

In my scenario, the login testlogin on ServerA already existed, but to show each step I will create a new login for better clarification.

Create New Login Using SSMS on ServerA

In the SSMS Object Explorer expand Security and select Create New Login.  Here I am using SQL Server authentication and creating a new login testlogin.

Generate Login Script to Use on ServerB

On instance ServerA, right-click on the login testlogin and choose Script Login as > CREATE To > New Query Editor Window.

This will generate a script like below.

USE [master] GO
/* For security reasons the login is created disabled and with a random password. */
CREATE LOGIN [testlogin] WITH PASSWORD=N'8ic8fZq1PEbEbG3qyaQpn1ih/IrJeBW0MzcKU0KDQVI=', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF
GO
ALTER LOGIN [testlogin] DISABLE
GO

Apply Login Script on ServerB

I applied this script on instance ServerB and did not include the DISABLE line as shown below.

USE [master] GO
/* For security reasons the login is created disabled and with a random password. */
CREATE LOGIN [testlogin] WITH PASSWORD=N'8ic8fZq1PEbEbG3qyaQpn1ih/IrJeBW0MzcKU0KDQVI=', 
DEFAULT_DATABASE=[master], 
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION=OFF, 
CHECK_POLICY=OFF
GO
--ALTER LOGIN [testlogin] DISABLE
--GO

The script ran successfully on ServerB, but the login for the application failed. In the comments of the code, you can see that a random password was generated, so this is why this did not work.

Process to Create a Working Login

I then deleted the login testlogin on ServerB and started with this approach.

Connect to instance ServerA using SSMS and open a new Query window and run the below query in the master database. Also, review the comments I put in for each section of code.

/*SUSER_SID() - Returns the SID of the current security context*/
USE [master]
SELECT SUSER_SID('testlogin')
GO

/*This is the SID value in varbinary for login testlogin
0xE7F3C36B478F5A4A96F179210CFF39C5 */


/*LOGINPROPERTY('testlogin','PASSWORDHASH') - Returns the hash of the password*/
USE [master]
SELECT LOGINPROPERTY('testlogin','PASSWORDHASH')
GO

/*This is the password hash for login testlogin
0x020019814E12C5DCBE7D55C803E46D9CD7E349C9F9000BC392759E0CFD0CF98AA5A3D88B1A725F660A82FE7CAEAECA34E49AC5F08C188F5EF5DB99B06EC1E290EBFF4DF10EF1 */

Now use the testlogin SID and the password hash string value as shown below and then run the script on instance ServerB to create the login with the same password.

CREATE LOGIN [testlogin] WITH PASSWORD = 0x020019814E12C5DCBE7D55C803E46D9CD7E349C9F9000BC392759E0CFD0CF98AA5A3D88B1A725F660A82FE7CAEAECA34E49AC5F08C188F5EF5DB99B06EC1E290EBFF4DF10EF1 HASHED, 
SID = 0xE7F3C36B478F5A4A96F179210CFF39C5, 
DEFAULT_DATABASE = [master],
DEFAULT_LANGUAGE=[us_english], 
CHECK_EXPIRATION = ON, 
CHECK_POLICY = ON

On instance ServerB the login testlogin was created and a login by the application was made successfully.   I still don't know the password, but at least the application is working with the password that is hardcoded into it.

Next Steps

• Check the login SID to see it exists before running on destination server.
• I tested with SQL 2014 Developer edition, but this should work with other versions.  Just test the code in a test environment prior to production.
• Please test this code in development or QA environment before rolling out to production.
• Check out these other Security tips.

About the author

Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips