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!
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 ;-).