Situation:
- In an isolated dev environment set up to enable Kerberos in SharePoint 2007 and SQL Server 2005, a single VM Server for the DC, one for the SQL Server and one for the SharePoint Web Front End, developers were unable to create custom databases in the SQL Server instance and use SQL Server local logins to connect to those databases with ODBC.
- Tests with generic UDL shortcuts (ODBC) failed with the error “Login failed for user “<username>”. The user is not associated with a trusted SQL Server connection.” upon clicking “Test Connection” button.
Carried out the following tests:
- Made sure we had the right password for the testing account. If needed, can change passwords this way.
- Made sure that the Database instance was set to support both SQL Server and Windows Integrated Authentication (this is what the majority of Google hits for this problem suggested).
- Checked some Registry settings:
- Made sure that SQL @@servername was correct (select @@servername executed as a query helps with this. Google can help provide more details).
- Checked SQL Logs for more information (found Error 18452 after each connection failure)
- The term “trusted connection” suggested that the issue might be with Kerberos even though we were using local accounts, so I also checked for the “Integrated Security” connection string value per discussion here.
- Finally went through the UDL shortcut configuration again, and found an interesting phenomenon. If you click the “Test Connection” button you get the generic trusted connection error. But, if you click the drop-down list for selecting the database you want to connect to, you get a very applicable error message.
The fix for this problem is to go back into the SQL Server Management Studio, edit the properties for the local user and first enable “Enforce Password Policy” for the user, THEN uncheck all of the options including “Enforce password expiration” AND “User must change password at next login”.