SQL Server allows you to connect to external data sources through a feature called linked servers. You can connect...
to other SQL Servers on the network, or even many different heterogeneous sources such as Microsoft Access, Oracle Database or IBM DB2. There are several options for configuring security context for connecting to a remote server. Unfortunately, many DBAs use brute force to make the connection work, without fully realizing security risks associated with this option. Within the linked server Properties tab, there is a security tab that allows you to configure several options for the security context of this particular linked server. The default option is the one shown below specifying that the connection should be made using the current security context.
With this setting, a connection to the linked server is established using the same login as is used to connect to the primary server. At this point I would like to emphasize than in the vast majority of cases, this is the ideal and most secure setting. If you are using LoginX and you connect to a linked server with this setting, you will have the privileges assigned to LoginX on the remote server -- nothing more, nothing less.
I created a test stored procedure to test how this works and to show what credentials will be used. The stored procedure was created on a linked server call Katmai (another SQL Server on the same network) in the VtSQL database and it looks like this:
CREATE PROCEDURE [dbo].[TestLinkedUser]
For more on linked servers
Copying a table across linked servers
A linked server: One tool to improve SQL Server performance
SYSTEM_USER is a system function that returns the credentials of the current connection. Now I connect to my primary server using Windows Authentication and a domain account, so I am connected as "MyDomain\rrehak" login. Next, I execute the stored procedure on the linked server called KATMAI:
The stored procedure correctly returns with "MyDomain\rrehak". As I mentioned, this is the ideal setting. But, since we do not live in an ideal world, this setting may not always work when Windows Authentication is used, especially on Windows Server 2003 networks or when connecting to a server within another domain. One of the reasons for occasional failures, especially when a linked server tries to connect to another linked server, is that Kerberos account delegation needs to be properly set up. For this reason, many database administrators instead configure the security using a SQL Server login as shown below:
Now any connection made to the linked server will use the UserX credential. This login has to exist on the linked server and the password you put in here has to match the login password. Now, when I run my remote TestLinkedUser stored procedure on the linked server, it correctly returns with "UserX." This setting gets you around potential issues with authentication, but at the same time it could quickly become a security hole and allow low-privilege users to attain excessive privileges on the remote server.
Since this is a global setting for all connections, it is tempting, and sometimes even necessary, to have a user name that has sysadmin privileges to both the SQL Server and the remote server we created a link to. It means that even John Doe who may have only read access in a single database on the primary server will have sysadmin privileges with whatever queries he may decide to execute on the linked server. Hopefully, anyone reading this already knows how dangerous this can be since it exposes the remote server.
Luckily, you have another option. In addition to having a single login used for connections to a remote server, you can map local account to remote accounts. If you look closely at settings in the middle of the screen, it says "For a login not defined in the list above, connections will:" This means that the setting I was showing you will work for all logins with the exceptions of the ones you created and mapped explicitly in the dialog at the top of the tab. To demonstrate this, I modified the settings to look like this:
The difference is that I added a SQL Login called "Roman" to the mappings and set the connection to impersonate that login. So now, when I execute the remote proc logged in with the "MyDomain\rrehak" account, the procedure correctly returns UserX. The reason for that is because the rrehak login is not explicitly mapped to any other account, so the connection defaults to UserX. But if I change my query window to connect with a SQL Login "Roman," the procedure returns "Roman." In this case I used the Impersonate option.
Since SQL Server uses the username and password of the local login to establish a connection with the remote server, this only works if a remote account with the same name exists and has the same password. If the passwords are different, or if you want to map it to a different login, you need to specify remote user and the password explicitly instead of using the Impersonate option.