How to set CHECK_POLICY OFF in the sql script

Question

While installing our software in a HP laptop, we got a SQL error stating "The password does not meet windows policy requirements because it is too short."

When I checked, the local security policy in my machine has minimum 8 characters and in that laptop it has 12 characters. Our SQl password has 11 characters. That is why it is not installed in that laptop alone.

We can increase the password more than 12. But maybe in future the minimum password requirement can even change to 20 characters. So we thought of disabling the password check. Since I am new to this SQL, I don't know where to add the condition check. I have two SQL Script. The code snippet for the 2 scripts is shown below. Please let me know where to add it.

Script1:

ALTER LOGIN [sa] WITH PASSWORD=N'MSSql2008!'
GO

IF EXISTS (SELECT * FROM syslogins 
                         WHERE name = 'teradyne')
BEGIN
    ALTER LOGIN [teradyne] WITH PASSWORD=N'SQL_PWD' 
END

GO 

Script2:

GO

EXEC ('IF NOT EXISTS (SELECT * FROM syslogins 
                         WHERE name = ''clientsoftware'')
        EXEC sp_addlogin @loginame=''clientsoftware'', @passwd=''TER_SQL_PWD'' ')
GO

CHECKPOINT

GO

Where SQL_PWD = software1! for both scripts.

Please let me know where to add that CHECK_POLICY and also whether I need to add that CHECK_EXPIRATION

Answer 1

For the ALTER LOGIN statement, you can use

ALTER LOGIN [teradyne] WITH PASSWORD=N'SQL_PWD' , CHECK_POLICY = OFF

You can't do it with the sp_addlogin procedure, and CREATE LOGIN is the preferred method.

If you need to use sp_addlogin, you could pre-hash the password, but I wouldn't recommend that to a beginner. See http://technet.microsoft.com/en-us/library/ms173768.aspx and http://technet.microsoft.com/en-us/library/ms189828.aspx