How do I force a SQL Login to change it’s password on next login

2

June 5, 2019 by Kenneth Fisher

Forcing someone to change their password on a periodic basis is a pretty common thing, and any time I’ve gotten a new active directory login I’ve been given an obviously bad password and forced to change my password on the next login. It’s a good idea right?

So can we do that with SQL Logins? (And yes, I realize by preference we should be using AD Logins, but sometimes you have to do what you have to do.) Absolutely we can. And should. I’m a firm believer that DBAs should not be in the password business. It’s your id. I don’t want to know the password. I don’t know the password. Don’t ask me. But of course when a DBA creates a SQL Login they have to enter the password, requiring them to forget that password going forward. Which is particularly hard if you had to email it to the user. (Email search)

All of that brings us back to How do we do it. The clause MUST_CHANGE after the password.

CREATE LOGIN kenneth_test WITH PASSWORD ='P@ssw0rd' MUST_CHANGE, 
     CHECK_EXPIRATION = ON;

You’ll notice a few things here. First of all I’m terrible at naming things. But more importantly CHECK_EXPIRATION must be turned on. Oh, and it’s not in the command because it’s the default, but CHECK_POLICY has to be on as well. This can cause a few difficulties over time depending on your policies. If you allow someone to have a non-expiring password (not the best idea, but again it happens in the real world) then you have to remember to turn it back off later.

ALTER LOGIN [kenneth_test] WITH CHECK_EXPIRATION = OFF;

This won’t run if they still have MUST_CHANGE turned on so you might want to know when they have changed their password. To do that we need the LOGINPROPERTY command. If you are doing a bunch at once you could use the PasswordLastSetTime property, but honestly, that’s going to be tricky. Here’s my preference:

SELECT 'ALTER LOGIN ' + quotename(name) + ' WITH CHECK_EXPIRATION = OFF'
FROM sys.server_principals
WHERE LOGINPROPERTY(name,'IsMustChange') = 0
  AND LOGINPROPERTY(name,'DaysUntilExpiration') IS NOT NULL;

If DaysUntilExpiration is non NULL then CHECK_EXPIRATION is still turned on. And IsMustChange should be pretty obvious.

Last but not least, what to expect. If you create the above login, then log in as that id you’ll see the following:

If you don’t manage to successfully change the password then you can’t log in. This is pretty cool if you are logging in using something like SQL Server Management Studio (SSMS) but I’m not sure how it will work if your first connection is through a piece of code. If you are handing out a login to be used in code I’d recommend having them log in once through SSMS (or other similar tool) and change the password before doing anything else.

2 thoughts on “How do I force a SQL Login to change it’s password on next login

  1. Max Vernon says:

    I agree it’s a great idea to have people change their passwords to someone only they know. However, password expiration can actually reduce security, and Microsoft has recently acknowledged this in their [Security Baseline for Windows 10](https://blogs.technet.microsoft.com/secguide/2019/05/23/security-baseline-final-for-windows-10-v1903-and-windows-server-v1903/)

    > Periodic password expiration is a defense only against the probability that a password (or hash) will be stolen during its validity interval and will be used by an unauthorized entity. If a password is never stolen, there’s no need to expire it. And if you have evidence that a password has been stolen, you would presumably act immediately rather than wait for expiration to fix the problem.

Leave a Reply to Kenneth Fisher Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,087 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: