How do I move a SQL login from one server to another without the password?

13

March 25, 2013 by Kenneth Fisher

This is an uncommon task but one that does turn up every once in awhile. A SQL login has to be moved from a development server to a test server, test to prod etc. Or maybe a lateral move to a new server. And frequently the DBA doesn’t and/or shouldn’t know the password.

The first thing to realize is that SQL Server uses hash encryption for its passwords. The password is taken and scrambled up using a hash algorithm. Then when a user tries to log in, SQL takes the input and hashes it using the same algorithm and checks to see if the hashed input matches the hashed password. There is no time (to my knowledge) when the password is “un-hashed” and turned back to an understandable string. All of this means that in order to copy the password intact from one server to another we have to get the “hashed” version of the password and create the login with it. Making sure to tell SQL that it is in fact already hashed and there is no reason to do it again. Fortunately there is a way to do this.

The basic command to create a SQL login is

CREATE LOGIN MyLogin WITH PASSWORD = '<strong password>'

By adding the keyword HASHED to the end of the command we can pass in the hashed value in hex rather than a string.

CREATE LOGIN MyLogin WITH 
PASSWORD = 0x0100230BC24D34869B5E83240FD0202462F384A73E7516D8B50A 
HASHED

In order to get the hashed version of the password we can use the function LOGINPROPERTY(loginname, ‘PasswordHash’).

Last but not least, to make this as easy as possible, we need to be able to convert the output of LOGINPROPERTY, which in this case is going to be binary, to a string. This is so we can construct our command in one step.

CONVERT(varchar(max), LOGINPROPERTY(MyLogin, 'PasswordHash'),1 )

Using all of this together I wrote the following query to generate the script I needed.

 SELECT 'CREATE LOGIN MyLogin WITH PASSWORD = ' +
     CONVERT(varchar(max), LOGINPROPERTY('MyLogin', 'PasswordHash'),1 ) + 
     ' HASHED'

And this one in case I want to generate more than one command at once.

SELECT 'CREATE LOGIN '+name+ ' WITH PASSWORD = ' +
     CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'),1 ) + 
     ' HASHED'
FROM sys.server_principals
WHERE name in ('MyLogin','MyLogin2')
  AND type = 'S'

Once it’s run we copy and paste the commands into a query window pointing to the new server, execute, and we are good to go.

EDIT: Quick change to the above queries. The CONVERT to varchar should have a style of 1 not 2. I’m not sure why 2 worked on the server I tested on but from what I’m reading in BOL and tests on other servers the style should be 1.

Also here is a version of the last query that will generate a script to create the new login with the same SID. This way if you are moving a database with a user that is associated with the login you won’t have to “fix” the user to link them back together again. I talked about this in my blog Logins vs Users.

SELECT 'CREATE LOGIN '+name+ ' WITH PASSWORD = ' +
     CONVERT(varchar(max), LOGINPROPERTY(name, 'PasswordHash'),1 ) + 
     ' HASHED, SID = ' + CONVERT(varchar(max), sid, 1)
FROM sys.server_principals
WHERE name in ('MyLogin','MyLogin2')
  AND type = 'S'
About these ads

13 thoughts on “How do I move a SQL login from one server to another without the password?

  1. Microsoft already provides a solution to this http://support.microsoft.com/kb/246133/en-us
    (This article was previously published under Q246133). The advantage of Microsofts solution is that even the usersids are scripted.

    • Actually I’ve used that before on several occasions. The downside to it is that it requires installing code on your server which isn’t always allowed/possible. Also for the particular use I was dealing with (moving a database & associated appid from test to prod) it would be rather like using a sledge hammer to put in a nail.

      That being said it’s a very handy script and one that DBAs should be aware of. I should have included it in my original post, so thanks for mentioning it!

  2. Bugbait says:

    Interesting, I ususally restore a database with that login and treat it like an orphan, not nearly as eligant!
    EXEC sp_change_users_login ‘Auto_Fix’, ‘MyLogin’

    • I’ve certainly used sp_change_users_login before. Honestly I’m rather aggravated that they have decided to get rid of it. And they haven’t replaced the ‘report’ functionality without having to write a query.

      • Bugbait says:

        Hmm, fly in ointment, this will only work when the SQL login already exists on the new server
        otherwise you need to recreate the account with the password
        EXEC sp_change_users_login ‘Auto_Fix’, ‘MyLogin’, NULL, ‘the-password-you-shouldnt-know’

        Why would they get rid of ‘report’ it was so useful!

      • Your right sp_change_users_login is really most useful if the login already exists. My first version of the query will give you a login where you have to “fix” the user by using sp_change_users_login. The second version actually uses the same sid so the user will match up with no problems.

        Also they didn’t really get rid of the functionality. It’s really a fairly simple query. Approximately

        SELECT dbuser.name, *
        FROM sys.database_principals dbuser
        WHERE sid NOT IN
        (SELECT sid
        FROM sys.server_principals)
        AND type NOT IN (‘R’,’U’)
        AND name ‘guest’

        You can even get rid of the U and include windows users in the query which the REPORT function doesn’t appear to support.

  3. […] couple of months ago I talked about moving a login from one server to another without the password. The basis behind this is creating the login using the hashed version of the password. Using the […]

  4. Don Schaeffer says:

    Wish I knew about this a while back when migrating databases to a new server.

  5. […] that when you restore a database from another server the SIDs for windows logins will match and the SIDs for SQL Logins probably won’t match unless you planned for this ahead of time. Note that if you are using a contained database (2012+) then this isn’t true for loginless […]

  6. Ashok Tripathi says:

    Thank you Ken! This made my job easy!

    • Glad you liked it. You might also look at my sp_srvpermissions SP. It’s primarily for security research but I also have create/remove commands for logins in there as well. ANd sp_dbpermissions does the same thing at the user/db level.

  7. […] transferring SQL Server passwords using the password hash a couple of different times. (Here and Here) and of course I use it in my sp_SrvPermissions script. So SQL Server stores the passwords for SQL […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 423 other followers

%d bloggers like this: