Every time I install sql server, in the initial days I used to face problems in logging on by sql server authentication mode.
These were the steps I used to follow to create new login in order to logon using sql server authentication mode.
1. Log on to the server using windows authentication mode and create some login in the section of Sqlserver->Security->Logins->(Right click)NewLogin.
2. Restart the sql server and disconnect it.
3. This time connect to the server using the sql server authentication mode by entering new login credentials.
But it never used to work. The error I receive was “The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)”.
After doing a long research on this I found the solution to this. To make sql server authentication work we need to configure the sql server to use Sql server and windows authentication mode. The following steps will solve the problem.
1. Right click on the root node of the sql server and select properties in the context menu.
2. That will open a popup window which will have server level settings.
3. Click on security tab it will show the view as in the following figure. There you have to make sure you have “Sql server and Windows authentication mode” is selected.
4. Press OK and restart the sql server again.
5. This time the sql server authentication mode using the new login credential will work.
Note: You have to make sure your login has server role as “SysAdmin” in order to logon.