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 scripts. 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

You have to do

Alter login login_name with CHECK_POLICY = { ON | OFF }
Alter login login_name with  CHECK_EXPIRATION = { ON | OFF }

Refer: http://technet.microsoft.com/en-us/library/ms189828.aspx