SQL Server Linked Server

SQL Server Linked Server allows you to access data from another sql database or another database platform like oracle or ms access (you will need odbc connections)

Example: selecting data from another database using linked server

In this example, I want to access data from sql server desktop-qwertyuio, adventureworks2012 db and contact type table

To resolve this issue, we must use a linked server

--SELECT DATA FROM THE DATABASE

SELECT [ContactTypeID]
,[Name]
,[ModifiedDate]
FROM [DESKTOP-QMOOH4U].[AdventureWorks2012].[Person].[ContactType] --<< FULLY QUALIFIED NAME (SERVER.DB.SCHEMA.TABLE)
GO

--INSERT INTO SOURCE ([DESKTOP-QMOOH4U\DEV]) DATABASE DATA FROM DESTINATION ([DESKTOP-QMOOH4U]) DATABASE

--DROP TABLE [PROD].[DBO].[Contacts] 

SELECT * INTO [PROD].[DBO].[Contacts] 
FROM [DESKTOP-QMOOH4U].[AdventureWorks2012].[Person].[ContactType]

--VERIFY

USE [PROD]
GO

SELECT *
FROM [dbo].[Contacts]
GO


--SCRIPT FOR LINKED SERVER:

USE [master]
GO

EXEC master.dbo.sp_dropserver @server=N'DESKTOP-QMOOH4U', @droplogins='droplogins'
GO

EXEC master.dbo.sp_addlinkedserver @server = N'DESKTOP-QMOOH4U', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DESKTOP-QMOOH4U',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DESKTOP-QMOOH4U',@useself=N'True',@locallogin=N'DESKTOP-QMOOH4U\RAF',@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DESKTOP-QMOOH4U', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

To see all created linked servers in SSMS, under Object Explorer, chose the Server Objects folder and expand the Linked Servers folder:

To create a linked server in SSMS, right click on the Linked Servers folder and from the context menu select the New Linked Server option:

The New Linked Server dialog appears:

In this dialog, the name of a linked server and server type must be identified. The linked servers can be defined for different kind of data source if the Other data source radio button is chosen. For the purpose of this article, the SQL Server radio button under the Server type section will be chosen and in the Linked server text box, a name of the server will be entered:

If the SQL Server type is chosen to configure a SQL Server linked server, the name specified in the Linked server text box 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).

Otherwise the following error may occur when the OK button is pressed:

To set how a user would authenticate to the WSERVER2012\SQLEXPRESS instance, under the Select a page section on upper left corner of the New Linked Server dialog, select the Security item:

SQL Server Linked Server

Here, different ways to authenticate the linked server can be set.

Under the Local server login to remote server login mappings, two ways of local logging to a remote login can be set. One way is to associate a local login with a remote login and other way is to impersonate.

Local Login

In the Local Login field, will be listed all the local logins. The local login can be an SQL Server Authentication local login:

Or a Windows Authentication login:

Now, when clicking 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 happens because the Impersonate check box isn’t checked.

Impersonate

The Impersonate check box when is checked passes the local login credentials to the linked server. For SQL Server Authentication, the same login with the exact credentials must exist on the linked server, otherwise when connected to the server with the SQL Server Authentication, the list of the databases under the Catalogs folder may look like this:

For Windows logins, the login must be a valid login on the linked server. In order to use impersonation, the delegation between the local server and the linked server must be set.

Let’s create a linked server using the local Windows login. From the Local Login combo box, choose the local Windows login and check the Impersonate checkbox and press the OK button:

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, with Miki or Zivko credentials, under the Catalogs folder, all databases that are available on a remote server for the Nenad 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: