none
Dealing with Sql Server password expiration

    Question

  • I work with Sql Server 2005 Express eddition and VB.NET 2008 .

    the application I build work with the database , and a few days ago the password suddenly expired while being used by users.

    I want to prevent such a case,  where at least I will be able to set a new password programatically .

    any suggestions ?

    Tuesday, April 27, 2010 10:09 AM

Answers

  • This is set at CREATE LOGIN time, but can be set later by ALTER LOGIN.  To prevent the password from expiring:

    ALTER LOGIN YourApplicationLogin
       WITH CHECK_EXPIRATION = OFF

    For CREATE LOGIN, CHECK_EXPIRATION = OFF is the default.  However, if you use the SQL Server 2005 SSMS, it checks it own by default.  I do not know about the SQL Server 2005 Express SSMS, but it probably does the same.

    RLF

    • Marked as answer by Gilp1506 Tuesday, April 27, 2010 3:29 PM
    Tuesday, April 27, 2010 1:57 PM

All replies

  • This is set at CREATE LOGIN time, but can be set later by ALTER LOGIN.  To prevent the password from expiring:

    ALTER LOGIN YourApplicationLogin
       WITH CHECK_EXPIRATION = OFF

    For CREATE LOGIN, CHECK_EXPIRATION = OFF is the default.  However, if you use the SQL Server 2005 SSMS, it checks it own by default.  I do not know about the SQL Server 2005 Express SSMS, but it probably does the same.

    RLF

    • Marked as answer by Gilp1506 Tuesday, April 27, 2010 3:29 PM
    Tuesday, April 27, 2010 1:57 PM
  • By using ‘alter login’ command, It seems only switch the CHECK_EXPIRATION on or off.

    What if I want to set an explicit date of expiration?

    How can I do that?

    Tuesday, November 09, 2010 9:37 AM
  • By using ‘alter login’ command, It seems only switch the CHECK_EXPIRATION on or off.

    What if I want to set an explicit date of expiration?

    How can I do that?


    I guess no.. As per BOL - CHECK_EXPIRATION and CHECK_POLICY are only enforced on Windows Server 2003 and later versions.

    Which means its Windows feature and I guess you can't expire a password on given date.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Tuesday, November 09, 2010 10:49 AM
    Moderator