Subject: Trasfer SQL Logins with original passwords and SIDs
Scope: the technical applies to SQL server 2008 and later.
When we upgrade or migrate an SQL server instance to a new instance, if we have lots of SQL logins the migration process will get bogged down in 2 thorny issues:
- SQL Login SIDs
- SQL Login passwords
SSIS Transfer Logins Task can easily transfer the windows logins and groups but not for SQL logins. When using SSIS Transfer Logins Task to transfer the SQL logins it will generate new passwords and SIDs for the transfered SQL logins and these logins are disabled in the destination SQL server instance. This is not very useful for the real work.
When an SQL login is created in the new SQL server instance, a new SID is created and bound to the Login. In this case, the database user in the migrated database is no longer mapped into the new created SQL login as they are using different SID now.
Generally speaking, the SQL user can be remapped by using Alter User with login = [login_Name] or sp_change_users_login (deprecated SP, better to use Alter User). However, when there are lots of SQL logins this technical becomes cumbersome. Particularly, for some environment, a production database might frequently be restored to a test environment then you need to fix the SQL user mapping all the time.
For the SQL login password, it is also every awkward for us to retype the password by creating a new SQL login in the new server instance.
Although the SQL login SIDs and password are stored in the old instance master database, before SQL server 2008 there is no an easy way to script out this binary information and transfer to a new instance. There are a few very intelligent techniques available in the SQL community and mainly it will use a complicated function to convert the binary data into string information and then convert it back to binary data in the destination instance. But in SQL server 2008 or later, these techniques became kinds of overkill.
In SQL server 2008, the COVERT function has added a new feature to convert binary data type to other data type. (check the book online for more details) The syntax as below:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Example:
CONVERT(varchar(max), 0xE4305FD31D353546B4EC6E56C906E912, 1)
When Expression is binary(n) or varbinary(n), 3 style options are available 0 (default), 1, and 2.
When use 0 (default), binary bytes will be translated to ASCII characters. Each byte is converted 1:1.
When use 1, binary bytes will be translated to character string and the characters 0x will be added to the left of the converted result
When use 2, binary bytes will be translated to character string and 0x prefix will not be used.
SQL login SIDs and password_hash are both use 0x prefix, so we can use the binary style 1 to script out the SIDs and Hashed password from the old server instance and apply to the new instance.
The code to script out the SQL logins with the convert function becomes very simple, an example script as below:
Select
'Create Login ' + QUOTENAME(A.name)
+ ' With Password=' + CONVERT(varchar(max), A.password_hash, 1) + ' hashed' --script out the passwrod
+ ', DEFAULT_DATABASE=' + quotename(A.default_database_Name) --if you apply the scrip to a mirroed server or log-shipped server user master database, as user database is not in useable state
+ ', DEFAULT_LANGUAGE=' + quotename(A.default_language_Name)
+ ', CHECK_POLICY=' + Case A.is_policy_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', CHECK_EXPIRATION=' + Case A.is_expiration_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', SID=' + CONVERT(varchar(max), A.SID, 1) --script out the SIDs
As SQLLogin
From
sys.sql_logins A
Where A.name Not like '##%##' --remove those system generated sql logins
And A.sid != 0x01 --SA sid is always same
(Note: If you have problem to copy and paste the script into the SSMS, copy it and paste to Office Word first and then paste to SSMS.)
You can use this scrip to store the SQL Login creation script to a table in the target server and use a cursor or loop to execute these scripts to transfer the SQL logins.
In the next blog, I will talk more depth on how to use an SSIS package to transfer the Logins and SQL server permissions from one server to another.