Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    2

    Unanswered: Create Logins with hashed password + must_change

    Hello,
    I am trying to import SQL Server 2000 logins to SQL Server 2005 with the passwords hashed and the MUST_CHANGE option set.
    The import of the login with the password works fine but the MUST_CHANGE option is ignored.
    We need the user to change the password the first time after the upgrade.
    Not sure why the MUST_CHANGE option is ignored or a good work around.

    Here is the code:
    CREATE LOGIN [TESTSQL]
    WITH PASSWORD =
    0x0100F8305D5A553F37D5284C3B4D16712FEBFA95D2A85135 0E7C553F37D5284C3B4D16712FEBFA95D2A851350E7C HASHED
    MUST_CHANGE,
    DEFAULT_DATABASE = MASTER,
    CHECK_EXPIRATION = ON ,
    CHECK_POLICY = ON ,
    SID=0x753C82292F365A46A830F50C8A079D3F


    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The MUST_CHANGE modifier is actually processed by the client application, such as SSMS (SQL Server Management Studio), not by SQL Server itself. While the documentation says that SQL Server will prompt for a password change, this isn't strictly true because the SQL Server doesn't actually run on the client machine.

    The first time a user logs in using a client that supports MUST_CHANGE, then the "force password change" functionality will be implemented. If the only clients that the users use to connect to SQL Server, then this functionality will stay set in the database but will never take effect.

    -PatP

  3. #3
    Join Date
    Sep 2008
    Posts
    2
    Thanks Pat,
    I am testing using SQLCMD. When I create the login with a pail text password I get prompted in SQLCMD to change the password but when I create the login with a hashed password then SQLCMD doesn't prompt me to change the password and I authenticate and the system allows me to login.

    (This way I get prompted to change the password in SQLCMD)

    CREATE LOGIN [TESTSQL] WITH PASSWORD= 'password'
    MUST_CHANGE,
    CHECK_EXPIRATION = on ,
    SID=0x753C82292F365A46A830F50C8A079D3F

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Pat Phelan
    The MUST_CHANGE modifier is actually processed by the client application, such as SSMS (SQL Server Management Studio), not by SQL Server itself...
    SQL Server calls NetValidatePasswordPolicy API every time there is an attempt to log on to the instance by the login that was created with CHECK_POLICY flag set to ON. The client application, regardless of which one it is, receives an informational message that the policy is violated if corrective action can be taken by the client app, or an error message if the account is locked/expired.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •