Automate SQL Logins to DR Site

There are many ways to setup a DR site like Log Shipping, Peer to Peer Replication, Mirroring, or even backups over the WAN.  I won’t get into the methods here, but I want to discuss a common issue that many DBAs simply forget.  Once you have your DR setup running, have you tested it?  Well you better have!  It’s just as important as testing restores of your backups.  Did you remember to re-create your logins on the DR server?  Hopefully you did or when you tested your DR plan I bet you found out, didn’t you?  Let’s talk about not only how to create those logins on the DR server, but how to automate it so new logins don’t get left behind.

Windows Logins vs. SQL Logins

Windows logins are easy because you don’t have to worry about SIDS and Passwords.  Active Directory handles that for you.  If you want to automate copying Windows logins it’s very easy to create an agent job that executes a stored procedure or straight SQL query, grabs the login name from sys.syslogins, and writes a create statement.  Since AD handles the SID and Password you don’t have to worry about the login being orphaned from the database user.

SQL Logins are not so easy because SQL assigns each login a SID upon creation and you also have to make sure the passwords are the same.  These values are stored in sys.syslogins but it’s the hashed value stored in a varbinary data type.  In order to automate this you have grab those values and do some data massaging to get them into a text form to provide to your create statement.  If you don’t match the SID the database user gets orphaned and if you don’t match the password your application can’t login.

Some Options

Microsoft has an article HERE that creates two stored procedures to help with this task.  The first SP does a hexadecimal conversion for the SID and password hash values and the second one scripts out the create statements.  The problem I have is that I want this automated, so in a DR situation it is one less thing I have to be concerned about.

The other option is the “Transfer Logins” task in SSIS.  This works well, but not everyone runs SSIS in every environment.  Also if the password changes on the account it does not update it.  In this case you would have to delete the login on your DR site and re-run the task.

How to Automate Logins to Your DR Site

I was 95% done writing my own stored procedure to handle both Windows and SQL logins when I ran across a snag.  I turned to Twitter and the #sqlhelp hash tag for advice.  I received a reply from Robert Davis @SQLSoldier (Blog|Twitter) that he had some code that would work and was supplied with his Pro SQL Server 2008 Mirroring book.  I told him that I had already tried that code, but it only handled Windows logins.  Well it turns out that the publisher has the old code for the procedure so he blogged and provided the new one HERE.  It does not handle changing the passwords for accounts that already exist, but it would be very easy to make that change yourself.  This is one brilliant piece of code, particularly how he uses XML to handle the SID and password hash.  His procedure also handles explicit permissions added to the login as well as any server roles it has been assigned.  If you are looking to automate your logins I highly suggest using his code.  Thanks Robert!