Tuesday, April 28, 2009

The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

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.

13 comments:

Seyhmus Inci said...

this does not work when connecting from a machine on a different domain.

Kiran said...

thanks a lot for ur help

Kiran said...

Excellent sollution! Saved my day!

Kiran said...

Proved, it workes, thanks a lot.

Kiran said...

Thanks a lot.It was really helpful....

Kiran said...

Hmmm.. I worked, thanks man

Kiran said...

hey thanks a lot

Kiran said...

Life Saver

Kiran said...

hi, you really saved me.

Kiran said...

hi, you really saved me. tooooooooo

Kiran said...

thanksssssssssssssss alot

Kiran said...

like u:">

Manjunath said...

Thank u man.. I have searched net for 2 days for this problem. Your blog solved it..Thank you very much..Keep sharing the knowledge..