Creating SQL 2005 Logins during installation

Hello,

We use one standard account with a password that is never given out.

In SQL 2000 we had a script to extract the password in encrypted format so that it could be run as part of the installation process.

That same process does not work for SQL 2005.

Below is a sample of the script that we were deploying for SQL 2000 installations.
-
-- Login: CPAPP

declare @.pwd varchar(50);

SET @.pwd = CONVERT (varbinary(256), 0x01003402EC1BDADF45C9D788C23459BC36D73E5B2B9F2F235138F6BB8D0CD2317FCBA41EB59D191801AC287A14FF) EXEC master..sp_addlogin 'CPAPP', @.pwd, @.defdb = 'CPSQL' , @.sid = 0xE5994FEF661AF842A0CA38AAFEB4360F, @.encryptopt = 'skip_encryption'

SET @.pwd = CONVERT (varbinary(256), 0x01003402EC1B9DA21489EC2D47CA9B8549255E9C9ACD4260661DFE04E9A0AFA631A27676C7A79E2DE20A52265666) EXEC master..sp_addlogin 'CPREPORT', @.pwd,@.defdb = 'CPSQL' , @.sid = 0xD7E65E9AE7E87F4F9C72929A0B37F35C, @.encryptopt = 'skip_encryption'

SET @.pwd = CONVERT (varbinary(256), 0x0100A44D0526FABFF90D4CA524FA6415DD998256EB62963309D5F561AC5B116318E4F93D2159D9BAC95F356EBDC2) EXEC master..sp_addlogin 'cpdata', @.pwd, @.defdb = 'CPSQL' , @.sid = 0xBE1AC61A2A8D8543ACAF2D403AAD96A4, @.encryptopt = 'skip_encryption'

-
The procedure that we are using to script the logins is from the Microsoft support page:
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

Is this what we should be using for SQL 2005?

Please advise.

Thanks in advance

IN SQL 2005 you should be using

CREATE LOGIN loginname PASSWORD ='hased password' HASHED , SID ='.....'

Have a look at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/eb737149-7c92-4552-946b-91085d8b1b01.htm

|||

Simon,

Thanks for your prompt reply.

We were able to run the script and create a login with a encrypted password successfully using "HASHED"

I tried connecting to the server using SQL Management studio by entering the newly created username and the encrypted password (long mix of chars and numerical) in the Connect to Server dialog box in the SQL Server authentication mode.

But we got the error (Microsoft SQL Server, Error :18456)

A couple of quick questions:

1) Is there a way to extract the encrypted password and check our script is working fine by connecting to server --programatically

2)How can we test connection to server using the newly created login name and encrypted password

Are we missing something ?

Could you please throw light on this?

Thanks in advance

|||Probably easiest to ceate your users again usnig the real password and then script them. From there, not sure if the Hash alogorithm has changed.|||

1) You cannot connect knowing only the hash, you need to know the password from which the hash was computed (or some other password that would collide with the original for the same hash value, but chances of finding such a password are slim).

2) To test the connection, you need to know the actual password and use it in the connection test.

The hashing algorithm has not changed, it's still SHA1. What has changed is that in SQL Server 2000, we actually kept two hashes - one for the actual password, the other for the password converted to uppercase. In SQL Server 2005, we dropped the second hash because it was making the hash vulnerable to a dictionary attack (the attack was described in http://www.nextgenss.com/papers/cracking-sql-passwords.pdf). How could this change impact you? It would impact you if you had set the password using a special casing, for example, "PasSworD", but you used to connect with "Password" and forgot the original casing over time. After moving the hash to SQL Server 2005, you must use the original password casing. Other than this, I can't think of any other changes.

Thanks
Laurentiu