LINKED SERVER

                                                                  LINKED SERVER

What is linked server?

Linked servers are configured to allow you to connect to other database instance on the same server or another machine or remote servers. This allows the SQL server to execute the SQL scripts against the OLEDB data sources on remote servers (the remote sources can be SQL, Oracle, etc. those Databases that supports the OLE DB can be used for linking servers)   using OLE DB providers.

How to create a linked server?

There are two ways of creating a linked server first, is using the GUI (Graphical User Interface) in SSMS and second, using the T-SQL script.

Using the GUI (Graphical User Interface) in SSMS:

1.   

       1. In the SSMS, expand Server Objects and right click on the Linked Servers and the click on the New Linked Server option.

      

      

      2. Then a New Linked Server window appears. Where you can enter the information related to new     linked server. In the General page, under the Linked server enter the name of the Linked Server we are about to create (you can enter any name of your choice). In my example, I’m creating a Linked Server with the name RAIN_SQLLINKEDSERVER. Then under the Server type select Other Data     source.

      

      

      3. Under the Other Data source for Provider choosing SQL Server Native Client 11.0 as I am using SQL Server 2012.For Product name select SQL SERVER and for Data source enter the name of the remote SQL Server Instance name to which you want to connect. If the remote SQL Server instance is a named instance then enter the data source as MACHINENAME\INSTANCENAME.

      

      

      Leave the Provider string blank and Catalog option is optional (If entered use the default database you will be using).

      

      4. Then click on security from the left pane. Under the security you could see some options

      

      Local login: Specify the local login name that connect to the linked server. This local login can be either a login using SQL Server Authentication or Windows Authentication.

      Impersonate: Pass the username and the password from the local login to the linked server. For SQL Server Authentication the login with the exact same name and password must exist on the remote server. For Windows Authentication, the logins must be a valid login on the linked server.

      Remote User: Use the remote user to map users not defined in the Local login. The remote user must be the SQL Server Authentication login on the remote server.

      Remote password: Specify the password of the remote user.

      Add: You can add a new local login by clicking this.

      Remove: You can remove an existing login.

      Not be made: Specify that a connection will not be made for the logins not defined in the list.

      Be made without using a security context:  Specify that the connection will be made to the linked server without using the security context for logins not defined in the list.

      Be made using the login’s current security context: Specify that the connection can be made using the security context of the login for logins not defined in the list. If connected to the local server using the windows authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, a login name and password will be used to connect to the remote server.In this case the login with the exact same name and password must exist on the remote server.

      Be made with using this security context: Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

      So, from all the security options above I’m going to select the last option Be made using this security context option. If you select this option make sure that the username with that password must exist on the remote server.

      

      6. Next click on the Server options on the left side pane and under that you have options to select the Server Options. For example, you can select Data Access as True if you want to access the data on the remote server using the Linked server and you can also enable the RPC (remote procedure calls) against a remote server to True. This option enables us to validate the remote login. And there are other options where you can select of your choice.

      

      7. Then click OK, the new linked server is created. You can see the new linked server created under the Linked Server in the Server Objects.

USING T-SQL Script

The other method of creating the Linked server is by using the T-SQL code which is shown below:

The following is the syntax to add a linked server:

EXEC sp_addlinkedserver

@server = ‘SERVERNAME’ (Name of the linked server to create)

@srvproduct = ‘SERVER PRODUCT NAME’ (This is the product name of the OLE DB data source to add as a linked server),

@provider = ‘PROVIDER NAME’ (Is the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source.),

@datasrc = ‘INSTANCE NAME or MACHINENAME/INSTANCE NAME (if it’s a named instance)’;

The following is the syntax to add a login in the security context:

EXEC sp_addlinkedsrvlogin

@rmtsrvname =’Linked server name’,

@useself = ‘false’,(this determines whether to connect to remote server by impersonating the local logins or explicitly submitting a login or password)

@locallogin = ’local login’,(is the login on the local server)

@rmtuser = ‘rmtuser’, (Is the remote user used to connect to the remote server when @useself is false)

@rmtpassword = ‘rmtPassword’(Password associated with the remote username);

Example :

Here in my example I’m adding one SQL Server as Linked Server

EXEC sp_addlinkedserver

@server = ‘RAIN_SQLLINEKDSERVER’,

@srvproduct = ‘SQL SERVER’,

@provider=’SQL Server Native Client 11.0’,

@datasrc=’RAIN-SQL’;

If the remote SQL Server does not have the instance name just enter the server name in the @datasrc.

The below example creates a mapping to make sure that the windows user ‘Rain\shiny’ connects through to the linked server accounts by using the login ‘ShinyP’ and password ‘abcd123’.

EXEC sp_addlinkedsrvlogin

@rmtsrvname = ‘RAIN_SQLLINEKDSERVER’,

@useself = ‘false’

@locallogin = ‘Rain\shiny(Local login name that connect to the linked server)’,

@rmtuser = ‘ShinyP’,

@rmtpassword = ‘abcd123’;