Wednesday, April 16, 2008

Scripting SQL Authentication Logins

Last week, one of my development dba friends informed an issue. We all know that one of the development dba tasks involved in developing a database is to keep the databases at the development, QA, Staging etc environments synchronized. Here, he has the development database to be shipped to the client staging server. The way he does the syncing is scripting all the objects and run them at the destination. (This is something the client insisted on). Anyways, he found an issue; when he scripted the SQL authentication login, the password that came is a cryptic one. So when he runs the script at the destination, he gets a different password! He had not seen the issue earlier, because he had created the login manually in the dev and QA machines. We thought of creating the login manually, but that would make the client uneasy because the client wants everything as a script only. We discussed, and found a solution. There are a couple of Microsoft recommended stored procedures to create logins using a "hashed" password. This is the link to the article - http://support.microsoft.com/kb/918992
Following is the work around we used to recreate the SQL authentication logins. (Assume that the name of the SQL authentication login we are using for the application is “AppLogin”.) We ran the scripts in the article on a SQL Server 2005 machine that has a SQL Authentication login with name “AppLogin” created with the standard password we wanted to use for your production, QA and development instances. The script created two stored procedures - sp_hexadecimal and sp_revlogin. Executed the stored procedure sp_revlogin as follows EXEC SP_HELP_REVLOGIN @LOGIN_NAME = AppLogin This generated the login creation script as follows CREATE LOGIN [AppLogin] WITH PASSWORD = 0x010031D72F18EC95B59D6B3682E46BFD7A6CD70B34F8783ECEBB HASHED, SID = 0x2B94B0D0C3BCCA4DA04C916FA3A4B3FD, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF Note the “HASHED” keyword. This shows that the hexadecimal text that is passed is hashed; while creating the login using this script, SQL Server will automatically take the password corresponding to the hashed value. Once we generated the script with the stored procedures, we included the script in our migration package so that a login with name “AppLogin” and the standard password will be created without having the password hard coded. Just note that, the SID for the login is hardcoded. If you want, you can remove the “SID = 0x2B94B0D0C3BCCA4DA04C916FA3A4B3FD” to ensure that there will not be a conflict with another login with the same SID. I am not sure whether any service pack level differences will generate different hashed value; we can probably test this and confirm.

No comments: