October 5, 2021 at 2:35 pm
I have a situation where a group uses a linked server to read data from a few of our tables. The linked server is configured to use the current user's context so we have logins with appropriate permissions set up on our side to allow for the use of the linked server. Is there a way to only allow those users to access our data via the linked server and not directly connect to the database engine? For example, from their instance they can run the select queries and still pull data but they cannot choose the instance to connect to and browse those tables in the object explorer.
October 5, 2021 at 2:50 pm
You might be able to do it via a stored procedure.
https://qa.sqlservercentral.com/forums/topic/signing-a-stored-procedure-that-uses-a-linked-server
October 5, 2021 at 3:30 pm
You might be able to use a login trigger to deny the connection for those specific users if they were not coming from a linked server. You'd have to review what the HOST_NAME() and other functions to know if you could determine whether they were coming from a linked server or not.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 5, 2021 at 3:58 pm
If you don't need the end user connecting as themselves, you could have it connect as a specific user on the remote machine. Downside is you may need to create multiple users on the linked server and map them up on the source side .
A nice tutorial on how to do this is available here:
https://database.guide/how-logins-work-on-linked-servers-t-sql-examples/
Specifically the section Example 4 where it ties the local user Milhouse to the remote user Maggie and no other users are tied to that account so ONLY Milhouse can pull data from the linked server AND is only allowed to see things that Maggie would be allowed to see.
The advantage to this approach is that the local user Milhouse (in the linked example) wouldn't know and wouldn't need to know Maggie's password and thus couldn't log into the linked server directly. Disadvantage is if you ever needed to change Maggie's password, you may have a lot of linked server passwords to update.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 5, 2021 at 5:53 pm
If you don't need the end user connecting as themselves, you could have it connect as a specific user on the remote machine. Downside is you may need to create multiple users on the linked server and map them up on the source side .
A nice tutorial on how to do this is available here:
https://database.guide/how-logins-work-on-linked-servers-t-sql-examples/
Specifically the section Example 4 where it ties the local user Milhouse to the remote user Maggie and no other users are tied to that account so ONLY Milhouse can pull data from the linked server AND is only allowed to see things that Maggie would be allowed to see.
The advantage to this approach is that the local user Milhouse (in the linked example) wouldn't know and wouldn't need to know Maggie's password and thus couldn't log into the linked server directly. Disadvantage is if you ever needed to change Maggie's password, you may have a lot of linked server passwords to update.
the other negative aspect is that someone with admin access to server can retrieve the password for any linked server on that server.
October 5, 2021 at 7:21 pm
Out of those three options, the trigger might be the only one I'm able to use. I'm not a fan of going that route but I'll look into seeing if it is possible to limit users to only connect from a certain server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply