Need sp_addlogin with CHECK_POLICY = OFF

Hi

We are using SQL Server-2005 as backend,

i had code like,

sp_addlogin 'a', 'a'

this give error as

"Password validation failed. The password does not meet Windows policy requirements because it is not complex enough"

as this is not full fill the Windows Password policy.

I too check the Local Security Settings is disable for the password settings.

I know i can use

CREATE USER <user> , CHECK_POLICY =OFF

but i want to set CHECK_POLICY =OFF, with the

sp_addlogin stored Procedure.

Any help,hint or code to solve this ?

Regards,

Thanks.

Gurpreet S. Gill

You cannot disable password policy when using sp_addlogin. You should use CREATE LOGIN instead, as sp_addlogin is deprecated and will be removed in a future version of SQL Server.

Thanks
Laurentiu

|||

I am trying to Migrate SQL Logins and passwords from A SQL2000 to 2005 system (aprox 20,000 systems !!)

Picked up some code here..

http://support.microsoft.com/default.aspx/kb/246133

which is fine but this targets sp_addlogin because the password can be declared as already encrypted. [However my Customers historiclly haven't been so hot on strong passwords in the past. We intend to enforce policy going forward and encourage NTLogins, but there is a period of grace after upgrade to SQL2005] so I also need CHECK_POLICY = OFF

so sp_addlogin is no good for me. Looking at the spec for Create Login it allows me to specify that a password has already been hashed ... (whereas sp_addlogin password options are already encypted or already encrypted with a previous version)

so my question does Hashing in SQL2005 use the same .. encryption algorithm as in SQL2000 ? and how far back can I go (I believe 6.5 Encryption is different to SQL 7 / 2000) I must say the documentation for sp_login if read literally ... encrypted in a previous version would suggest that when adding a login in SQL2005 should use the skip_encryption_old as my encryptopt !!

Thanks

|||

this is ONE year old post, why not use new post, will help you much.

Gurpreet S. Gill

|||

You can use CREATE LOGIN with a hashed password (see BOL for more information on the HASHED option: http://msdn2.microsoft.com/en-us/library/ms189751.aspx).

For example:

CREATE LOGIN [Login1] WITH PASSWORD = 0x… HASHED

go

When you create a login using a hashed password, the password policy cannot be checked for complexity on such password (because the system cannot get the original password back, the only information available is the hash), but will be used from that point on (i.e. the next time you change the password).

I recommend reading Laurentiu’s blog article regarding SQL Server 2005 password hashes:

http://blogs.msdn.com/lcris/archive/2007/04/30/sql-server-2005-about-login-password-hashes.aspx

If you have any further question, please let us know. Thanks.

-Raul Garcia

SDE/T

SQL Server Engine