Authentication Across SQL Linked Servers

I’ve used linked server in SQL Server on many occasions, but one of the mysteries to me is getting the Security right. Most times I’ve gotten away with simply declaring a common SQL User, which is one heck of a cop out I know, but hey, it works. We were faced with an issue at work t’other day which my cop out wouldn’t work for.  We had to call Analysis Services from within a SQL Stored Procedure, but the caller was a SQL Server User used by our Linux based CastIron Integration tool, and in addition Analysis Services does not support SQL Server Users.
Well done to Warren Howe of Ridgian who solved the issue.  Here’s ‘Howe’ …. Smile
On the Security Tab he set the third option on the connections "Be made using the logon’s current security context".  This should ensure that users using SSMS can still connect, and that the SQL Server Account itself can connect. He then set the local login to be the SQL User used by the CastIron tool and he mapped it to the Remote Login which was a domain account which had access to the Analysis Services Cube. Note the user is NOT impersonated as a final step he allowed the SQL Server User used by the Cast Iron tool to execute the extended stored procedure "xp_prop_oledb_provider" in the master database.
This now works a treat, hope it helps someone out there in Internetland!
Dave Mc

About davemcmahon81
Software Developer & Architect, User Group Leader, Speaker, Writer, Blogger, Occasional Guitarist, Man-made Global Warming Sceptic, Climate Change Believer, General Optimist but most of all proud Husband and Dad ...

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: