The user is not associated with a trusted SQL Server connection

Posted June 21st, 2008 in Microsoft SQL Server

Microsoft SQL Server allows you to connect using "windows authentication mode", which means using Windows logins, and "SQL Server AND windows authentication mode". In you create a SQL Server user and don't have the SQL Server authentication mode enabled, you won't be able to log in using that login, and will get error message 18452: "The user is not associated with a trusted SQL Server connection". This post looks at the error message and how to enable the appropriate authentication mode.

The user is not associated with a trusted SQL Server connection

When you attempt to log in with your SQL Server login you will receive the error message as shown in the screenshot below, despite entering the correct server, login name and password. If your login credentials are correct and you are being denied access then it's likely that the server is configured to only accept Windows Authentications. This is the default for SQL Server Express Edition (it may be for regular SQL Server as well, but I don't know for sure).

Change to SQL Server and Windows Authentication Mode

To change from just windows authentication mode to both windows authentication mode and SQL Server authentication mode, log into the server using the SQL Server Management tool with a user who has sufficient adminstrative rights.

Right-click the server in the object explorer and click properties to bring up the "Server Properties" window as shown in the screenshot below.

Select the "security" option, which has a red arrow next to it in the screenshot.

Change "windows authentication mode" to "sql server and windows authentication mode"

Click the OK button.

change to sql server and windows authentication mode

It will then tell you "some of your configuration changes will not take effect until sql server is restarted"
restart the service and you can now connect using sql server authentication mode.