Clone a SQL Server login and password to a new server


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




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

 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

 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

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

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

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

 

 

Nice tips Really I faced this issue in past.


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

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

Encrypting passwords for use with Python and SQL Server

Understanding SQL Server fixed database roles

How to configure SSL encryption in SQL Server

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





get free sql tips
agree to terms


Learn more about SQL Server tools