Clone a SQL Server login and password to a new server


By:   |   Updated: 2017-01-26   |   Comments (8)   |   Related: More > 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.

Create 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.

Generate login Script

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.


Last Updated: 2017-01-26


get scripts

next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips
Related Resources





Comments For This Article




Tuesday, September 08, 2020 - 4:54:52 AM - Robert Back To Top (86432)
Thank you very much! It works like a charm!

Friday, March 03, 2017 - 5:54:51 PM - Geordy Back To Top (47101)

 This was very helpful, but it wasn't until I scrolled all the way to the right that I noticed that you have to include the word "HASHED" after the password.

I would suggest making a clear note about this important fact.

 

Thank you for your article.

 

 


Friday, March 03, 2017 - 12:15:08 PM - Brian Hubartt Back To Top (47073)

 I use this Microsoft option that works really well...

 

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

 


Friday, March 03, 2017 - 4:00:45 AM - Perry whittle Back To Top (47040)

The whole point is that passwords should not sync across environments.

and if your application is hard coding passwords, it's probably time for a more secure, robust application.


Thursday, January 26, 2017 - 12:10:56 PM - Bhavesh Patel Back To Top (45593)

Thanks @rob and @zhunya We have a way for powershell module for such kind of jobs.
But actually I didn't go for this apporach because source and destination server is different one and required to perform manually steps and not have powershell module.
But sure on automation kind of job We should use powershell way.


Thursday, January 26, 2017 - 10:11:57 AM - Rob Back To Top (45589)

You can also use the dbatools PowerShell Module to do this in one easy step

You can find about this command here

https://dbatools.io/functions/copy-sqllogin/


Thursday, January 26, 2017 - 4:43:37 AM - K.B Patel Back To Top (45579)

 

 

Nice tips Really I faced this issue in past.


Thursday, January 26, 2017 - 4:35:38 AM - Zhunya Back To Top (45578)

Better use Powershell module DBATOOLS ( https://dbatools.io/ ) set of Powershell functions for migrating, testing for best practice configuration, and just doind possibly any DBA work just with one cmd'let.

To clone login - Copy-SqlLogin -Source sqlserver -Destination sqlcluster -Logins netnerds

Greate time-saver module



download





Recommended Reading

Enabling xp_cmdshell in SQL Server

Understanding SQL Server fixed database roles

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Encrypting passwords for use with Python and SQL Server

Implementing SQL Server Security with Stored Procedures and Views








get free sql tips
agree to terms