Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Refer to my answer at dba.stackexchange.com/questions/43013/… –  Kin May 28 '13 at 20:09
    
@Kin I should've clarified this earlier. The AD group has read access on the target SSAS linked server. The problem is that, due to NTLM limitations, they can't use the linked server from their workstation, they can only use it if they remote in to the server containing the SSAS linked server. –  lush May 28 '13 at 20:39
1  
Perhaps this SF question is similar enough to yours to be useful. I think that the short answer is that NTLM just doesn't work like that. If the powers that be are disinterested enough in security to not worry about Kerberos, perhaps they'll accept a linked server consideration that maps Windows logins to SQL logins. –  Nathan Jolly May 28 '13 at 22:17
add comment

2 Answers

up vote 0 down vote accepted

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.

share|improve this answer
add comment

I actually found a resolution to this. When setting up the linked server to Server B, I used a Windows authenticated account as the remote login (although MSDN says it must be a SQL Server authenticated login) and I was able to successfully connect to linked server B, while connected to server A from a local workstation, while NTLM was set as the authentication mode.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.