SQL Authenticated User needs to change their own password

Question

SQL Authenticated User needs to change their own password

Terry Mercer

Old Hand

Points: 362
More actions
November 12, 2012 at 12:40 pm

#270145

We have an Access application that leverages ODBC DSN to link to SQL Server 2008 database. SQL Authenticated logins and passwords have been created for each user on the SQL Server. These logins have been granted access to the SQL Server database.
When creating the logins: Enforce password policy, Enforce password expiration and User must change password at next login have all been checked. We have supplied each user with the original password. In this case login = FuryIII and original password = HenryJ1
The issue arises when the user attempts to create a User DSN on their workstation. After selecting Server, Connect with SQL Server authentication, checks box Connect to SQL Server to obtain default setting for the additional configuration options. enters Login ID: FuryIII and Password: HenryJ1 Clicks Next user receives - Microsoft SQL SQL Server Login message box with following information Connection failed: SQLState: '4200' SQL Server Error: 18488 [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'FuryIII'. Reason: The password of the account must be changed.
What we don't know is how user FuryIII changes his password.
There does not seem to be any method available for this during setup of the ODBC DSN and therefore FuryIII cannot login to the Access application.
User FuryIII does not have nor do we want him to have access to SQL Server tools. Our policy dictates the SQL Server DBA should not know each user's password and using NT authentication is also not an option.
Any assistance would be appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Richard Moore-400646 SSCrazy Points: 2384 More actions · Answer 1

To my knowledge you can't change a password through the ODBC call. You have to either execute the sp_password stored procedure , pass the password API to an application or allow access to SMSS. The DBA / User would have to work together in order to get this changed, perhaps through a RDP session.

Terry Mercer Old Hand Points: 362 More actions · Answer 2

Thank you. We created an VB applet that connects to the server and uses

ALTER LOGIN FuryIII WITH

PASSWORD = 'NewPassword'

OLD_PASSWORD = 'HenryJ1'

It seems to work well as long as the SQL Authenticated login has CHECK_POLICY set to OFF

ALTER LOGIN [FuryIII] WITH CHECK_POLICY = OFF

Thanks for your assistance.

Eric M Russell SSC Guru Points: 125519 More actions · Answer 3

NULL

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

sgmunson SSC Guru Points: 110639 More actions · Answer 4

Terry Mercer - Monday, November 12, 2012 5:11 PM
Thank you. We created an VB applet that connects to the server and uses ALTER LOGIN FuryIII WITH PASSWORD = 'NewPassword' OLD_PASSWORD = 'HenryJ1'It seems to work well as long as the SQL Authenticated login has CHECK_POLICY set to OFFALTER LOGIN [FuryIII] WITH CHECK_POLICY = OFFThanks for your assistance.

If you want that policy back in place, you'll have to have your DBA re-enable it after the first successful login.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

HappyGeek SSCoach Points: 18726 More actions · Answer 5

sgmunson - Thursday, April 19, 2018 11:58 AM
Terry Mercer - Monday, November 12, 2012 5:11 PM
Thank you. We created an VB applet that connects to the server and uses ALTER LOGIN FuryIII WITH PASSWORD = 'NewPassword' OLD_PASSWORD = 'HenryJ1'It seems to work well as long as the SQL Authenticated login has CHECK_POLICY set to OFFALTER LOGIN [FuryIII] WITH CHECK_POLICY = OFFThanks for your assistance.
If you want that policy back in place, you'll have to have your DBA re-enable it after the first successful login.

I think the OP left the building some years ago...........

...