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
