Linked servers allow getting data from a different SQL Server instance using single T-SQL statement.
This article will explain how to create, configure and drop a SQL Server linked server using system stored procedures.
Also, there is a way to create and configure a linked server to another SQL Server instance using SQL Server Management Studio (SSMS). To see how to configure a linked server by using the SSMS GUI, see the article How to create and configure a linked server in SQL Server Management Studio.
Creating a SQL Server linked server
To create a linked server, use the sp_addlinkedserver procedure.
Executing the following code:
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver N'TestServer', N'SQL Server'; GO |
This will create a linked server with the name TestServer under the Linked Servers folder:
In order to get databases from the remote SQL server the exact name of the SQL Server need be entered.
For default instance of the SQL Server type the name of the computer that hosts the instance of SQL Server (e.g. WSERVER2012):
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver N'WSERVER2012', N'SQL Server'; GO |
For the SQL Server name instance, type the name of the computer and the name of the instance separated by slash (e.g. WSERVER2012\SQLEXPRESS):
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver N'WSERVER2012\SQLEXPRESS', N'SQL Server'; GO |
The results will be something like this:
When creating a linked server like this by default, it will pass the current security context of the local login to the remote login.
Window or the local login credentials, will be used if Windows or SQL Server authentication is selected, respectively. In order to successfully connect to a remote SQL Server, the exact user with the same username and password must exist on the remote server:
Besides defining a parameter for the name of the linked server and the product name, while using sp_addlinkedserver, other parameters such are: provider name, data source, location, provider string, catalog can be set:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE master; GO EXEC sp_addlinkedserver @server = 'server', @srvproduct = 'product name', @provider = 'provider name', @datasrc = 'data source', @location = 'location', @provstr = 'provider string', @catalog = 'catalog'; GO |
@server is a name of the linked server:
|
1 2 3 4 5 6 |
USE master; GO EXEC sp_addlinkedserver @server = N'WSERVER2012\SQLEXPRESS', |
@srvproduct is the product name of the OLE DB data source you’re adding as a linked server (e. g ‘SQL Server’, ‘Microsoft.Jet.OLEDB.4.0’,’ Oracle’ etc.). The @srvproduct parameter is nvarchar(128) data type, by default this parameter is NULL. When using SQL Server as the product name, the provider name, data source, location, provider string, and catalog parameters do not have to be specified:
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver @server = N'WSERVER2012\SQLEXPRESS', @srvproduct = N'SQL Server'; GO |
@provider is the OLE DB provider name and must be unique for the specified OLE DB provider installed on the current computer:
The @provider parameter is nvarchar(128) data type, by default this parameter is NULL. If the provider name parameter is omitted, SQLNCLI (SQL Server Native Client) is used:
|
1 2 3 4 5 6 7 |
EXEC sp_addlinkedserver @server=N'WSERVER2012\SQLEXPRESS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'WSERVER2012\SQLEXPRESS'; |
@datasrc is the name of the data source as interpreted by the OLE DB provider. If you are connecting to an instance of SQL Server, provide the instance name:
|
1 2 3 4 5 6 7 |
EXEC sp_addlinkedserver @server=N'OtherSQLServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'WSERVER2012\SQLEXPRESS'; |
When creating a linked server like this, a name of the server in the @server parameter can be anything, does not need to be a name of a remote server:
If you are using another data source (e.g. Excel file), provide the full path to the Microsoft Excel file in the @datasrc parameter:
|
1 2 3 4 5 6 7 |
EXEC sp_addlinkedserver @server = 'ExcelData', @srvproduct = 'Microsoft.Jet.OLEDB.4.0', @provider = 'Microsoft.Jet.OLEDB.4.0', @datasrc = 'C:LinkedExcelData.xls'; |
@location is the location of the database (source), if required by the OLE DB provider. The @location parameter is nvarchar(4000) data type, by default this parameter is NULL.
|
1 2 3 4 5 6 7 8 |
EXEC sp_addlinkedserver @server=N'OtherSQLServer', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'WSERVER2012\SQLEXPRESS', @location= NULL; |
@provstr is the OLEDB string that identifies the source. The @provstr parameter is nvarchar(4000) data type, by default this parameter is NULL. If the linked server created using SQLNCLI then in the @provstr parameter can be specified the instance of SQL Server to which the user will be connected:
|
1 2 3 4 5 6 7 8 |
EXEC sp_addlinkedserver @server=N'WSERVER2012\SQLEXPRESS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'WSERVER2012\SQLEXPRESS', @provstr = N'SERVER=WSERVER2012\SQLEXPRESS'; |
@catalog is the name of the catalog or the name of the database for the SQL Server, by default this parameter is NULL. When the linked server is defined against an instance of SQL Server, the catalog refers to the default database to which the linked server is mapped.
|
1 2 3 4 5 6 7 8 9 |
EXEC sp_addlinkedserver @server=N'WSERVER2012\SQLEXPRESS', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'WSERVER2012\SQLEXPRESS', @provstr = N'SERVER=WSERVER2012\SQLEXPRESS', @catalog = N'AdventureWorks2014'; |
More about sp_addlinkedserver parameters can be found on this link.
Create or update a mapping between a login on the local SQL Server instance and account on a remote SQL Server
After creating a linked server using sp_addlinkedserver procedure:
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver @server = N'WSERVER2012\SQLEXPRESS', @srvproduct = N'SQL Server'; GO |
Use the sp_addlinkedsrvlogin to create or modified a mapping a local logins to the remote logins using the following syntax:
|
1 2 3 4 5 6 7 8 |
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'remote server name', @useself = 'useseif', @locallogin = N'local login', @rmtuser = N'remote user', @rmtpassword = N'remote password'; |
@rmtsrvname is the name of the remote server (linked server) which login mapping applies to:
|
1 2 3 4 |
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'WSERVER2012\SQLEXPRESS'; |
@useself is used to determine how connection with remote server will be established. There are three states ‘True’, ‘False’, ’Null’.
|
1 2 3 4 5 |
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'WSERVER2012\SQLEXPRESS', @useself =N'True'; |
If the ‘True’ state is used, then the current security context of the local login will be passed to the remote login.
Window or the local login credentials, will be used if Windows or SQL Server authentication is selected, respectively
When executing the above SQL code, the following results will appear in the Catalogs folder for SQL Server Authentication and local user ‘Jack’:
Note: To successfully connect to a remote server the user with the exact same username and password must exist on the remote server.
In case when connecting to local SQL Server using SQL Server Authentication with local user login credentials that doesn’t exist on the remote server (e. g. user ‘Ben’), the results under the Catalog folder may look like this:
The same security context is used when creating a linked server with the sp_addlinkedserver procedure:
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver N'WSERVER2012\SQLEXPRESS', N'SQL Server'; GO |
The @useself parameter is varchar(8) data type, by default this parameter is set to ‘True’.
If the ‘False’ value is set to @useself parameter, then the local server login use the arguments specified in the @rmtuser and @rmtpassword parameters to log in to the linked server:
|
1 2 3 4 5 6 7 8 |
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'WSERVER2012\SQLEXPRESS', @useself ='false', @locallogin = N'Ben', @rmtuser = N'Jack', @rmtpassword = N'Jack'; |
Now, when local user ‘Ben’ login to local SQL Server, under the Catalogs folder, all databases that are available on a remote server for the ‘Jack’ remote login will be listed:
For a linked server that doesn’t require username and password (e.g. Microsoft Access), these arguments can be set to Null.
@locallogin is a local login, by default this parameter is set to Null. You can designate either an individual login or all local logins. To specify that all local logins be affected pass a Null to this parameter. If not Null, then in the @locallogin parameter can be a SQL Server login or a Windows login.
@rmtuser is the username of the remote login used to connect to a remote server if @useself is set to ‘False’.
@rmtpassword is the password of the remote user used to connect to a remote server if @useself is set to ‘False’.
Set a SQL Server linked server options
Additionally, you can configure a linked server by using sp_serveroption procedure. Here you can set the vireos of options related to a linked server like: collation compatible, collation name, connect timeout, data access, lazy schema validation, rpc, rpc out, use remote collation etc., by executing the following code:
|
1 2 3 4 5 6 |
EXEC sp_serveroption @server = 'server', @optname = 'option_name', @optvalue = 'option_value'; |
@server is the name of the linked server for which to set the option.
@optname the name of the option to be configured.
@optvalue the value of the option. Valid values are True or On to enable and False or Off to disable the option, a non-negative integer for the connection timeout and query time out options, or collation name for the collation name option.
The following example, enables remote procedure calls to the ‘WSERVER2012\SQLEXPRESS’ linked server:
|
1 2 3 4 5 6 |
EXEC sp_serveroption @server = ' WSERVER2012\SQLEXPRESS', @optname = 'rpc out', @optvalue = 'True'; |
To allow/deny a linked server for distributed query access, use the @optname ‘data access’. The following example, disable distributed query to ‘WSERVER2012\SQLEXPRESS’ linked server
|
1 2 3 4 5 6 |
EXEC sp_serveroption @server = 'WSERVER2012\SQLEXPRESS', @optname = 'data access', @optvalue = 'False'; |
Now when execute a linked server query:
|
1 2 3 |
SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e |
The following error message will appear:
Msg 7411, Level 16, State 1, Line 1
Server ‘WSERVER2012\SQLEXPRESS’ is not configured for DATA ACCESS.
To set the query timeout use the @optname ‘query timeout’. Here can be set how long, in seconds, a remote process can take before time is out. From the example below query timeout is set to 120 seconds:
|
1 2 3 4 5 6 |
EXEC sp_serveroption @server = 'WSERVER2012\SQLEXPRESS', @optname = 'query timeout', @optvalue = 120; |
More about options under the sp_serveroption procedure can be found on this link.
Getting information(settings) about a SQL Server linked server
To see information about linked servers and the referenced data sources, SQL Server Management Studio or the system stored procedures can be used.
Below is the list of the most commonly used system stored procedures.
To see all about login mappings defined against a specific linked server, run the sp_helplinkedsrvlogin procedure.
|
1 2 3 4 5 |
EXEC sp_helplinkedsrvlogin @rmtsrvname = N'remote server name', @locallogin = N'local login' |
@rmtsrvname the name of the linked server for which want to see login setting displayed, by default this parameter is Null
@locallogin the local login that has a mapping to the linked server, by default this parameter is set to Null
In the example below are displayed all login mappings for all linked servers defined on the local machine:
|
1 2 3 4 |
EXEC sp_helplinkedsrvlogin; GO |
The result will be something like this:
| Linked Server | Local Login | Is Self Mapping | Remote Login |
| WSERVER2012\CTP21 | Null | 1 | Null |
| WSERVER2012\SQLEXPRESS | Null | 1 | Null |
| WSERVER2012\SQLEXPRESS | WSERVER2012\Zivko | 1 | Null |
| WSERVER2012\SQLEXPRESS | Ben | 0 | Jack |
Linked Server is the name of the linked server.
Local Login displays which local users are affected by this mapping. If Null is set, this mapping applies to all users who don’t have specific mappings.
Is Self Mapping, if it displays 1, the local login and password are used when connecting to a linked server. If 0 displayed, then the value from the Remote Login column are used for connecting to a linked server. Note, remote password is not displayed for security reason.
To display all login mappings for a specific linked server, execute the following:
|
1 2 3 4 |
EXEC sp_helplinkedsrvlogin @rmtsrvname = N'WSERVER2012\SQLEXPRESS'; |
| Linked Server | Local Login | Is Self Mapping | Remote Login |
| WSERVER2012\SQLEXPRESS | Null | 1 | Null |
| WSERVER2012\SQLEXPRESS | WSERVER2012\Zivko | 1 | Null |
| WSERVER2012\SQLEXPRESS | Ben | 0 | Jack |
To displays all login mappings for a local login, execute the following:
|
1 2 3 4 5 |
EXEC sp_helplinkedsrvlogin @rmtsrvname = NULL, @locallogin = N'Ben'; |
| Linked Server | Local Login | Is Self Mapping | Remote Login |
| WSERVER2012\SQLEXPRESS | Ben | 0 | Jack |
sp_linkedservers – this procedure returns a list of linked servers defined on the local server
|
1 2 3 |
EXEC sp_linkedservers |
sp_catalogs – this procedure displays a list of catalogs for the specified linked server
|
1 2 3 4 |
EXEC sp_catalogs @server_name = N'WSERVER2012\SQLEXPRESS' |
Basically, this displays a list of available databases for the chosen linked server:
sp_tables_ex – shows the table information about the tables for the specified linked server:
|
1 2 3 4 5 6 7 |
EXEC sp_tables_ex @table_server = N'WSERVER2012\SQLEXPRESS', @table_schema = N'HumanResources', @table_catalog = N'AdventureWorks2014', @table_type = N'Table'; |
sp_columns_ex – shows column information for all columns, or a specified column in a remote table
|
1 2 3 4 5 6 7 8 |
EXEC sp_columns_ex @table_server = N'WSERVER2012\SQLEXPRESS', @table_name = N'Address', @table_schema = N'Person', @table_catalog = N'AdventureWorks2014', @column_name = N'City'; |
sp_table_privileges_ex – displays table permissions for a linked server table.
|
1 2 3 4 5 6 7 8 |
EXEC sp_table_privileges_ex @table_server = N'WSERVER2012\SQLEXPRESS', @table_name = N'Address', @table_schema = N'Person', @table_catalog = N'AdventureWorks2014', @fUsePattern = 0 |
sp_column_privileges_ex – displays a list privileges for columns on a specific table for a linked server
|
1 2 3 4 5 6 7 8 |
EXEC sp_column_privileges_ex @table_server = N'WSERVER2012\SQLEXPRESS', @table_name = N'Address', @table_schema = N'Person', @table_catalog = N'AdventureWorks2014', @column_name = N'City'; |
sp_testlinkedserver this procedure tests the connection to a linked server:
|
1 2 3 4 |
EXEC sp_testlinkedserver @servername =N'Server name' |
If the test fails, it returns an error message with the reason of the failure.
Below is an example in which creates a linked server named ‘WSERVER2012’ and then tests the connection:
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_addlinkedserver @server=N'WSERVER2012', @srvproduct = N'SQL Server'; GO |
Test a linked server connection:
|
1 2 3 4 5 6 |
EXEC sp_testlinkedserver @servername = N'WSERVER2012'; GO |
Reason for the failure:
OLE DB provider “SQLNCLI11” for linked server “WSERVER2012” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI11” for linked server “WSERVER2012” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.
Msg 2, Level 16, State 1, Line 6
Named Pipes Provider: Could not open a connection to SQL Server [2].
To see the settings for a linked server from SQL Server Management Studio, right click on linked server under the Linked Server folder and chose Properties command:
This will open the Linked Server Properties dialog:
On the top right side of the dialog, there are three tabs (General, Security, Server Options) on which can be seen all settings for the chosen a linked server.
To show all setting s for a chosen linked server in a query editor, right click on the name of a linked server under the Linked Servers folder, from the context menu chose Script Linked Server as -> Create to -> New Query Editor Window command:
This will create a .sql script with all settings that contains the chosen linked server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
USE [master] GO /****** Object: LinkedServer [WSERVER2012\SQLEXPRESS] Script Date: 6/27/2017 1:56:57 PM ******/ EXEC master.dbo.sp_addlinkedserver @server = N'WSERVER2012\SQLEXPRESS', @srvproduct=N'SQL Server' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'WSERVER2012\SQLEXPRESS',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'WSERVER2012\SQLEXPRESS',@useself=N'False',@locallogin=N'Ben',@rmtuser=N'Jack',@rmtpassword='########' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'WSERVER2012\SQLEXPRESS',@useself=N'True',@locallogin=N'WSERVER2012\Zivko',@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'data access', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'rpc', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'rpc out', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'query timeout', @optvalue=N'120' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'WSERVER2012\SQLEXPRESS', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO |
Delete a mapping
To delete an existing mapping between a local login and login on a linked server use the sp_droplinkedsrvlogin system stored procedure.
|
1 2 3 4 5 |
EXEC sp_droplinkedsrvlogin @rmtsrvname = N'Remote server', @locallogin = N'Local login’; |
@rmtsrvname is the name of a linked server that the login mapping applies to. A linked server must exist under the Linked Server folder, otherwise the following error message my appears when execute the sp_droplinkedsrvlogin procedure:
Msg 15015, Level 16, State 1, Procedure sp_droplinkedsrvlogin, Line 32 [Batch Start Line 0]
The server ‘WSERVER2012’ does not exist. Use sp_helpserver to show available servers.
@locallogin is the local login that has mapping to a linked server. A mapping for local login to a remote server must exist, otherwise the following error message may appear:
Msg 15007, Level 16, State 1, Procedure sp_droplinkedsrvlogin, Line 51 [Batch Start Line 0]
‘Jana’ is not a valid login or you do not have permission.
If the @locallogin property is set to Null, the default mapping which is created when a linked server is created by sp_addlinkedserver procedure will be removed.
The following example will remove the login mapping for the ‘Ben’ local user to the ‘WSERVER2012\SQLEXPRESS’ linked server:
|
1 2 3 4 5 |
EXEC sp_droplinkedsrvlogin @rmtsrvname = N'WSERVER2012\SQLEXPRESS', @locallogin = N'Ben'; |
This code will remove the default login mapping for all users to the ‘WSERVER2012\SQLEXPRESS’ linked server:
|
1 2 3 4 5 |
EXEC sp_droplinkedsrvlogin @rmtsrvname = N'WSERVER2012\SQLEXPRESS', @locallogin = Null; |
Delete a SQL Server linked server
To remove a linked server, form the Linked Server folder use the sp_dropserver procedure:
|
1 2 3 4 5 |
EXEC sp_dropserver @server = N'Server', @droplogins = ‘droplogins’ | NULL |
@server is the name of the server that will be removed.
@droplogins if the ‘droplogins’ value is set, then all logins for the specified remote server will be removed, by default the @droplogins property is set to Null.
Now, when executing this code:
|
1 2 3 4 5 |
EXEC sp_dropserver @server = N'WSERVER2012\SQLEXPRESS', @droplogins = NULL; |
The following error message will appear:
Msg 15190, Level 16, State 1, Procedure sys.sp_dropserver, Line 56 [Batch Start Line 3]
There are still remote logins or linked logins for the server ‘WSERVER2012\SQLEXPRESS’.
To resolve this problem, there are two solutions:
- Remove all logins related to a linked server by using the sp_droplinkedsrvlogin procedure and then execute the above mentioned code
- Instead of a Null value for the @droplogins property in the sp_dropserver procedure, just put the ‘droplogins’ value and all logins related to a chosen linked server will be removed together with a linked server:
|
1 2 3 4 5 |
EXEC sp_dropserver @server = N'WSERVER2012\SQLEXPRESS', @droplogins ='droplogins'; |
Other articles in this series:
- How to create and configure a linked server in SQL Server Management Studio
- How to query Excel data using SQL Server linked servers
- How to configure a Linked Server using the ODBC driver
- How to create a linked server to an Azure SQL database
He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.
In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.
See more about Marko at LinkedIn
View all posts by Marko Zivkovic
- How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
- How to install MySQL on Ubuntu - March 10, 2020
- Using SSH keys to connect to a remote MySQL Server - November 28, 2019