Automatically Transfer and Synchronize SQL Server logins, SIDs, Passwords and Permissions

April 5, 2012 at 3:54 AMSteven Wang

Subject: to transfer and synchronize SQL server logins, SIDs, SQL Login Passwords and permissions from one server instance to another by using SSIS package

This technical applies to: SQL server migration, Primary server and second server login synchronization including SIDs, Passwords and permissions transfer. SQL server 2008 and later is applicable.

  

1. Real world Issues

It is too often when a principal server failover to a mirror server, the SQL server logins are found is not totally in sync with the principal server. This is same to the log-shipping servers, replicated sites and other DR scenarios like Backup/restore databases from one server to another.

As I mentioned in my last post: A simple Way to Migrate SQL Logins from One Instance to Another, it is not convenient to transfer server logins from one server to another. 

2. Login Synchronization Consideration

It will be nice if we can have an automated process or script to transfer logins from one server to another including SIDs, passwords and permissions.

Typically, to transfer sql server logins from one server to another automatically, the following needs to be considered:

A) Login SIDs (either windows logins or SQL logins) in Destination server but not in source, these logins in destination server need to be dropped

B) SQL logins have different passwords between Source server and destination server, the SQL Logins in destination server needs to be altered

C) Login names in Destination server not in source server need to be altered with same name as in source

D) Logins in Source server but not in destination server, these logins in source server need to be synchronized from source to destination server.

E) Logins with different Is_Disabled value between source and destination. The destination logins need to be altered.

F) Logins’ server role assignments need to be synchronized from source to destination.

G) Login permissions need to be synchronized from source to destination.

H) There are others need to be considered like default language etc. but the above mentioned are most important ones.

I have transformed these requirements into the SSIS tasks as below:

A) Drop logins in destination where SIDs are different

B) Drop logins in destination where passwords are different (Drop is simpler than alter.)

C) Drop logins in destination where names are different (Drop is simpler than alter)

D) Check and Sync Logins

E) Alter logins in destination where Is_Disabled are different

F) Check and Sync role members

G) Check and sync permissions

The SSIS package lay out as below:

 3. SSIS Package design

The package contains 7 containers and each container has contained 3 tasks: a Data flow task, A ForEach Loop task, and a Execute SQL script task.

The general idea is to use the data flow task to generate the SQL script and store the query text in a recordset destination which assigns to an object variable: Query_Dataset; the ForEach task use an ADO enumerator to loop through the Query script records in the Query_Dataset variable and assign it to a string variable:SQL_Query; the Execute SQl script task runs the string query variable to apply the change.

The variables have been setup as below:

 A) Drop logins in destination where SIDs are different Task Design

The data flow task: Get login with SIDs diff task has the below design:

This is use merge join to find out any SIDs in Destination but not in Source. The conditional split is to split out any NULL SID value from the Login source in the merge Join component.

The derived column component is used to generate the DDL query like: “Drop Login MyLoginName;”

The query is stored in the Query_dataset where Query_Dataset variable is configured.

 

B) Drop logins in destination where passwords are different task design

The data flow task: Get Login with Password diff task design is depicted as below:

C) Drop logins in destination where names are different Task Design

A Login with same SID in Source and destination server could have different name if Login name in source server is altered. Therefore, these logins need to be synced to destination. To simplify the process, I simply drop those logins in destination and reimport from the source again later.

The data flow task: Get login with Name diff is depicted as below:

 

D) Check and Sync Logins task design

In this task I will use the SQL script I have created in my last post to generate the Create Login DDL queries.

The query will script out the source login SIDs and also passwords if they are SQL logins.

The data flow task: get server login diff task is depicted as below:

 

The script will script out both windows logins and SQL logins but will leave out the R = Server role, C = Login mapped to a certificate, and K = Login mapped to an asymmetric key. As in SQL server 2008, server role is fixed. The C and K are more complicated and I just want to keep things simple here.

The script in Login source is as below:

Select Upper(A.name) As Name,
Case When A.type IN ('G', 'U')
Then
'Create Login ' + QUOTENAME(A.name) 
+ ' From Windows With DEFAULT_DATABASE=[Master]' --+ quotename(A.default_database_Name)
+ ', DEFAULT_LANGUAGE=' + quotename(A.default_language_Name)
When A.type IN ('S')
Then
'Create Login ' + QUOTENAME(A.name) 
+ ' With Password=' + CONVERT(varchar(max), B.password_hash, 1) + ' hashed'
+ ', DEFAULT_DATABASE=[Master]' --+ quotename(B.default_database_Name)
+ ', DEFAULT_LANGUAGE=' + quotename(B.default_language_Name)
+ ', CHECK_POLICY=' + Case B.is_policy_checked when 0 then 'OFF' When 1 Then 'On' End 
+ ', CHECK_EXPIRATION=' + Case B.is_expiration_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', SID=' + CONVERT(varchar(max), A.SID, 1)
End As SRVLogin
From sys.server_principals A
Left Outer Join
sys.sql_logins B
On A.name = B.Name
Where A.type  Not In ('R', 'C', 'K')
And A.name Not like '##%##'
And A.sid != 0x01

 E) Alter logins in destination where Is_Disabled are different

This task is used to keep the is_disabled value synced between source and destination. The data flow task: get login with IsDisable diff is depicted as below:

F) Check and Sync role members task design

This task is used to sync the server role members between source and destination. I get both role name and its members from source and destination servers. A full join component is used to find out which role members for a server role is in source but not in destination and vice versa. For those logins in a server role in source but not in destination, then an sp_addsrvrolemember script will be created. In contrast, for those logins in a server role in destination but not in source, an sp_dropsrvrolemember script will be created.

Both scripts will be combined and save into the Query_Dataset variable. The data flow task: Check role Member task is depicted as below:

 

G) Check and sync permissions task design

This task is used to sync the login permissions from source to destination. This query is used to script out the login permissions:

Select distinct
A.name
, B.Permission_Name
, B.state
, B.state_desc
From sys.server_principals A
Inner Join
sys.server_permissions B
On 
A.principal_id = B.grantee_principal_id
Where A.type  Not In ('R', 'C', 'K')
And A.name Not like '##%##'
And A.sid != 0x01

A full join is used to find out which permissions are applied in source for a login but not in destination and vice versa.

For those permissions are applied in source for a login but not in destination then those logins will go to the Permission_Applied route. And the below expression is used to create the permission grant script:

(DT_WSTR,2000)([state_desc source] + " " + [Permission_Name source] + " TO [" + [name source] + ([state source] == "W" ? "] WITH GRANT OPTION;" : "];"))

For those permissions are applied in destination for a login but not in source then those logins will go to the Permission_Removed route. And the below expression is used to create the permission grant script:

(DT_WSTR,2000)("REVOKE " + [Permission_Name dest] + " TO [" + [name dest] + "];")

The data flow task: Check Permissions task is depicted as below:

 

4. SSIS Package Automation

An SQL agent Integration service job can be used to automate the Login synchronization process. The procedure is to create a job to use the package. 

For example, a job name can be: Apply Login Changes from Source to Destination.

And Remember to set your source and destination server variable value to your server names like the below:

 

Once the job is set up you can schedule the job to run automatically in your preferred time intervals.

You can also create a server level DDL trigger to automatically run the job whenever an DDL command is performed on logins. An example of the trigger like:

Create Trigger trigger_ApplyLoginsToDestination
On All Server
After Create_Login, Alter_Login, Drop_Login
As
Exec msdb..sp_start_job 'Apply Login Changes from Source to Destination' --change to your own job name
Print 'You have synced your login changes from source to destination server.'

You can download this package from the attachment in this post.

Thanks for reading.

 

SQLServer_Login_Sync_Blog.zip (47.20 kb)

Comments (16) -

Hi,

In my environment the instance name are different on Principal and Mirror Server. This is causing the package to fail as the corresponding NT service user names are different.

for ex:
Principal : NT SERVICE\MSSQL$UATCSDB01_DB
Secondary : NT SERVICE\MSSQL$UATCSDB02_DB

Please confirm on any alternative for this.

Thanks in advance.

Regards,
Animesh

Reply

Hi Animesh,

For your situation, you can modify the connection manager: MasterDB_Destination and MasterDB_Source not to use the expression in the package. Instead, you can change the connection string for both to sue your username and password.

I believe this will work.

With regards,


Steven

Reply

can you explain more on this what you mean by expression &  string in your statement

Reply

could you quote exactly which paragraph you were referring to? I was not able to exactly catch what you want.

thanks

Reply

linux vps
United States linux vps says:

Good day. Extremely nice blog!! Man... Excellent ... Superb ... I will bookmark your website and take the feeds additionally...I'm satisfied to find numerous useful information here in the article. Thank you for sharing...
<a href="http://www.alphavbox.com">linux vps</a>

Reply

Christophe
Canada Christophe says:

Very good article and blog... We have the same kind of problem to synchronize linked servers... Is there a way to get SQL logins passwords stored in linked servers definitions?

Thanks!

Reply

best virtual server
United States best virtual server says:

Useful information shared. I am very happy to read this article..Thanks for giving us nice info. Fantastic walk-through. I appreciate this post.

Reply

I am getting following error while trying to add into a project. Is there any idea what I am missing?

********

===================================

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.
(Microsoft Visual Studio)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)
   at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events)
   at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.<>c__DisplayClass4.<LoadPackageFromFile>b__3(String password, IDTSEvents events)
   at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageWithPassword(PackageLoader loader, IWin32Window dialogParent, String packageName, Boolean askOptionalPassword, String& packagePassword, DtsProtectionEventListener errorListener)
   at Microsoft.DataTransformationServices.Controls.PackageProtectionUtils.LoadPackageFromFile(String file, IWin32Window dialogParent, Boolean askOptionalPassword, PackageUpgradeOptions packageUpgradeOptions, String& packagePassword, DtsProtectionEventListener errorListener)
   at Microsoft.DataTransformationServices.Controls.PackageLocationControl.LoadPackage(DtsProtectionEventListener errorListener, PackageUpgradeOptions packageUpgradeOptions, String& packagePassword)
   at Microsoft.DataTransformationServices.Project.Controls.AddExistingPackageForm.AddExistingPackage(AddExistinngPackageEventListener eventListener)
   at Microsoft.DataTransformationServices.Project.Controls.AddExistingPackageForm.AddExistingPackageForm_FormClosing(Object sender, FormClosingEventArgs e)

===================================

The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails.


------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.LoadPackage(String FileName, Boolean loadNeutral, IDTSEvents100 pEvents)
   at Microsoft.SqlServer.Dts.Runtime.Application.LoadPackage(String fileName, IDTSEvents events, Boolean loadNeutral)

Reply

Hi Siva,

I suspect that you were trying to load the SSIS package under SSIS 2005 version. I just to clarify that this is only working with SQL server 2008 or later.

Thanks.



Steven

Reply

linux virtual server hosting
India linux virtual server hosting says:

This is nice post which I was awaiting for such an article and I have gained some useful information from this site. Thanks for sharing this information.
feel free to visit us  http://www.alphavbox.com/

Reply

Snorri Kristjansson
Iceland Snorri Kristjansson says:

Thank you very much for sharing this. I had some problems editing the package in Visual Studio 2010. I could not change the source and destination servers names no matter what I tried. Solved that by creating new connections for source and destination servers in connection manager and replacing the connection used everywhere in the package. This is now working fine in my environment. Great solution, thanks again Smile

Reply

UltrasoundTech
United States UltrasoundTech says:

Just like the several causes described above you have many choices to make international currency exchange. <a href="theultrasoundtechnician.com/.../">What Kind Of Degree Do I Need To Be An Ultrasound Technician</a>. The recipient can use the identical transfer code to withdraw the income. You need to have not fear utilizing a funds transmitter for moving cash just as you must not worry working with a bank.

Reply

How do I edit the data sources in the download package? After you change and save everything back to the default value of Source and Destination ...

Regards
Marek

Reply

Ali Khawaja
Saudi Arabia Ali Khawaja says:

Hi Stephen - great great work.  really nice.  excellent work.  

i wanted to get your opinion whether this will be appropriate solution for alwayson enviornments as well as we need to sync users over there as well.

Regards
Ali

Reply

Thank you very much for sharing, Stephen.  It is one solid solution to tackle the login sync issue between SQL instances.  I have tried the solution and it works great in SQL 2014 as well.

Reply

steven wang
Australia steven wang says:

Thanks Mike for your comments.

cheers.





steven

Reply

Pingbacks and trackbacks (1)+

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading