To totally unlock this section you need to Log-in
In SQL Server environments the linked server functionality is commonly used to connect to another (remote) database or file (Xls, CVX) using SQL Server Management Studio (SSMS) and access and use the remote data and objects. In this way you can write SQL queries or stored procedures from SSMS directly on a remote database on another machine. In Oracle environments this functionality is called DBLinks (Database Links).
As a quick example, we can consider a scenario where we need to create a view (saved query) on a database which receives data from a database that resides on a remote server. The other database can be SQL Server, Oracle or another supported database: we could use linked servers to establish connections to a set of source systems available on a datawarehouse/BI environment or multiple databases.
In this way if someone asks for a specific data of a set of data or complex data retrieve, we can always search and extract data from multiple and remote databases in couple of seconds (or minutes) by writing a query using linked servers.
More specifically, linked servers allow submitting a T-SQL statement on a specific SQL Server instance, and then it returns data from other SQL Server instances. A linked server allows joining data from several SQL Server instances using T-SQL statements when data exists on multiple databases on different SQL instances. The only configuration needed to retrieve data from different SQL instances using linked servers functionality is to establish a connection (using proper credentials) to the specific remote SQL server and instance.
In SQL Server are available two methods to configure linked server, using SSMS. The first one is by using sp_addlinkedserver system stored procedure and the second one is by using directly SQL Server Management Studio (SSMS) GUI interface.
From now on you will see how to configure a linked server using a SQL Server data source. To check and see all available linked servers in SSMS you will need to go to Object Explorer, chose the Server Objects folder and expand the Linked Servers folder:
Now to create a linked server in SSMS make a right-click on the Linked Servers folder and from the context menu select the New Linked Server option:
The New Linked Server dialog will appear:
In this new window, you will have to specify the name of the linked server (in the field of Linked server) and the server type you will connect (usually it will be used the Other data source option). The Other data source radio button can give us the possibility to define different data source (not only databases): in this article we will use only the SQL Server radio button under the Server type section and in the Linked server text box, an example name of the server will be entered:
If the server type called SQL Server is chosen then the name specified in the Linked server text field must be the name of the remote SQL Server.
If a SQL Server instance is default instance, type the name of the computer that hosts the instance of SQL Server (e.g. WSERVER2012). If the SQL Server is a named instance, type the name of the computer and the name of the instance separated by slash (e.g. WSERVER2012\SQLEXPRESS).
If you try to connect to a named instance without following the above instructions the following error could occur when the OK button is pressed:
No we have to specify how a user would authenticate to the SQL Server instance instance and we will do this by going under the Select a page section, on upper left corner of the New Linked Server dialog and finally select the Security item:
Under the Security section ther will be available different ways to authenticate the linked server and these parameters are really important to allow several operations over the remote SQL Server using T-SQL statements (simple queries, stored procedures, etc.).
Under the Local server login to remote server login mappings text, there are two ways of to map a local login to a remote login: the first one is to associate a local login with a remote login and the second one is to impersonate.
Local Login
In the Local Login field, will be listed all the local logins available on SQL Server (Domain or SQL Logins). The local login can be a SQL Server Authentication local login:
But it also can be a Windows (Active Directory) login:
Now, when you will click the OK button on the New Linked Server dialog, the following error message will appear:
The login mappings should either be impersonate or have a remote login name.
See the image below:
This basically happens because we don't have specified any "mapping" between the Local login and another credential (remote or impersonated over the remote SQL Server).
Now we will see first the Impersonate option and what this option will imply for the connection, and then we will see the Remote login option (and probably this is the most used type of mapping used between different SQL Servers not joined on the same domain).
Impersonate
The Impersonate check box is usued to pass the local login credentials to the linked server. If Impersonate is used, for SQL Server Authentication, the same login with the exact credentials (username and password) have to exist on the linked server (remote SQL Server); if those credentials does not exist, SQL Server Management Studio will connect to the server but the list of the databases under the Catalogs folder may look like this (void):
Even for Windows logins, the login must exist and be a valid login on the remote SQL server. In order to use impersonation, the delegation between the local server and the linked server must be set.
Creating a linked server using the local Windows login we will need to specify the local Windows login and check the Impersonate checkbox and press the OK button:
This time, under the Catalogs folder, all databases that are located on the linked server will be listed:
Remote User
The remote user option allows users from the local SQL server to connect to the linked SQL server even though their credentials aren’t present on the remote server by using the credentials from the user that exists on the remote server. Basically, it allows local logins to connect to a remote server as a different login that must exist on a remote server.
Remote Password
Specify the password of the remote user.
From the Local Login drop down list, choose a local login which should map to a remote login. On the Remote User field, enter the name of the remote user that exists on the remote server and in the Remote Password filed, enter a password of that remote user. Then, press the OK button:
Now, when connected to the local server using SQL Server Authentication, under the Catalogs folder, all databases that are available on a remote server for the remote login will be listed.
Additionally, on the Linked Server dialog, it can be identified how logins that are not set in the Local server login to remote server login mappings list will connect to the linked server, for that there are four options that can be used and they are located under the For a login not defined in the list above, connections will section:
Not be made
If this radio button is chosen, any users that aren’t identified in the Local server login to remote server login mappings list cannot establish connection to the linked server.
For example, if login with different account (e.g. Ben) that not set in the login mapping list the list of the databases under the Catalogs folder will look like this:
If you attempt to execute a linked server query:
SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e
The following result will appear:
Msg 7416, Level 16, State 1, Line 1 Access to the remote server is denied because no login-mapping exists.
Be made without using a security context
The Be made without using a security context option is used for connecting to data sources that do not require any authentication, for example like a text file. When this option is selected for connect to a linked server, will have the same effect as selecting the “Not be made” option.
If you attempt to execute a linked server query:
SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e
The following message e may appear:
OLE DB provider “SQLNCLI11” for linked server “WSERVER2012\SQLEXPRESS” returned message “Invalid authorization specification”. Msg 7399, Level 16, State 1, Line 1 The OLE DB provider “SQLNCLI11” for linked server “WSERVER2012\SQLEXPRESS” reported an error. Authentication failed. Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “SQLNCLI11” for linked server “WSERVER2012\SQLEXPRESS”.
Be made using the login’s current security context
If this option is chosen, it will pass the current security context of the local login to the remote login. If Windows Authentication is used, the windows credentials will be used to connect to a remote SQL server. If SQL Server Authentication is used, then the local login credentials will be passed to remote SQL Server. Note, to establish connection to remote server successfully, then the user with the exact same credentials must exist on the remote server otherwise when execute a linked server query:
SELECT * FROM [WSERVER2012\SQLEXPRESS].AdventureWorks2014.HumanResources.Employee e
The following message will appear:
Msg 18456, Level 14, State 1, Line 1 Login failed for user ‘Ben’.
Be made using this security context
The fourth option under the For a login not defined in the list above, connections will section is Be made using this security context. In the Remote login and With password fields, enter the credentials of the SQL Server Authentication login that exist on a remote server, otherwise the following error may occur:
The last item under the Select a page menu is the Server Options item. When selecting this option, the following window will be shown:
Collation Compatible
The first option is the Collation Compatible option. This option is used to identify if the linked server has the same collation as the local server. This option should set to True only if is known that the linked server has the same collation as the local, otherwise it should be set to False (default).
Data Access
This option is used to allow/deny access to the linked server data. If this option is set to False, the access to remote will be denied. This option is useful to disable access to a remote server temporally. The following message will appear when execute a linked server query and this option is set to False:
Msg 7411, Level 16, State 1, Line 1 Server ‘WSERVER2012\SQLEXPRESS’ is not configured for DATA ACCESS.
By default, the option is set to True.
RPC and RCP Out
This RCP (Remote Procedure Call) is used to enable the access to remote procedures to be called from the linked server or to be called to the linked server.
If these options are set to False, the following error will appear when some procedures from the linked server are called:
Msg 7411, Level 16, State 1, Line 4 Server ‘WSERVER2012\SQLEXPRESS’ is not configured for RPC.
By default, the False value are set for the RPC and RCP Out options.
Use Remote Collation
When this option is set to True, the collection of remote columns will be used and collection specified in the Collation Name filed will be used for data source that are not SQL Server data source, but if the option is set to False then the collation for the local server will be used. By default, is set to False.
Collation Name
If the Use Remote Collation filed set to True, this option is used to specify the collation name of the linked server for the data source that is not SQL Server data source. When chose a collation name, it must be a collation that SQL Server supports.
Connection Timeout
This option is used to set the maximum time the local server should wait for to get a connection to the linked server SQL Server instance. If 0 (zero) is set, then the server option remote login timeout is used. By default, 10 second is set for this option. Note, the default value for SQL Server 2008 is 20 seconds.
Query Timeout
This option is used to set how long, in seconds, a remote process can take before time is out. The default value is 600 second (10 minutes). To disable query timeout put 0 (zero) in this field and the query will wait until it is completed.
Distributor
In this option, it can be specified whether the linked server is participating in replication as a distribution Publisher.
The Distributor is a database instance, that acts as a store for replication specific data associated with one or more Publishers.
Publisher
In this option, it can be set whether the linked server to be a replication publisher or not. If True, the linked server is a publisher. Otherwise, is not.
The Publisher is a database instance, that makes data available to other locations through replication.
Subscriber
In this option, it can be specified whether the linked server is a replication subscriber or not.
A Subscriber is a database instance, that receives replicated data.
Lazy schema validation
This option checks schema changes that have occurred since compilation in the remote tables. If this option is set to False (default state), SQL Server checks changes before the execution of a query and if there are some changes, it recompiles the query. If the Lazy schema validation is set to True, a SQL Server delay schema checks the remote tables until query execution.
Enable Promotion of Distributed Transactions
This option is used to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. If this option is set to True calling a remote stored procedure starts a distributed transaction and enlists the transaction with MS DTC.
Now, when everything is set, click the OK button on the New Linked Server dialog. A newly created linked server will appear under the Linked Server folder.
To test that linked server if it works properly, go right-clicking on that linked server and choose Test Connection:
If a connection with linked server is established successfully, the following info message box will appear:
Otherwise, an error message will be displayed that shows a problem that prevents connection to be successfully established:
Querying data using a linked server
Querying data using the linked server is a little bit different then querying data from the local SQL Server. In the normal queries, usually, two part notation is used [Schema].[ObjectName], for example db1.Employee:
SELECT * FROM db1.Employee e
When querying a table from a linked server, the fourth part notation is used LinkedServer.Database.Schema.ObjectName. To get data from the Employee table which is located in a database on the linked server, querying code will look like this:
SELECT * FROM [DBSERVER\SQLEXPRESS2016].[AdventureWorks2014].[db1].[ Employee]
Deleting a linked server
To delete a linked server, under the Linked Servers folder, right click on the linked server and from the context menu choose the Delete command:
This will open the Delete Object window dialog:
Click the OK button and from the message box, choose the Yes button:
If everything goes well the linked server will be removed from the Linked Servers folder.
Creating a Linked Server using Stored Procedures
As an alternative to using the options in the user interface, you can execute stored procedures in Management Studio to create your linked server.
To create a linked server using stored procedures:
- In Management Studio (SSMS), connect to your Database Engine instance.
- In the toolbar at the top of Management Studio, click New Query.
- In the editor window that appears, type the following statement for creating the linked server, where [LinkedServerName] is the name of the linked server, [DataStore] is the type of data store that you are linking to SQL Server, and [DSNName] (Connector Name) is the name of your DSN for the data store:
EXEC master.dbo.sp_addlinkedserver @server=N'[LinkedServerName]', @srvproduct=N'[DataStore]', @provider=N'MSDASQL', @datasrc=N'[DSNName]'
If your data store requires authentication, then type the following statement, where [LinkedServerName] is the name of the linked server and [UserName] and [Password] are your credentials:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'[LinkedServerName]', @useself=N'False', @locallogin=NULL, @rmtuser=N'[UserName]', @rmtpassword=N'[Password]'
- In the toolbar at the top, click Execute.
- The Messages pane opens and displays a message indicating whether the statements were executed successfully.
- In the Object Explorer, select Linked Servers and then click Refresh.
- The linked server appears as a node under the Linked Servers branch in the Object Explorer, and you can expand the node to browse the contents of the server down to the table level.
 |