What’s for? – For distributed queries by accessing data on a remote server
Can the remote data source be an Excel file? – Yes, as long as we have an OLE DB provider as the remote data source
What are the two basic steps to create a linked server? – create a linked server and configure the logins
How to create a linked server? – T-SQL (sp_addlinedserver) or using the SSMS GUI
What are the two basic ways to configure logins for a linked server? – Impersonation/Delegation (i.e., self-mapping for Windows logins) and Mapped Logins (for SQL Server Logins on the remote server)
5.1 How to Configure Linked Servers for Delegation
SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query on the local server to connect to a linked server when all the following conditions exist:
- A user is connected to SQL Server by using Windows Authentication Mode.
- Security account delegation is available on the client and sending server.
- The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.
See below for details:
SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as delegation. Under delegation, the instance of SQL Server to which a Windows user has connected by using Windows Authentication impersonates that user when communicating with another instance. Security account delegation is required for distributed queries when a self-mapping is used for a specific login against a specific linked server.
Delegation Requirements
To illustrate the requirements for delegation, consider the following scenario: A user logs on to a client computer that connects to a server that is running an instance of SQL Server,SQLSERVER1. The user wants to run a distributed query against a database on a linked server, SQLSERVER2. This scenario, in which one computer connects to another computer to connect to a third computer, is called a double hop.
5.1.1 Requirements for the client
- The Windows authenticated login of the user must have access permissions to SQLSERVER1 and SQLSERVER2.
- The user Active Directory property, Account is sensitive and cannot be delegated, must not be selected.
- The client computer must be using TCP/IP or named pipes network connectivity.
5.1.2 Requirements for the First/Middle Server (SQLSERVER1)
- The server must have an SPN (A service principal name is the name by which a client uniquely identifies an instance of a service) registered by the domain administrator.
- The account under which SQL Server is running must be trusted for delegation.
- The server must be using TCP/IP or named pipes network connectivity.
- The second server, SQLSERVER2, must be added as a linked server. This can be done by running the sp_addlinkedserver stored procedure. For example:
EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'
- The linked server logins must be configured for self-mapping. This can be done by running the sp_addlinkedsrvlogin stored procedure. For example:
EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'
5.1.3 Requirements for the Second Server (SQLSERVER2)
- If using TCP/IP network connectivity, the server must have an SPN registered by the domain administrator.
- The server must be using TCP/IP or named pipes network connectivity.
5.2. Mapped Logins – Specifying the login/pw that will be used when the linked server is used
Impersonation/Delegation is the preferred way. But if you cannot do it or it is not desirable, you can use mapped login.
Two ways to create a mapped login:
- T-SQL way - sp_addlinkedsrvlogin
The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts by using the login MaryP and password d89q3w4u.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';
- SSMS GUI way
(Below is excerpted from http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm)
Login mapping is a way to associate a login on the local server, with a login on the remote server. There are two different ways a local login can be mapped to a remote login. The first method is to impersonate, and the second is to associate the local login with a remote login and password. The impersonate option takes the local Windows login and uses it to connect to the linked server. It does this by impersonating the local login. In order for the local server to impersonate, the login requires that delegation be setup between the local server and the linked server. A discussion on delegation is outside the scope of this article. To map a local login you would associate it with a remote login and password. The remote login needs to be a SQL Server Authenticated user on the remote server. The following screen shot shows how I have mapped some local logins to remote logins on SERVER2:
Here I have mapped three different local logins to two different remote logins. The first login mapping is for “DJ\GREG”, which is a Window domain authenticated user that is defined on the local server. I’ve identified the mapping so “DJ\GREG” is to be impersonated when connecting to “SERVER2”. This means anytime “DJ\GREG” is logged onto SERVER1 and issues a linked server query to “SERVER2” those request will connect and run the query on “SERVER2” in the security context of “DJ\GREG”. The second mapping is for “WEB_USER” which is a SQL Server authenticated user. I’ve mapped “WEB_USER” to the same remote login. In doing so, I had to provide the password for login “WEB_USER”. This password must be the password for the “WEB_USER” on linked server, in my case that would be “SERVER2”. The third login mapping demonstrates how you can map multiple local logins to a single remote login. In my example I mapped the Windows domain authenticated login “DJ\LINDA” to the remote login “WEB_USER”. Using mapped logins is a way to identify only those users from the local machine that can connect to the linked server.
In addition to mapping logins, you can also identify how logins that are not defined in the mappings would connect to the linked server. There are four different options that can be used. These four options are the different radio buttons in the screen shot above.
The first option “Not be made” is fairly obvious. When you select this option, any users not identified in the login mappings will not be able to connect to the linked server. The second method “Be made without using a security context” is to be used for connecting to data sources that do not require any authentication, like a text file. If you select this option to connect to a linked server then this has the same effect as selecting the “Not be made” option. The third option “Be made using Login’s current security context” means you want the linked server request to use the Windows account of the login to connect to the linked server. In order for this option to work, your SQL Server machine will need to be able to impersonate a local account. This option is a simple way to identify that all Windows accounts can use a linked server, without mapping each login. However, remember this requires delegation to be set up. The last option “Be made with this security context” is a way to say everyone using this linked server will connect with a single remote login and password to the linked server. The remote login needs to be a SQL Server Authenticated login.
6. What are the two ways to utilize a linked server? – Use four-part-name distributed and OPENQUERY with a pass-through query
6.1 What is QUENQUERY? – for pass-through query on a linked server
OPENQUERY - Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name.
6.2 Syntax
OPENQUERY ( linked_server ,'query' )
6.3 Examples
1) Executing an UPDATE pass-through query
The following example uses a pass-through UPDATE query against the linked server created in example A.
UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')
SET name = 'ADifferentName';
2) Executing an INSERT pass-through query
The following example uses a pass-through INSERT query against the linked server created in example A.
INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');
3) Executing a DELETE pass-through query
The following example uses a pass-through DELETE query to delete the row inserted in example C.
DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');
6.4 Distributed query (Four-part) or OPENQUERY? Which one is better? Test! Test! Test!
7. What’s the difference between sp_addlinkedsrvlogin and sp_addremotelogin?
Assume that Server1 is the local server and Server2 is the remote server. In short:
sp_addlinkedsrvlogin create a login mapping, letting local users run distributed queries on the linked server.
sp_addremotelogin also create a login mapping, but letting the remote user run remote procedure calls on the local server.
7.1 sp_addlinkedsrvlogin
7.1.1 Purpose
Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.
When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.
7.1.2 Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the user credentials of the local login when connecting to the linked server on behalf of the login. This is equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server, without specifying a local user name. Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.
After the authentication has been performed by the linked server by using the mappings that are defined by executing sp_addlinkedsrvlogin on the local instance of SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.
sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.
7.1.3 Examples
1) Connecting all local logins to the linked server by using their own user credentials
The following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.
EXEC sp_addlinkedsrvlogin 'Accounts';
Or
EXEC sp_addlinkedsrvlogin 'Accounts', 'true';
If there are explicit mappings created for individual logins, they take precedence over any global mappings that may exist for that linked server
2) Connecting a specific login to the linked server by using different user credentials
The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts by using the login MaryP and password d89q3w4u.
EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';
Note: This example does not use Windows Authentication. Passwords will be transmitted unencrypted. Passwords may be visible in data source definitions and scripts that are saved to disk, in backups, and in log files. Never use an administrator password in this kind of connection. Consult your network administrator for security guidance specific to your environment.
7.2 sp_addremotelogin
7.2.1 Purpose:
Adds a new remote login ID on the local server. This enables remote servers to connect and execute remote procedure calls (on the local server, I added).
Note: This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use linked servers and linked server stored procedures instead
7.2.2 Syntax
sp_addremotelogin [ @remoteserver = ] 'remoteserver'
[ , [ @loginame = ] 'login' ]
[ , [ @remotename = ] 'remote_name' ]
[ @remoteserver = ] 'remoteserver'
Is the name of the remote server that the remote login applies to. remoteserver is sysname, with no default. If only remoteserver is specified, all users on remoteserver are mapped to existing logins of the same name on the local server. The server must be known to the local server. This is added by using sp_addserver. When users on remoteserver connect to the local server that is running SQL Server to execute a remote stored procedure, they connect as the local login that matches their own login on remoteserver. remoteserver is the server that initiates the remote procedure call.
[ @loginame = ] 'login'
Is the login ID of the user on the local instance of SQL Server. login is sysname, with a default of NULL. login must already exist on the local instance of SQL Server. If login is specified, all users on remoteserver are mapped to that specific local login. When users on remoteserver connect to the local instance of SQL Server to execute a remote stored procedure, they connect as login.
[ @remotename = ] 'remote_name'
Is the login ID of the user on the remote server. remote_name is sysname, with a default of NULL. remote_name must exist on remoteserver. If remote_name is specified, the specific user remote_name is mapped to login on the local server. When remote_name on remoteserver connects to the local instance of SQL Server to execute a remote stored procedure, it connects as login. The login ID of remote_name can be different from the login ID on the remote server, login.
7.2.3 Return Code Values
0 (success) or 1 (failure)
7.2.4 Examples
1) Mapping one to one
The following example maps remote names to local names when the remote server ACCOUNTS and local server have the same user logins.
EXEC sp_addremotelogin 'ACCOUNTS';
2) Mapping many to one
The following example creates an entry that maps all users from the remote server ACCOUNTS to the local login ID Albert.
EXEC sp_addremotelogin 'ACCOUNTS', 'Albert';
3) Using explicit one-to-one mapping
The following example maps a remote login from the remote user Chris on the remote server ACCOUNTS to the local user salesmgr.
EXEC sp_addremotelogin 'ACCOUNTS', 'salesmgr', 'Chris';
