Sql-server – ny way to connect to one server and utilize a linked server using Windows authentication and NTLM

authenticationlinked-serverSecuritysql serversql-server-2005

I currently have SQL Server 2005 (Server A) that has a SSAS 2005 linked server (Server B). The powers that be do not want to enable Kerberos authentication, so I'm stuck with NTLM.

The problem is that when developers connect from their local workstation to Server A and try to run queries against the linked server, Server B, they are not able to. They are only able to if they RDP into Server A and then run queries against the linked server, Server B. My understanding is that this is due to constraints of NTLM.

Is there any way at all I can enable the developers to connect to Server A on their local workstation and be able to run queries against the linked server?

To clarify, the user has access to both the SQL Server instance containing the linked server, as well as the SSAS catalog that the linked server points to.

Best Answer

Without setting up Kerberos then no. Why do they not want to setup Kerberos? It's already 99% setup. All you need to do is create the SPNs and restart the SQL Server instances. Kerberos is the default AD authentication system. You are falling back to the less secure authentication systems. Sounds like the powers that be don't understand the differences.