Create Linked Server in SQL Server

Follow the below steps to create Linked Server : 

Step 1 : Open SQL Server Management Studio

Step 2 : Expand “Server Objects”

Step 3 : Right Click on “Linked Servers” and then click on “New Linked Server”

Step 4 : Write the appropriate Linked Server Name and also click on Radio Button “SQL Server” under general tab :

Step 5 : Now go to Security Tab on the same window and click on Add button :

Step 6 : Once we click on add button, a new line will be added with few options :

1-      Local Login : you have to fill your current instance (server) name.

2-      Impersonate : If we have “Linked Server” with Windows Authentication then we have to click on this checkbox, otherwise not.

                  3-      Remote User/ Remote Password : : If we have “Linked Server” with Server Authentication                     then we have to enter username and password in the appropriate boxes.

Step 7 : Now click on OK button to link the server with current instance.

Step 8 : Now you can see your linked server in “Object Explorer” under the path : “Server Objects\Linked               Servers\<your Linked Server Name>” 

We can also create a linked server by using T-SQL query :

EXEC sp_addlinkedserver 

@server=N'COMPUTER_A',     -- Enter Linked Server Name

@srvproduct=N'',             -- Not Needed

@provider=N'SQLNCLI',     -- Enter the SQL Server Driver Here

@datasrc=N'COMPUTER_A\SQLEXPRESS';  -- Enter the Instance Name Here

Posted By : MITESH GUPTA