none
Production site is not functioning due to an User Login Password expire.

    Question

  • Hi All,

    SQL Server 2005

    We have an issue with user login password expire. Generally we create logins without password expire but the user is unable to login to the server and getting password expire error.

    a) What would be the reasons behind this scenario.

    b) How to resolve this issue.

    c) How can we avoid this issue in future.

    I'm really grateful to your valuable suggestions on this. Thank You.

    Regards,

    Kalyan.


    ----Learners Curiosity Never Ends----

    Tuesday, December 24, 2013 3:03 AM

Answers

  • You must have applied password policy for that account. If you don't want that feature, you can disable password expiration for SQL Logins.

    USE [master]
    GO
    CREATE LOGIN [myLogin] WITH PASSWORD=N'pass@word1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
    GO
    


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, December 24, 2013 4:08 AM
  • Hi,

    In addition, you can use ALTER LOGIN (Transact-SQL) to configure the enforcement of password policy options of a SQL Server login.

    When CHECK_POLICY is changed to OFF, CHECK_EXPIRATION will also be set to OFF. The following combinations of policy options are not supported:

        If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.

        If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.

    More information, please see policy enforcement section in the following TechNet article:

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

    Thanks.


    Tracy Cai
    TechNet Community Support

    Wednesday, December 25, 2013 3:31 AM

All replies

  • You must have applied password policy for that account. If you don't want that feature, you can disable password expiration for SQL Logins.

    USE [master]
    GO
    CREATE LOGIN [myLogin] WITH PASSWORD=N'pass@word1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
    GO
    


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Tuesday, December 24, 2013 4:08 AM
  • It seems password policy enforcement is enabled. <o:p></o:p>

    Disable the login and unclick the 'enforce password policy' and 'enforce password expiration'<o:p></o:p>

    Also, might be possible account is locked. If yes than use below link to enable the account

    http://www.mssqltips.com/sqlservertip/2758/how-to-unlock-a-sql-login-without-resetting-the-password/


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, December 24, 2013 9:59 AM
  • Hi,

    In addition, you can use ALTER LOGIN (Transact-SQL) to configure the enforcement of password policy options of a SQL Server login.

    When CHECK_POLICY is changed to OFF, CHECK_EXPIRATION will also be set to OFF. The following combinations of policy options are not supported:

        If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.

        If CHECK_POLICY is set to OFF, CHECK_EXPIRATION cannot be set to ON. An ALTER LOGIN statement that has this combination of options will fail.

    More information, please see policy enforcement section in the following TechNet article:

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

    Thanks.


    Tracy Cai
    TechNet Community Support

    Wednesday, December 25, 2013 3:31 AM