Often you need to access data on a server that lives on a separate server. Fortunately SQL Server lets you create a Linked Server to join Server A to Server B. Once linked you can query against the Linked Server B just as if it was a local server.
To create the Linked Server run the following command on Server A:
EXEC master.dbo.sp_addlinkedserver @server = N'TheNameYouWantForYourLinkedServer', @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI10', @datasrc=N'TheActualDatabaseServerName\Instance,PORT' GO
@server – This is just the friendly name that the Linked Server B will be referenced as. This can be anything you want.
@srvproduct – This is the data connection you are making. It will always be SQLNCLI for MS SQL Server. This will vary if you are linking to an Oracle or other server type.
@provider – The example above is specifying that SQL Native Client 10 (this is a SQL Server 2008 server) should be used. Again this will differ depending on the edition/type of database server you are linking.
@datasrc – This is the server name that you are linking. In the above example, we are also specifying an instance name (as we are linking a named instance of SQL Server) as well as a port number because the named instance is not using the default. If you are linking a default instance listening on the default port you do not need to specify these two things.
Two Hops This Time:
No, we’re not talking about the terrible song by Mr. C. What we are talking about is an error you may get if you are trying to connect to Server A using SSMS on your local workstation. First, here is the error:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
So, what gives? Well, it’s that second hop that’s getting you! Here it is in straight English. Your local workstation (via SSMS) connects to Server A (First Hop). Server A then passes along the query to Linked Server B (Second Hop). Essentially what is happening is Server A is just throwing the query to Linked Server B using an anonymous logon which is then being rejected by Server A.
Ok, we know what’s wrong… How do we fix it? Well, the first option (and it isn’t going to be covered here) is to switch from NTLM authentication to Kerberos. That is well outside the technical abilities of this author so I’m going to leave you with your good friend Google.
How we fix this issue is by using a SQL Server authentication account to handle the communication between Server A and Linked Server B.
Run this command to grant a SQL Server account access to Linked Server B:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TheNameOfTheLinkedServerYouCreated', @useself=N'False', @locallogin=NULL, @rmtuser=N'SQLAuthUserName', @rmtpassword='SQLAuthUserAccountPassWord'
Of course, the SQL Auth user you grant to this Linked Server must exist on the actual SQL Server being linked and have permission to the database(s) you are trying to access via the Linked Server. We typically create a generic account like ‘linked_user’ and then give it the minimum permissions we need for the minimum amount of database in question. That way if the account should ever be compromised the risk is minimal.
So, now when your local workstation (via SSMS) connects to Server A the query is passed along to Linked Server B using the SQL Auth account which has permission.
By the way, the syntax for executing a query against the Linked Server B is as follows:
SELECT * FROM [LinkedServerName].[DatabaseName].[Schema].[TableName]
Most of this can be done via the GUI in SSMS as well.