Security related Login password change

Question

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

Answer 1

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.

Answer 2

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.)

Answer 3

Hi @Sam ,

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.