Adding a linked server in MS SQL Server

A linked server allows you to connect and query against a database on another MS SQL Server instance even if it is on another machine. Once you have created a linked server you can use the tables in the linked server just like you would use a local table (in most cases).

Let’s assume you have to instances of MS SQL Server on two different machine. On the first instance (called it SQL1) you have a database called MyDB1. On the second instance (call it SQL2) you have a database called MyDB2. Now we want to run a query from MyDB1 on MyDB2.

In general here is what we need to do

1. Create a linked server
2. Specify the credentials that the link will use

Specifically here is what we need to do.

1. Open SQL Management Studio and open a query window for MyDB1.
2. Create the linked server. I prefer the SQL statement way of doing this as I think the UI in MS SQL Management Studio under Server Object | Linked Server is confusing to use since it is made to work for all kinds of server besides MS SQL server. 
   
   Customize (change the items in red and green) the SQL statement below to point to your stuff and then execute it in the query window. Please note that the item in red is what will show up under the Server Objects | Linked Servers in SQL Server Management Studio. The item in green is the name of the database you want to get access to on the remote server.
   
   EXEC master.dbo.sp_addlinkedserver
   @server = N'MyDB2LinkedServer',
   @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI', @datasrc=N'SQL2',
   @catalog=N'MyDB2'
   
3. I can never seem to get Windows credentials to work for linked servers, so I usually go with a named SQL Server user. The example below uses SQL Server user named DB2User and has a password of db2user. Please use a better password in a real environment.
   
   Customize (change the items in red and green) and execute the sql statement in the same query window. Please note the item in red MUST match what you used in the first statement (above). The items in green must be the username and password of the user / login on the remote server you are trying to access and must have access to the database you are trying to access on that same remote server.
   
   EXEC sp_addlinkedsrvlogin 'MyDB2LinkedServer', 'false', NULL, 'DB2User', 'db2user'

 

Now that you have the link established, it should show up in SQL Server Management Studio under Server Objects | Linked Servers. If it doesn’t try right-clicking the Linked Servers node and choose Refresh.

Now that you have a link it is time to give it a try. We’ll do this by selecting from table on the remote server from the local server. Using the scenario discussed earlier, follow the steps below to test your linked server.

1. In the same query window or a new one. For a new one, open SQL Management Studio and open a query window for MyDB1.
2. Customize the statement below for your stuff and execute it.
   
   select count(1) from MyDB2LinkedServer.MyDB2.dbo.MyTable1
   
   Here we are selecting from a table called MyTable1 on the MyDB2 database on SQL2 all the while we are still connected to MyDB1 on SQL1. If you tables are not in the dbo schema you will need to change dbo to be the name of htat schema. Typically you can tell just by looking in SQL Server Management Studio and browsing the tabes. If there is just a table name, then it is almost certainly dbo. If it same xxx.MyTable1 then the schema is called xxx and the above statement will need to have have dbo replaced with xxx.
   
   Pretty cool. You can then join tables, update and update rows, etc just as you would any other tables in your local database.