SQL Authenticated user change his/her own password

45 pts.
Tags:
SQL Server
SQL Server database
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 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.

Software/Hardware used:
SQL Server 2008, Access 2010, ODBC DSN
ASKED: November 12, 2012  7:57 PM
UPDATED: November 12, 2012  8:06 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

The reason that this is happening is because you are forcing the user to change their password on the first login, but the ODBC DSN creator doesn’t support this.  You need to have your application correctly prompt the user to connect.

On a separate note you’d be better off not requiring that the DSN be created at all and instead imbed the DSN information within the connection string within the Access database, or better yet have some VB Script within the access database setup the DSN for the user automatically through code so that the user doesn’t need to deal with this.  This gives you the added bonus of not needing to change every single DSN that users have when you want to change/upgrade/rename the SQL Server.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TLMercer
    Thanks to any that have reviewed this and were formulating a response.  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.
    45 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following