Share via

SQL Server Authentication: How to let a user change his password in advance of its expiration date

  • Article

Question

Wednesday, July 22, 2015 8:48 AM

Hi Experts,

Could anyone give me the answers fot the two questions below?

We need to use SQL Server Authentication for some reason and would like to enforce Password Policy with 90-day expiration period. I found "Change password" dialogue appears when I first logged in with the new user, but don't know (1)what happens when the user failed to change the password before it's expired or (2)how a user can change his password in advance of the expiration date with no particular server-level permission.

I would appreciate your help!

All replies (10)

Friday, July 24, 2015 7:50 AM ✅Answered | 1 vote

I think I should think about developing a simple excel macro to execute DDL to change passwords.

Hi nino_miya,

For the issue that developing a excel macro to execute DDL, I would recommend you post the question in the following forum to get better support.
              

Excel for Developers : https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev

Thanks,
Lydia Zhang

Lydia Zhang
TechNet Community Support

Thursday, July 23, 2015 3:54 AM | 1 vote

Hi nino_miya,

Firstly, if the SQL Server Password is not changed within the expiration date, you will be able to see the below mentioned error message with in SQL Server Error Log when logging it to SQL Server . This can result in an unplanned downtime for your applications hence you should make sure you change password before the password expiration age in a planned way.

Logon Error: 18487, Severity: 14, State: 1.
Logon Login failed for user 'Login Name'. Reason: The password of the account has expired.

Secondly, to change the password of a SQL Login, the ALTER LOGIN command can be used. And every login has control permission on itself, that privilege alone cannot be enough to change a login's password. Therefore you have to specify the current password if you want to change your own password. There is an example as follows.

ALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?' OLD_PASSWORD = '2Secr@s';

Reference:
Enforce Password Policies and Password Expiration for SQL Server Logins
How to Change a SQL Login’s Password

Thanks,
Lydia Zhang             

Lydia Zhang
TechNet Community Support

Thursday, July 23, 2015 4:23 AM | 1 vote

SQl user password policy is also inherits from windows password ploicy.

when you create a SQL login you will be given 3 check boxes 1) to enforce policy 2) expery 3) password change on next login.

This password expiry comes from your windows policy. Once your password is expired you application can not connect to your database. 

TO correct this you must connect to the database with higher privileges or admin rights and execute the above alter command.   

Friday, July 24, 2015 2:59 AM

Hi Lydia,

Thank you so much for your reply! 

We would like to let our users only via Excel tables and have them type their password every time they update the data.

I understand that If we want to set up their login with SQL Server Authentication enforcing expiration policy, we need to prepare some UI for the users to execute ALTER LOGIN statement and let them change their password.

If their is something wrong with my understanding, could you point it out?

Regards,

nino_miya

Friday, July 24, 2015 3:11 AM

Hi Kumar,

Thank you so much for your reply!

I've wrote my understanding in the reply to Lydia. Could you point out if you find any misunderstanding in it?

Regards,

Yosuke

Friday, July 24, 2015 5:13 AM | 1 vote

Hi nino_miya,

Users can just install SQL Server Management Studio (SSMS), then use SSMS to connect to SQL Server instance and execute ALTER LOGIN statement.

Thanks,
Lydia Zhang

Lydia Zhang
TechNet Community Support

Friday, July 24, 2015 7:10 AM

Hi Lydia.

Thanks for your advice!

I hesitate to ask the users in the sales/marketing division to install SSMS and instruct them how to change the password.

I'm sure they'll forget in 90 days how to use it and where they have saved the file in which I instruct the way to do it....

I think I should think about developing a simple excel macro to execute DDL to change passwords.

Anyway, I really appreciate your help.

Regards,

nino_miya

Monday, July 27, 2015 12:05 AM | 1 vote

Hi Nino_miya,

I understand your users connect to the database from Excel. That means you should have a database connection with in your excel to connect to the database. yes you can develop a macro and provide a form to change the password and behind the scene you can use DDL command to change the password.

Well it is little bit mess i would not recommend because that it is hard way of doing and rist involved in it.

if i were you i would do this way.

i use windows authentication  instead of sqL login. password policy looked after by AD. i hope that satisfies your organisation policy.

Good luck

kumar

Monday, July 27, 2015 1:38 AM

Thanks Lydia for your advice and the information!

I hadn't thought of joining the Excel developer's forum to ask them for advice. I beleve this will be a great help.

Regards,

nino_miya

Monday, July 27, 2015 2:11 AM

Hi Kumar,

I would like to choose Windows Authentication is possible, but I can't because the users and the server belong to the different domains with no trust relationship due to some historical reason.

I really appreciate your generous support!

Regards,

nino_miya