Sunday, September 10, 2006 9:52 PM
by
plitwin
Login failed ... not associated with a trusted SQL server connection
If you've ever encountered a message along the lines of "not associated with a trusted connection to SQL Server", the problem is usually the SQL Server not supporting mixed mode (a.k.a. "SQL Server and Windows Authentication mode" in SQL Server 2005 or "SQL Server and Windows" authentication in SQL Server 2000).
This usually occurs after one has added some SQL Server logins to a SQL Server and tries to establish a connection to that SQL box using ADO.NET, SQL Server Reporting Services, or some other client application. The issue is usually failry insiduous because SQL Server lets you create SQL Server logins regardless of whether plans on accepting those connecctions. It would be nice to get some sort of warning along the lines of "This SQL Server login will not work until you change the authentication mode for the server".
How to fix:
For SQL Server 2005:
1. Startup SQL Server Management Studio.
2. Right click on the server name in the Object Explorer window of SQL Server Management Studio and select Properties from the popup menu.
3. At the Server Properties dialog, under Select a page, click on Security.
4. Check to see that the Server Authentication option is set to SQL Server and Windows Authentication mode. If it is not, change it now and OK to commit the change.
5. If you changed the mode in step 4, you will get a warning message about needing to restart the server.
6. If you changed the mode in step 4, right click on the server name and select Restart from the popup menu. Click OK at the confirmation dialog box.
For SQL Server 2000:
1. SQL Server Enterprise Manager.
2. Right click on the server name in the console tree of SQL Server Enterprise Manager and select Properties from the popup menu.
3. Click on the Security tab of the SQL Server Properties dialog.
4. Check to see that the Authentication option is set to SQL Server and Windows. If it is not, change it now and OK to commit the change.
5. If you changed the mode in step 4, you will get a warning message about needing to restart the server and offering to do so.
6. Click OK twice.
This has bit me a couple of times in the past and my coworkers at least three times in the last year (where my memory of the problem and the fix made me a hero that day--wish all days and memories were like that).
Paul