question

Samanthar-3682 avatar image
0 Votes"
Samanthar-3682 asked ErlandSommarskog commented

Security related Login password change

Hi All,

We want to give provision for SQL Authentication users to change their password for the first time and password should never expire and the user should be changing only his password and not others password. I have done some research and tried to implement the same functionality and it was working fine.
However, I came to know that I will not be able to use 'MUST_CHANGE' option and CHECK_POLICY=ON with my implementation.
For me, MUST_CHANGE is a must. Is there a way, using triggers or so we can implement this functionality. Did anyone done this before, if so, please let me know how can I accomplish it.


  • Step1 : Login as admin user and then create a login with check_policy = off
    USE [master]
    GO
    CREATE LOGIN [Smith]
    WITH PASSWORD=N'Test#123',
    DEFAULT_DATABASE=[master],
    DEFAULT_LANGUAGE=[us_english],
    CHECK_EXPIRATION=OFF,
    CHECK_POLICY=OFF
    GO

  • Step2 : Provide connect permission to "Smith" login
    use [master]
    GO
    GRANT CONNECT SQL TO Smith;
    GO

  • Step3 : Open a new connection in SSMS and login as "Smith" user

  • and she can able to change her password

ALTER LOGIN Smith
WITH PASSWORD = 'Helloworld$123' OLD_PASSWORD = 'Test#123';
GO

  • Step 4: logoff and re-connect as "Smith" using new Password='Helloworld$123'

Alternate way :
Granting ALTER ANY LOGIN permission to Smith.
However, Smith has permissions to change other login's passwords as well which shouldn’t be the case.


Thank you.

Regards,
Sam

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered ErlandSommarskog commented

Here is a trigger, but the name is not taken out of thin air. Logon triggers are always something you should be careful with, because if something goes wrong, users may not be able to log in. And that this may include the DBA.

I opted to add a check on when the password was changed, which should be very recently if the user had MUST_CHANGE. My thinking is that there can be special users, for instance sa, which has CHECK_POLICY ON, and this should not be changed.

CREATE OR ALTER TRIGGER Dangerous ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS
IF datediff(SECOND, 
            convert(datetime, loginproperty(original_login(), 'PasswordLastSetTime')), 
            sysdatetime()) < 10
BEGIN
   DECLARE @sql nvarchar(MAX) = 
           'ALTER LOGIN ' + quotename(original_login()) + 
           ' WITH CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF'
   PRINT @sql
   EXEC(@sql)
END


Personally, I would not go for a solution like this. I'd rather have something that generates a password that is not usable, for instance newid(), so that the user to preserve his sanity changes the password.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Sir for the reply.
I completely agree with you. We may not go with this approach but at least for POC we may have to show this workaround that there is a way we can do it and will try to put conditions so that it applied for specific logins.

Sir, I have a follow up question, why did we kept this logic of 10 sec? Why are we checking this? What if, I remove this logic.

IF datediff(SECOND, convert(datetime, loginproperty(original_login(), 'PasswordLastSetTime')), sysdatetime()) < 10

Secondly, we should allow password change only for the 1st time and not every time when the user tries to login.

0 Votes 0 ·

Sir, I have a follow up question, why did we kept this logic of 10 sec? Why are we checking this? What if, I remove this logic.

I explained this in my post above. It's a safety precaution, so that you don't change the settings for accounts that should have CHECK_POLICY and CHECK_EXPIRATION ON. For the accounts we are dealing we can safely assume that they changed the password very recently, since they had MUST_CHANGE.

Secondly, we should allow password change only for the 1st time and not every time when the user tries to login.

The MUST_CHANGE setting resets itself, once you have changed the password, no worry.

1 Vote 1 ·
ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered Samanthar-3682 commented

One option would be to create the user with CHECK_EXPIRATION and CHECK_POLICY=ON, and then have a DDL trigger which reacts on ALTER LOGIN, so that when the password is changed the settings are reversed. The user would not have the option to do this, so the DDL trigger would need to have an EXECUTE AS clause with a user with sufficient permission.

I can't say that this is something that I like. (And I have not tested it.)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Erland, could you please share the code of DDL trigger would look like? I am not getting complete picture of it.

0 Votes 0 ·
CarrinWu-MSFT avatar image
1 Vote"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @Samanthar-3682,

Some 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 fails.
• 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.
• Setting CHECK_POLICY = ON prevents the creation of passwords that are:
○ Null or empty
○ Same as name of computer or login
○ Any of the following: "password", "admin", "administrator", "sa", "sysadmin"

So you cannot set MUST_CHANGE is ON and CHECK_EXPIRATION is OFF at the same time. Please refer to Password Policy to get more details.

If you would like to implement the functionality, please refer to Adding enhanced password constraints to SQL Server to get the information about only Password Change Enforcement.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.