The last few weeks I've been working on disaster recovery procedures
for my organization. We review them at least yearly to ensure we've
taken into account new applications, changes to the infrastructure,
etc. This sort of thing is supposed to be done after any change, and
our change control procedures handle that, but it's always a good idea
to double check the procedures as a separate practice. After all, this
is one area you don't want to make a mistake in.
One of the situations any SQL Server DBA may face is that there will be
less hardware available in a disaster recovery situation. After all,
each additional server purchased for a secondary operating location or
placed on a disaster recovery contract is extra cost for the
organization. Therefore, a smart organization tries to reduce this cost
like it would any other expense. If an organization can get away with 1
SQL Server in the first few days of a DR situation when it would
normally operate with 5, the organization will do so and plan on
increasing capacity after critical systems are brought on-line. This
can put the DBA in a quandry as he or she consolidates the contents of
these 5 SQL Servers onto 1.
There are a lot of challenges to this, but one of the biggest is
ensuring all needed logins are created. Windows logins aren't a big
deal. Execute the appropriate sp_grantlogin and things go
swimmingly well. Restore the user databases and the SIDs match up.
Since the passwords are on the Windows-side, there isn't anything else
for the DBA to worry about. But SQL Server-based logins are a totally
different story. Some third party applications have a set
password that cannot be changed. Sometimes this password is created by the application when it
is installed, meaning no one knows what the password actually is. A couple of the applications I deal with have this
problem. Therefore, a DBA can't just execute an sp_addlogin
command with any old password and get things to work with the
application. The application doesn't allow for the password to be changed.
That means the correct password must be set. But if you don't know what
it is to begin with, does that mean you have to crack the password?
No, it doesn't. That's time-consuming and ultimately unnecessary.
Another issue is matching up the SID between the login and the user.
Yes, running sp_change_users_login can get the SIDs to match up
again, but that's unnecessary, too. In SQL Server 7.0 and SQL Server
2000, both the password and the SID is stored in the syslogins system table. If there was a way to extract this information and build a script using it, we'd be all set. After all, sp_addlogin
allows for us to set the SID and the password (even in an encrypted
state). And Microsoft has provided a solution in the following
Knowledge Base article:
How to transfer logins and passwords between instances of SQL Server (246133)
The KB article has a script which creates two stored procedures: sp_hexadecimal and sp_help_revlogin. These get added to your master database. When you execute sp_help_revlogin (which uses sp_hexadecimal), it'll create a script that recreates all the logins on the SQL Server in question. An example output is the following:
/* sp_help_revlogin script
** Generated May 20 2006 9:40PM on TESTSQL */
DECLARE @pwd sysname
-- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'
-- Login: builtin\users
EXEC master..sp_grantlogin 'builtin\users'
-- Login: TestUser
SET @pwd = CONVERT (varbinary(256),
0x0100B7745C01DBEE94BE4032E5A8382A75A6C334DBE408884646976A9B723EC1B6B8D43D596BC2DE97DEA7C4005F)
EXEC master..sp_addlogin 'TestUser', @pwd, @sid = 0x813E48ED68B83C438FA9DF3D1CBFD70D, @encryptopt = 'skip_encryption'
Looking at the output, you've probably noticed that it doesn't set the
default database and language. Some applications aren't smart enough to
change the database. In a disaster recovery situation this is not
something you want to be troubleshooting. That means you want to go
ahead and set the database and language when you create the login. That
is easily remedied with a script
like the following:
SELECT 'EXEC sp_defaultdb [' + name + '], [' + dbname + ']; ' +
'EXEC sp_defaultlanguage [' + name + '], [' + language + '];'
FROM syslogins
WHERE name <> 'sa'
All of the generated login scripts can be combined into a set of
"master" login scripts to run in a DR situation. Keep in mind that these scripts need to be
protected because even though the SQL Server login passwords are
encrypted, there are tools that can crack them. Therefore, treat them
with the appropriate care.
I'll be blogging more about disaster recovery and SQL Server in the
coming weeks. Let me take this opportunity, though, to plug an old
friend, Chris Kempster.
Chris has written an excellent eBook on SQL Server disaster recovery
and it is provided free by Quest Software: SQL Server eBook: A Practical Guide To Backup, Recovery, and Troubleshooting. You will be required to create a login on the Quest site. You can find a review of the eBook here on sql-server-performance.com.
