Log Shipping and Logins

Establishing a failover server for your SQL Server? First things, first: strategize on your logins.

• By Eric Johnson
• 06/28/2010

So you decided to set up log shipping in order to establish a failover server, but what's next? Log shipping only takes care of keeping a user database in sync from one server to the other. In the event of a real disaster, there are several other components on your SQL Server that you need to account for in order to facilitate a true failover. You need to make sure you give some thought to logins, linked servers, SQL Agent jobs, credentials, proxies and anything else that exists outside of your user databases that could affect the applications that use the SQL Server. Some of these are pretty simple and others can be a little trickier, but they are all things that can be dealt with.

Let's look at logins, as they are arguably the most important piece that you will need to keep in sync. If you have Windows logins, the login SID in SQL Server will match the SID from Active Directory. So all you need to do is create the same login on both your primary and secondary server.

SQL Server logins are a little more complicated, as the SQL Server assigns the SID when the login is created. Why does the SID matter? All database users are linked to logins by SID; if the SID of a login is different on the secondary server, the database users tied to that login will be orphaned. This can be fixed, but it's better to avoid the problem all together.

Enter, a handy little stored procedure called sp_help_revlogin. You can find the code for this procedure on Microsoft's support site by just doing a quick Web search. Just be sure to grab the correct version for SQL Server. Once you have it, you simply run it on your primary to generate all the create statements for your logins, both Windows and SQL Server. For each Windows Login you get code like this:

CREATE LOGIN [ejohnsonmobile\eric] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

This will allow you to just recreate the login on the secondary server. For each SQL Server Login you will get something like this statement:

CREATE LOGIN [analyst] WITH PASSWORD = 0x0100A7A0020EEAB8D0B17D28883AA17A00D197575B2F55CEC9EE HASHED, SID = 0x7FC18E2B10F5FE4CA127E9837FCE5D4C, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Notice that the statement contains the encrypted version of the password and the SID. This means that when run on the secondary server, the password will match the SID and we will not have any issues after the failover.

So how do you keep logins in sync over time? We here is where you can get creative. You can manually run sp_help_revlogin from time to time in order to ensure that you have all the newest logins on your secondary. Alternatively, you can write a little script that calls sp_help_revlogin via a job, stores the output and then dynamically runs the create statements.

A couple things to be aware of:

sp_help_revlogin generates CREATE statements, so if a particular login already exists, the statement will fail. This is not an issue for a Windows login but might be for a SQL Server login. If you have already created an analyst login, but the password on the primary changes, the CREATE statement will fail and the login on the secondary will still have the old password.

Also, it is possible that another login on the secondary server is using the SID that your new login is attempting to use. In either case, you need to be more creative. You can delete all the SQL Server logins and recreate them each time with your sp_help_revlogin output, or you can write some more dynamic code and change the CREATE statements to ALTER statements if the login already exists.

As for an actual SID conflict, that will take a little more troubleshooting and creative scripting.

Finally, the CREATE statement also specifies a DEFAULT_DATABASE parameter. If the default database happens to be one of the user databases that is being Log Shipped, then the CREATE will fail. This happens because the user database is offline in a restoring state on the secondary and cannot be specified as the default database. You can fix this by using a default database that is always online, like master, or dynamically changing the create statements to use a different default on the secondary server.

How you proceed with logins syncing will depend on how many logins you have, what type of logins you have, and how often the logins change. Each environment is different, so take a close look at what you have and then make a decision. Even developing a process whereby you manually run sp_help_revlogin once a month may be just fine in your case. In any event, test your process thoroughly, including an actual database failover test, before you put anything into production. The wrong time to find out that you have a login issue is right after an actual production failure.