When setting up linked servers, the selection of the accounts that are used for the linked server logins should have the lowest permissions needed to get what the users on the source side of the linked server need to do. Over time, this will mean changing the permissions of the linked server or even setting up multiple linked servers that all point to the same target server so that different applications don’t have permission to access each other’s databases over the linked server. The one thing that you never want to do is to use a login for the linked server that has sysadmin rights on the target instance, especially if that linked server is available for everyone on the server to use.
The reason that you don’t want to have a linked server with sysadmin rights is pretty straightforward: you don’t want someone connecting to your source server and having the rights to do whatever they want on the target server. Beyond the threat of the user on the source server being able to do whatever they want on the target server, the target server can’t audit what’s being done because, as far as the target server is concerned, everything is being done by the shared account that the linked server is using for authentication. (See my statement earlier about anyone on the source server being able to access anything they want against the target server.)
If you have linked server connections set up in one direction you might have them set up in the other direction as well, also using sysadmin rights. Well, that means that you’ve just given the user on the source server access to the source server and the target server with sysadmin rights. They could change any setting they want, create databases, drop databases, etc. Let’s look at a diagram to clarify what we’re talking about.
At the top of the diagram, we have our user named “Bob” who is connecting to the server named “Source”. When they connect to the server, they have minimal permissions. They run whatever queries they need to get what they need to do their job. Part of that includes using a linked server to the server named “Target.” That linked server was set up with an account that has “sa” or sysadmin rights because it’s easier just to use sysadmin rights than make changes constantly to the permissions of the linked server. Not every command that Bob executes against the server Target has sysadmin rights. So Bob can now drop databases, take databases offline, do restores, etc. While poking around the server Target, Bob funds a linked server back to Source. Using a couple of simple commands like exec target.master.dbo.sp_execute, he can issue commands against the server Source, where he finds out that he now has sysadmin rights against both source and target.
Now, Bob isn’t looking for trouble, so he files away this knowledge for a later day.
The later day comes and Bob sees a bunch of his co-workers getting laid off. Bob remembers that he has access to both these servers with sysadmin rights, so he gets a window up in SQL Server Management Studio, writes his script to drop databases on both servers and waits to be called in to HR to be laid off. Once he’s called in, he clicks execute and locks his machine and walks to HR.
By the time that anyone figures out what happened (if they ever do) Bob no longer works for the company, but did the damage while he did.
Now let’s assume that it isn’t Bob that is going to damage the database server, but some outside attacker that wants to damage the company. And they are going to use Bob’s account to do it. It wouldn’t take them long to figure out just how much they have access to, and just how much damage they can do, just because a couple of linked servers are misconfigured.
In conclusion, please stop using sysadmin rights on linked servers.
If you need help reviewing your linked servers’ permissions and what permissions are needed by the applications, Contact our team to schedule a meeting to review the required permissions and design an action plan to help resolve these issues.
Denny
