It appears that you are accessing the database from a web application which runs under NTAUTHORITY\Anonymous account on IIS. When you access SQL Server from this account, the same user credentials are sent to the database. The linked server call is occurring under the context of NT AUTHORITY\ANONYMOUS and apparently the access is not allowed on the other server.

If you are permitted to use SQL Server authentication, that may solve the problem right away. Otherwise, you might need to change the web application to run under another account that has access to the database, or give NTAUTHORITY\ANONYMOUS access to the linked server.

Jacob,

Thanks for replying.

I am executing query from Query analyzer and not from any application.

Somewhere on internet i found below reply from someone but i didn't understand what should be done for the same?

"This is the double-hop problem – basically they need to talk to their system administrator and get Kerberos trusted delegation enabled. Otherwise, they need to use sql logins instead and configure those for the linked server. "

You mentioned that you are executing the query from query analyzer. How did you connect to query analyzer? (windows or sql authentication?, what is the username used?). What is the username used in the linked server connection?

I am connected to sql server using windows authentication i.e. domain\user1

the same login is having access on another sql server so i have used the same one in the linked server and ticked impersonate checkbox. I opted option "Be made using the login's current security context".

Under the above environment, I dont think the error you mentioned can happen. I would expect that you may be seeing an error related to the current user account. However, I read above that you already did extensive research and tried various options. So I am assuming that this problem is a nasty one :-)

I would suggest you wait for a few days more to see some one else steps into this thread and provide some suggestions that might help you to fix the problem. If you can arrange a remote access to your environment, I can take a look at it over the weekend and see if I can figure out the problem.

I am tryting to execute it from my local sql client query analyzer and it's throwing that error whereas when i remotely connect to SQL server1 and try to execute the same query, it runs well.....somewhere it's written that this is a double hop problem....but no solution is provided.

Somewhere i found people talking about Kerberos authentication configuration.......as a sql developer, i m totally blank on it....

try this link. Here the same problem with sqlserver 2005 and solved with service pack installed.

http://support.microsoft.com/kb/925001

try this link also related double-hop

http://go4answers.webhost4life.com/Example/linked-server-give-nt-176107.aspx

Example of single-hop and double-hop

http://blogs.msdn.com/b/sqlprotocols/archive/2006/08/10/694657.aspx

Linked Server security issues
http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/bfe7ffe0-0dd4-4aa0-9593-7769ae3781f8/

I took help from Sys admin person.

They set SPN for my server and allow delegation for kerberos authentication in Active directory for my remote server.

It needs restart of the server.

It worked.