Login failed for user: The user is not associated with a trusted SQL Server connection

Please visit my new Web Site https://coderstechzone.com

This is your first project? Am i right? Yes almost 50%(my guess) Asp.net beginner got the below message while he run his project. The message is:

Login failed for user ''. The user is not associated with a trusted SQL Server connection. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.

Don't worry about this error. This is a common scenario. In this post i will try to help you. If you debug the code then in Conn.Open(); line you get this error.

In SQL Server there are two methods to authenticate a user is:
1. Windows Only Authentication
2. Windows and SQL Server Authentication or Mixed mode Authentication.

The most common reason for this error is you are using Windows Authentication & your supplied connection string is wrong. The right format of connection string for Windows Authentication is given below:

<add name="ConnectionTest" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=DBNAME;Trusted_Connection=yes;" providerName="System.Data.SqlClient"/>

*So simple. Just copy then change the connection string name & Database name.

Reasons & Solutions:
1. Check that web.config connection string is not misspelled or not any unwanted spaces.
2. If you are in local & SQL Server in remote then make sure that your domain is trusted in the SQL server domain.
3. If you change your windows password just now then logout from windows & the login again. This happend due to cache problem.
4. If your SQL Server or network is too busy then SQL Server can not authenticate you from domain controller.
5. If you are in local & SQL Server in remote & you are bound to create trusted connection then impersonation is the solution. For more details on impersonation Click Here.
6. You are trying to connect by a user like sa. User sa can not connect since the mode is Windows mode. To connect through a user you must change the mode to Mixed. See details at the bottom section to change the mode.

Here i have discussed on Windows Authentication. For Mixed mode authentication you may receive this error for wrong connection string. An idle connection string is as follows:

<add name="ConnectionTest" connectionString="Data Source=SERVERNAME;Initial Catalog=DBNAME;User Id=USERNAME;Password=xxxxx;" providerName="System.Data.SqlClient"/>

*So simple. Just copy then change the connection string name , server name, Database name, user name & password.

Reasons & Solutions:
1. Check that no unwanted space within the connection string.
2. Check user name & password.
3. Immediate password change may be reason.

4. Network congession may be the another reason.

Check Sql Server Authentication Mode:
You can check the mode easily. Run the below SQL:

EXEC master.dbo.xp_loginconfig

You will get login mode from the result like:

Change Authentication Mode:
Sql Server 2000:
You have to change the registry which is very sensitive. So becareful & backup first.
To set this key to mixed-mode, follow these steps:

1. Stop SQL Server and all related services, such as SQLAgent, from the Services control panel.
2. Click Start, click Run, type regedt32, and then click OK.
3. Find the HKEY_LOCAL_MACHINE window on the local computer.

4. Navigate to the following registry key for the default instance:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer

5.Navigate to the following registry key for a named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance Name\MSSQLServer\LoginMode

6.On the right-hand pane, find the value LoginMode and double-click it.
7. In the DWORD Editor window, set the value to 0 or 2. Also, make sure that the Hex radio button is selected.
8. Click OK.

For mixed mode set the LoginMode is 0 or 2 & for Windows Authentication set 1.

Restart SQL Server and SQL Server Agent services for this change to take effect.

For more details Click Here.

For SQL Server 2005:
In SSMS (SQL Server Management Studio), after you log in using your Windows Authentication, right click on the server and choose “Properties” and then go to the Security page and change the “Server Authentication” to be “SQL Server and Windows Authentication mode” or vice versa as shown in the image below: