Creating Linked Server from SQL 2012 to SQL 2000

We all know that creating linked server from SQL 2012 to SQL 2000 does not work using the new native client SQLNCLI11. This client only connects to the following: 2005, 2008, 2008R2. Currently for one of my clients, we are virtualizing the environment to OS & SQL 2012, thus the need for this blog to show the workaround. I will highlight the steps needed to be performed on Win 2012.

Test Scenario

Old Source – SQL 2012(Sp2 with hotfix) on Win 08R2 || Destination – SQL 2000(Sp4) on Win 03

New Source – SQL 2012(Sp2 with hotfix) on Win 2012R2|| Destination – SQL 2000(Sp4) on Win 03

1. Tried to create a net new linked server using the new client and the following error was generated.

SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions. OLE DB provide “SQLNCI11” for linked server “xxxxx” returned message “Client unable to establish connection”. (Microsoft SQL Server, Error:22)

2. Scripted out the linker server (from old source) and tried to execute it on the new source and got the following error. This error indicates that ODBC connection cannot be located.

Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “xxxxx“. OLE DB provider “MSDASQL” for linked server “xxxxx” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. (Microsoft SQL Server , Error:7303)

Here is part of the scripted linked server:

/****** Object:  LinkedServer [xxxxx]    Script Date: 4/7/2015 9:57:51 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'xxxxx', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc=N'xxxxx', @location=N'System'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'xxxxx',@useself=N'False',@locallogin=NULL,@rmtuser=N'testmeuser',@rmtpassword='testmepass'

 Workaround

• RDP to the box
• Goto Control Panel–> System and Security–> Administrative Tools
• In Windows 2102 you will see 2 ODBC Data Source
  • ODBC Data Sources (64-bit)
  • ODBC Data Sources (32-bit)
• Select ODBC Data Sources (64-bit)–> System DSN(tab)
  • Add
  • Select SQL Server
  • Enter
    • Name of DataSource
    • Description (for documentation purposes)
    • Server (the Destination Server)
  • Select your mode of login (in my case we used SQL login for Linked Servers) & provide username and password
  • Leave all options as is on the remaining pages and click on Finish
  • Make sure to Test Data Source

Go back to your linked server script and plug in the values:

• @provider=N’MSDASQL’
• @datasrc=N’datasourcename’ (Name of DataSource provided)
• @rmtsrvname=N’servername’ (Name of Server provided)
• @rmtuser=N’testmeuser’ (User provided)
• @rmtpassword=’testmepass’ (Password provided)

This will allow you to create your linked server to SQL 2k using ODBC DSN. Surprisingly there are lot of organizations still using SQL 2k and if you are one of them drop a comment and share with the community of how many instances /dbs.

~ Adios

Khan

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |

Follow me on Twitter