0

I need to lock down access to a linked server object in MSSQL server.

I am building views on a host database, from which, I query to populate a staging table on my warehouse server. I am using Data Tools/SSIS to extract the view data. To simplify the SSIS package, I am using the OPENQUERYsyntax to query the linked server object that exists on my warehouse server, and connects to other SQL servers, Oracle servers, etc., through linked server objects.

To provide access to the linked servers, I have set up a local SQL login on the host db that has read access, then I use 'Be made using this security context:' and pass the local SQL login. That works just fine.

I realize now that I have a problem: any user with warehouse access can query the linked server object because of that stashed security context! I don't want that! I do need folks who should have access to be able to query (so I can write my SSIS packages), as well as the SQL Server Agent service account to have access so when the SQL Server Agent job runs as that user that it can successfully query the linked server.

I believe that the key to locking down query access to the linked server object is somewhere in the 'Local server login to remote server login mappings', but I'm having a hard time figuring that out. When I try to add for instance NT SERVICE\SQLAgent mapped to the local login with access, then save, I hit 'Login failed for 'NT AUTHORITY\ANONYMOUS LOGON' when saving.

Any ideas on how I can allow a security groups that have access, and SQL Server Agent service account to query the linked server, but not the rest of people with warehouse access?

henhen
  • 23
  • 9

1 Answers1

0

This is known as the 'double hop' problem

(https://blogs.technet.microsoft.com/askds/2008/06/13/understanding-kerberos-double-hop/)

and to get rid of the Anonymous login error you would have to properly set up Kerberos pass-through authentication;

https://blogs.msdn.microsoft.com/farukcelik/2008/01/02/how-to-set-up-a-kerberos-authentication-scenario-with-sql-server-linked-servers/

https://www.databasejournal.com/features/mssql/article.php/3696506/Setting-Up-Delegation-for-Linked-Servers.htm

However this is quite involved - you mention that to 'simplify' the SSIS package you use a linked server, however SSIS solves exactly this problem... Why don't you just use SSIS to copy the data from the other server to avoid using a linked server?

Milney
  • 6,253
  • 2
  • 19
  • 33
  • Thanks for the information - it sounds I went the wrong direction to simplify. So you're saying that one approach would be to set up the connection managers within SSIS and manager the connections from there instead of linked servers on the warehouse, is that right? – henhen Feb 14 '18 at 23:00
  • That would indeed be the recommended approach. SSIS is designed for exactly that type of scenario, where linked servers are not ideal for it (although they will work if you do set up Kerberos properly). – Milney Feb 14 '18 at 23:01
  • Very good information. I guess I was thinking that for the SSIS connection managers, I like just having a single parameter that I can update to target a separate environment. I update to 'TEST' and it targets my warehouse test environment, which in turn has my linked servers set up with the correct configurations for their environments, and I don't have to mess with anything in SSIS – henhen Feb 14 '18 at 23:04
  • @henhen - Are you running your Job through SQL Agent? If so - You can create 2 jobs, one for test and one for live - then in the Agent Job Step properties, you can override the connection manager settings. In this way you don't need to change any setting, you just run the appropriate job – Milney Feb 14 '18 at 23:05
  • If you don't use SQL Agent, you can still do this - using variables and expressions in the Connection Managers that reference the variable, then you just need to swap the one variable – Milney Feb 14 '18 at 23:05
  • Yes - I'm running jobs both manually for testing, then scheduled with SQL Agent. Overriding the settings their might be a good option. I'll give that a shot. Or maybe I can convince our friendly DBA to set up Kerberos. I really appreciate the help Milney! – henhen Feb 14 '18 at 23:08