Advertisements
Home > SQL Server 2012, SQL Tips > SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround

SQL Server 2012 does not support Linked Server to SQL Server 2000, workaround


Yes, you read it right, “SQL Server 2012” has stopped connecting to “SQL Server 2000” via linked Servers. As this new version uses a new Native Client version i.e. SQLNCLI11, instead of the old SQLNCLI10. This new client only connects back to 2008R2, 2008 and 2005 only.

– I upgraded my Database Servers from “SQL Server 2008 R2” to “SQL Server 2012”.

– Restored the databases from the backup taken from 2008R2.

– Ran the jobs and found that one of them was failing with following error:
 

Error Message:
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Client unable to establish connection”.
Msg 22, Level 16, State 1, Line 0
SQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions.
OLE DB provider “SQLNCLI11” for linked server “NorthWind2000” returned message “Invalid connection string attribute”.


 

As I have upgraded from 2008 R2, so I checked Providers under the Linked Server and found that with the new SQLNCLI11, I still have the “SQL Server Native Client 10.0” i.e. “SQLNCLI10”.

So, I tried to create the Linked Server by using “SQLNCLI10”, but it again gave an error, as follows:

Msg 8522, Level 16, State 3, Line 1
Microsoft Distributed Transaction Coordinator (MS DTC) has stopped this transaction.


 

I scripted out the DDL of my existing Linked Server, as below:

USE [master]
GO

-- Existing LinkedServer [NorthWind2000]:
EXEC sp_addlinkedserver	@server = N'NorthWind2000', 
						@srvproduct=N'MSSQL', 
						@provider=N'SQLNCLI', 
						@provstr=N'PROVIDER=SQLOLEDB;SERVER=NorthWind'

EXEC sp_addlinkedsrvlogin   @rmtsrvname=N'NorthWind2000', 
							@useself=N'True', 
							@locallogin=NULL, 
							@rmtuser=NULL, 
							@rmtpassword=NULL
GO

 

=> WORKAROUND / FIX:

Now as a workaround to make this Linked Server work we have an option to use the ODBC Data Source which will connect to our remote server.
There are 2 approaches:

1. Either we create an ODBC Data Source (DSN) and use it in our Linked Server.

2. Or, use the Data Source (DSN) connection string directly in the Linker Server Provider.
 

–> Using appraoch #1:

Create an ODBC Data Source:
1. Open Control Panel, go to Administrative Tools, then “Data Sources (ODBC)”.
2. On “ODBC Data Source Administrator” window go to “System DSN” Tab.
3. Here click on Add to create a new DSN.
4. Choose “SQL Server” and click Finish.
5. On the new window, give a proper name for the Source DSN (like: NorthWind2000DSN), we will use this name while creating our Linked Server. Provide the Server name which is on SQL Server 2000, here “NorthWind”. Click Next.
6. Choose the Authentication Type, either Windows or SQL Server auth. Click Next.
7. Change the default database, not necessary. Click Next.
8. Click Finish. You will see a new DSN created under System DSN tab.

Now, create Linked Server and provide this DSN in the @datasrc param and provide the @provider param “MSDASQL”.

You can use the below query to create the same:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO

-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
						@srvproduct=N'MSDASQL', 
						@provider=N'MSDASQL', 
						@datasrc = N'NorthWind2000DSN', 
						@location=N'System';

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
						  @useself=N'True', 
						  @locallogin=NULL, 
						  @rmtuser=NULL, 
						  @rmtpassword=NULL
GO

 

–> Using appraoch #2:

We can also directly put the DSN connection String in the Provider String @provstr param.
Let’s check it below:

USE master
GO
-- Drop Existing LinkedServer [NorthWind2000]:
EXEC sp_dropserver @server=N'NorthWind2000', @droplogins='droplogins'
GO
-- Re-create LinkedServer [NorthWind2000] by using the ODBC connection:
EXEC sp_addlinkedserver @server = N'NorthWind2000', 
						@srvproduct=N'', 
						@provider=N'MSDASQL', 
						@provstr=N'DRIVER={SQL Server};SERVER=NorthWind;Trusted_Connection=yes;'

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'NorthWind2000', 
						  @useself=N'True', 
						  @locallogin=NULL, 
						  @rmtuser=NULL, 
						  @rmtpassword=NULL
GO

This way you can query SQL Server 2000 data from SQL Server 2012 via Linked Servers by using ODBC DSN.

This seems seamless but it is an indirect process and a workaround to query SQL Server 2000 database.
To make your queries or ETLs efficient it is advisable to upgrade to a higher version, at-least SQL Server 2005.
 

>> Check & Subscribe my [YouTube videos] on SQL Server.
 


Advertisements
  1. Max Svendsen
    July 12, 2016 at 12:17 pm

    Thanks a million, had to use the provstr

  2. Iqbal Butt
    October 29, 2015 at 1:14 am

    You rocks sir 🙂 its works like a charm. Thanks

  3. Kevin3NF
    August 20, 2015 at 6:43 pm

    Replication from 2000 through a 2014 Distributor will generate the same error:

    Invalid connection string attributeSQL Server Native Client 11.0 does not support connections to SQL Server 2000 or earlier versions

    For me, it was simple to move the publication to another distributor that was still SQL 2008 R2 🙂

  4. codecorrect
    July 1, 2015 at 12:12 am

    Thanks

  5. Guira
    March 25, 2015 at 12:48 am

    Thanks guy.
    I used like this and worked sucesses

    SELECT ls.* FROM OPENROWSET( ‘MSDASQL’ , ‘DRIVER={SQL Server};SERVER=HostName;UID=login;PWD=password;’ , ‘SELECT * FROM dbo.tabela’ ) AS ls
    go

  6. John Schmidt
    March 12, 2015 at 2:33 am

    Will this same approach work on sql server 2014?

    • March 12, 2015 at 10:18 am

      Hi @John, I’ve not tried, but can you please test and let me know. If I get time I’ll post an update for the same. Thanks!

      • John Schmidt
        March 19, 2015 at 1:53 am

        Manoj, I was able to get the linked server to work on sql server 2014 using approach #2. Thanks!

  7. Tony A.
    February 24, 2015 at 6:37 pm

    Linked a 2005 SQL Server to a SQL 2012 with Option 1 and It worked like a dream!
    Thanks for this post it is exceptionally useful.

  8. Max
    January 30, 2015 at 7:44 pm

    Thanks a lot! Is there any way to link servers FROM SQL2000 TO SQL2012?

  9. November 22, 2014 at 3:14 am

    Ugh, I *finally* got the linked server to work, thanks to you! THANK YOU!
    I used approach #1, mainly because I was too tired to compare the two approaches.
    A special warning: make sure you click on the SYSTEM DSN tab, and not the USER DSN tab…sigh…

  10. Steve H
    November 20, 2014 at 4:09 am

    Turns out if you have a field defined as TIMESTAMP, it cannot be interpreted by SQL 2012

  11. tverney
    November 12, 2014 at 10:48 pm

    May have to run the following on the source server:
    grant execute on xp_prop_oledb_provider to public

  12. Steve H
    November 7, 2014 at 10:23 pm

    I used your technique here and now we get this error when trying to test the process…

    Description: Executing the query “EXEC [dbo].[spDentzonePdrPremGetEDWDealerInfo3] ?,…” failed with the following error: “The OLE DB provider “MSDASQL” for linked server “LS_SCS” supplied inconsistent metadata for a column. The column “dtrecord_maint” (compile-time ordinal 14) of object “”scs_auto”.”dbo”.”scs_dealers”” was reported to have a “DBCOLUMNFLAGS_ISROWVER” of 0 at compile time and 512 at run time.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

  13. pianomansteve
    September 3, 2014 at 8:36 pm

    Is it possible to set up a linked server from SQL2000 to SQL2008? Upgrading my SQL2000 box is not currently an option so I am trying to set up a linked server to the SQL 2008 box and while it creates fine it always returns an error when I try to run a SQL statement against it. I get an error:

    exec sp_addlinkedserver ‘SQLSRVCATI\MULTIMODE’
    exec sp_addlinkedsrvlogin ‘SQLSRVCATI\MULTIMODE’, ‘false’, null, ‘sa’, ‘password’

    Server: Msg 14, Level 16, State 1, Line 1
    Invalid connection.

    If I try using your code above I get pretty much the same error:

    EXEC master.dbo.sp_addlinkedserver @server = N’SQLSRVCATI\MULTIMODE’, @srvproduct=N”, @provider=N’MSDASQL’, @provstr=N’DRIVER={SQL Server};SERVER=SQLSRVCATI\MULTIMODE;Trusted_Connection=yes;’
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’SQLSRVCATI\MULTIMODE’,@useself=N’false’,@locallogin=NULL,@rmtuser=N’sa’,@rmtpassword=N’password’

    Server: Msg 7399, Level 16, State 1, Line 1
    OLE DB provider ‘MSDASQL’ reported an error.
    [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (ParseConnectParams()).]
    [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.]
    OLE DB error trace [OLE/DB Provider ‘MSDASQL’ IDBInitialize::Initialize returned 0x80004005: ].

    Any thoughts or hints you can offer?

  14. Kumar
    May 14, 2014 at 1:49 am

    Thanks! very useful info.

  15. varsha
    April 29, 2014 at 9:06 pm

    Thanks a lot.. I solved the similar issue with approach #2.. and it worked

  16. Bhanu
    March 28, 2014 at 5:26 am

    Thanks Manoj…I used the approach #1 and it worked..

  17. Christopher Enengl
    December 4, 2013 at 2:12 pm

    I tried approach #2, the creation of the linked server was no problem, but when running a query i always get the error message:

    OLE DB provider “MSDASQL” for linked server “rmt-server” returned message “[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ‘(null)’. Reason: Not associated with a trusted SQL Server connection.”.
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “MSDASQL” for linked server “rmt-server” reported an error. Authentication failed.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “rmt-server”.

    My user exists on the rmt-server, i can connect via SSMS without any problems.
    Security Context of the Linked Server is as you have in your example “Be mad using the login’s current security context”, which is my Standard Windows Usercontext

    Any Ideas?

    Thanks for your help!

  18. dustin
    June 21, 2013 at 2:15 am

    thanks a million, i used the second without error

  1. October 17, 2016 at 3:04 am
  2. January 20, 2014 at 3:32 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: