Marios Philippopoulos (3/22/2008)
Connections are made using the login's current security context.
So in the following query, the login running the query is the same account in whose context the connection is attempted:
SELECT * from [server\instance2k].dbName.dbo.tblName;
The puzzling thing is that the same user is able to connect to instance server\instance2k through SSMS, while logged on the instance hosting the linked server.
In your case it is pretty clear what the problem is (not so clear for the OP, who had less detail). What you are running into is almost certainly the "Two Hop Rule", which is that under windows domain security, an impersonated security context cannot re-impersonate (that is, it cannot generate the same impersonation on another server).
This is relevant because when you connect from a client to the SQL Server, it impersonates you to generate the security context. In order to get to the linked server using Trusted connections, the security context of your server session would have to be re-impersonated on the target server, and that is not allowed. So even though you can connect directly from your client to both servers, you cannot connect from you client to the first server and then through that to the linked server, because that would be a two-hop impersonation.
The way to test to see if this is really the problem is to find a way to get a session on your SQL server without it having to be impersonated and then try to connect to the linked server from there. I know of two ways to do this:
1) Log on to your server at the console or through Remote Desktop, then run your client to connect to the SQL server on the same box; then connect through it to the Linked Server; it should work now. OR..
2) Write a stored procedure that tries to connect to the linked server and run it using the SQL Agent making sure the the Run As.. is set. (Actually, I am not sure that this still works under Sql2005..)
If you ask Microsoft, they will say that the solution to this problem is Kerberos, but I have yet to see anyone successfully use Kerberos to address this problem in a complex multi-domain corporate enterprise network.
The solution that everyone ends up using is SQL Logins for server-to-server communications. Not ideal, and not a secure as anyone would like, but it does work.
The answer isn't just Kerberos, as in Kerberos authentication, but actually Kerberos delegation. Kerberos authentication, by default, is just like NTLM: double-hops aren't permitted. With Kerberos delegation, multiple hops can be set up. Where we typically see this is web browser (client) to web server (first hop) to SQL Server (second hop) all trying to pass the user credentials from the web browser all the way through to the SQL Server. Another instance is SQL Server client to first SQL Server (first hop) to linked SQL Server (second hop), again using the user's credentials the same way. If the security context changes between hops, either by using a "service account" or by using a SQL Server login, then the double hop situation doesn't occur.
With respect to complex multi-domain environments, the big thing is forest level. In Windows 2000 AD you don't have forest level trusts. So domains from one forest can't talk to domains in other forests and do Kerberos authentication. The forest is the Kerberos realm boundary. Windows will drop back to NTLM, which absolutely does not support more than one hop (by design). In Windows 2003 and above domains, Forest-level trusts are permitted, which means forests can have Kerberos authentication between domains in the separate forests.
K. Brian Kelley