If you have ever used Linked Servers within SQL then you will appreciate the benefits of being able to connect directly from one SQL Server to another without the need to form an independent connection. Linked Servers have been around for ages and many systems would struggle to operate without them.
One of the biggest benefits of Linked Servers is the fact that you can jump SQL version, allowing your system to access data from a legacy system using an older version of SQL Server. Unfortunately though, since the launch of SQL Server 2012, SQL Server now uses a new and improved native client that no longer supports anything further back than SQL Server 2005.
While for most of you this would not cause an issue, some companies are still rocking the old 32bit servers with SQL Server 2000 and may not be able to upgrade due to legacy systems.
For some this may stop companies upgrading to the later versions of SQL Server, but hold on, not all is lost. There is a work around, and the best of it is, is that it’s an ‘easy fix’.
First thing first, we need to give SQL Server the tools it needs to access these older version of SQL Server. Being that it is the native client that has been changed, the fix for this is quite simple; install an older version of the SQL Server 2008 Native Client.
The native client is part of the SQL Server 2008 Feature Pack which can be found here https://www.microsoft.com/en-us/download/details.aspx?id=27596. Simply locate the sqlncli install file for the relevant system architecture and install it on your new SQL Server.
Once this is installed, SQL Server will have the ability to connect to other SQL Servers using the SQL Native Client v10 (rather than the new v11).
The next thing is to add the actual Linked Server. I personally have had issue with trying to add the linked server through the UI of Management Studio and due to this I am going to suggest that you perform the next step using SQL commands.
Without further ado, open Studio Manager, connect to your new SQL Server and open a new query window for the master database.
To create the linked server, enter the following code in the query window:
EXEC [sp_addlinkedserver] @server = N'MyLinkedServer', @srvproduct = N'', @provider = N'MSDASQL', @provstr = N'DRIVER=
SQL Server;SERVER=MyServer\MyInstance;'; GO
This will create a new linked server between your new SQL Server and your old SQL Server. Once this is done, you can then use the UI to add any user impersonation settings. Alternatively, you can also do this via SQL using the following statement:
EXEC [sp_addlinkedsrvlogin] @rmtsrvname = N'MyLinkedServer', @useself = N'False', @locallogin = N'sa', @rmtuser = N'sa', @rmtpassword = N'password'; GO
And there you go. Your servers are now linked. I hope this tutorial is helpful and as usual, constructive criticism and suggestions are welcome.