Blitz Result: Linked Servers Configured

Linked servers let your users query from one SQL Server to another (or even to other database platforms.)  There’s nothing wrong with linked servers by themselves, but often they get set up using powerful logins.  People set up a linked server over to another server, set it up to use the SA login by default, and then they don’t realize that every query that gets executed across those linked servers uses SA permissions.

This part of our SQL Server sp_Blitz script checks sys.servers looking for is_linked = 1.

To Fix the Problem

You have to investigate each linked server to check and fix its security configuration.  We can’t easily tell from SQL whether we’re using a login that’s a sysadmin (or just an overly powerful login) on another server.

Return to sp_Blitz or Ask Us Questions

---

How to Review and Lock Down Linked Server Permissions

You can check each one by going into SQL Server Management Studio, Server Objects, Linked Servers.  Right-click on each server and look at its security information.  That’s the account it’s using to connect to the other SQL Server.

If the login isn’t sa, there’s more leg work: you need to check the other instance and see what permissions the account has there.

If that account has more permissions than you’re comfortable with on the other server, start asking questions about what permissions it really needs.

Better Options

Look at that list of options at the bottom. You don’t have to let everyone through the linked server! It’s much more secure to do one of the following:

Securing Linked Servers: Easy Mode

This method is a little fussy, but at least it doesn’t require a Masters Degree in Active Directory Administration. (When you find someone that issues those, let us know.)

• Add lists of logins that are allowed to use the linked server
• Map them to remote accounts appropriately
• Select “Not be made” for logins not defined in the list.

LINKED SERVERS: Rocket Science Mode with Kerberos Authentication

This option requires working closely with an active directory administrator and testing, but it results in a better security configuration.

• Set up Kerberos authentication/delegation between the SQL Servers so that Windows accounts can “double hop”
• Use the “Be made using the login’s security context” option

Even when you fix security holes with linked servers, you still have a wealth of performance issues to consider

SQL Server will sometimes wait to filter results until after they’re all pulled back over the network.

• Big table locks
• Writing to tempdb
• Mismatched execution plan for much larger results

Low permission users can’t access index or column statistics before 2012 SP1

• User needs sa, db_owner, or ddl_admin to use them
• The only thing worse than SQL having statistics is SQL not having statistics