asked
How do I execute sql on a remote linked server? I get Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection
I'm trying to execute a simple SQL query on a linked server and having no luck. These are both SQL Server 2000 machines, and here is the code I'm executing to set up the linked server: USE [master] GO EXEC master.dbo.sp_addlinkedserver @server = N'COSQL01', @srvproduct=N'SQL Server' ; GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'COSQL01', @locallogin = null , @useself = N'True' ; GO Both of these run just fine. When I try to execute a simple query: SELECT name FROM [cosql01].master.sys.databases ; I get: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. Both servers are set up to allow both SQL Server and Windows authentication mode and I'm an admin on both servers. Any help would be much appreciated!
This site runs by you voting. For each helpful answer below, click on the thumbs up next to those answers. If any one answer lead to a solution, indicate this by clicking on the thumbs up next to that answer.
That's an error to do with impersonation of security across to the linked server. I'd start with reviewing the info here:
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx
http://www.mssqltips.com/sqlservertip/2312/understanding-when-sql-server-kerberos-delegation-is-needed/
OK - thanks. I got it to work (sort of). The problem now is that I can't see all of the rows in the linked-server's tables. So if I run select count(*) from cmrez.dbo.reservations on the target server itself it returns 809472, but if I run the same query from a remote server I only get 78324. What's up with that!? EDIT - The linked server is in fact NOT working. The source server also has the database/table CRMEZ/RESERVATIONS, so that's what's being queried - not the destination (Linked-server). Any ideas why the link isn't working as expected?
I seem to have come up with a solution. The target server supports mixed-mode logins, so I created a new login and I specify for the linked server to use that login and it appears to work. I don't know why I couldn't use pass-through security, but that doesn't matter as long as it works ;-).
This is the "double-hop" authentication / delegation issue. Take a while to read this :
http://technet.microsoft.com/en-us/library/ms189580(v=sql.105).aspx
15 People are following this question.
