SQL Login Enforce Password Policy

Question

I have a 3 node cluster running windows 2003 x64 sp1 and SQL Server 2005 version 9.00.2153.  My problem is the following...

This Saturday I migrated a web application's database to this server.  After restoring the database I created the sql login for the service account, set the password and disabled the password policy for this login.  I then ran sp_change_users_login to attach the already existing db user with the same name to the login.  I changed the connection string for the application, tested the application connectivity and functionality then detatched the old database on the old server.  Everything went like clockwork, no problems at all. 

Come Monday morning at 8:35 I started getting alerts that the web site was down.  I tested the site and sure enough it was down.  I then attempted to connect to the database server using the login that was created for the app and the connection failed.  I logged in with my ID and got in fine.  Nothing showing to be wrong with the DB, I checked the new login and somehow the "Enable Password Policy" had been set for the new login.  I disabled it and still no connection.  I went to the database and checked the DB user and somehow the link between it and the login no longer existed.  I reran the sp_change_users_login and restested the web site and verified that that web site was back online. 

My question is this, is there any stored proc that resets these values back to default for some reason, a series of events that might revert the "Enable Password Policiy" to the default for a login, or is there a particular domain level operation that might occur such as Security Polcies that would affect these settings in SQL? 

No one else was on the machine when I went to check it out at 8:40 so it has left me puzzled.

Any help would be appreciated.

Thanks.

Zach

Answer 1

  I am not aware of anything that may turn on the password policy as a side effect, and I just run some tests with sp_change_users_login and it seems to behave as expected.

 

  I would suggest monitoring the usage of this account for a little while, and try running the following statement to make sure that the password policy is still defined as you specified:

SELECT * FROM sys.sql_logins WHERE name = 'login_name'

 

   If you detect any unexpected changes on the is_policy_checked column, some operation between your checks may be the cause of this change in the password policy. Once you can detect what operation (or combination) is the one causing these changes, we can try to figure out the reason for this side effect.

 

  Thanks a lot,

  -Raul Garcia

   SDE/T

   SQL Server Engine

Answer 2

I have had a very similar situation occur in the last 24 hours.

windows 2003 and SQL Server 2005 version 9.00.2153.

We had to do a database restore yesterday and when we finally had everything back up and running ( which involved a Windows reboot as well) we logged into the primavera application to test it was ok. this was fine ... jobs a good un' !!!

Until 7:35 am this morning when we started to receive failed logins for 2 individual SQL authenticated accounts. neither of which had their passwords changed.

however this morning the database logins showed as having "enforce password policy" checked. which shouldnt have been the case for these logins.

in the end i unchecked the policy and reset the passwords and access to the system was resumed as normal.

is their something within Active Directory or Windows Server that can change a SQL servers user credentials ?
not sure whats happened here but would be interesting to hear what anyone has to say on it.

thanks
Paul

http://msdn.microsoft.com/en-us/library/ms161959.aspx
http://technet.microsoft.com/en-us/library/ms144284.aspx

i believe its down to an API within windows 2003 called "NetValidatePasswordPolicy". the windows server itself forces password policy on SQL Server Logins as well as windows authenticated logins.

might be worth checking the windows server logs to see if any group policy executed between the time you restored your server and when you logged in the following morning.

i found the following in my Application Log :
Source : SceCli
Security policy in the Group policy objects has been applied successfully.



let me know what you think....cheers.