Re-imagining Linked Servers

Introduction

Linked servers are an essential part of the SQL Server developer's tool kit.  They are often necessary for pulling/pushing data to/from remote servers whether those servers run SQL Server, Oracle, DB2, PostgreSQL or any other RDBMS. Linked servers are flexible and can adapt to many different types of "servers" including Excel workbooks and MS-Access databases among others

Creating a linked server is relatively easy.  There is both a GUI method in SSMS (under Server Objects/Linked Servers) and a T-SQL method, using the system stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin. 

Problem

Securing access to linked servers falls outside the normal SQL Server permissions controls.  For example, to give some user -- say CORP\USER1 -- access to a linked server, you need to add that login id as a linked server login and map it to some login on the linked server.  That means that, for every user who needs to access the linked server, you potentially need to add a new linked server login.

There are options available to mitigate this somewhat.  If the remote user is in the same AD domain as the local user, you can specify that the connection should be made using their credentials.  This cannot work across domains unless those domains are in a trust relationship.  You can also specify a catch-all remote user which is used for connections where there is no explicit mapping specified. Also, you can simply deny access for any users trying to connect that are not in the explicit login list. (I'm using the terms "user" and "login" rather loosely here.  Strictly speaking, linked servers only know about logins since they are a server-level object.)

Solution

I find these options deficient and inflexible.  I do not want to edit the linked server object every time I need to give a new login access or remove an old login.  I'd rather use SQL Server's roles and credentials.  Here's how I envision this working:

1. When you create a new login for a linked server, you should have additional options for the local login, specifically:
1. An AD group that is in the Server Logins.  This would mean that any member of the group that can connect to the server (that is, not DENYd), can use the linked server.
2. A SQL Server user-defined role.  This is the most flexible.  Any member of the role would have access to the linked server.  Note that you can add AD groups to roles if those groups are included in the server login list.
2. In addition to specifying a specific remote login, you should have the ability to specify a Credential. 

These additional options, should they be added, would make the management of linked servers easier and more consistent with the SQL Server security model.  With these in place, adding logins to the list of those permitted to use the linked server would be a simple matter of adding them to the AD group or add the login directly to the server role.  Also, adding or changing the remote login would be managed as credentials,

Conclusion

I'm convinced that it is time to align linked server management with the SQL Server roles and credentials security model.  Let's see if we can raise this issue for SQL Server Next!