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