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.
thanks a million, i used the second without error
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!
MSDN: Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server: http://social.technet.microsoft.com/Forums/office/en-US/7f6a5c58-e908-4f5e-aee0-41a72fe5c8b4/cannot-initialize-the-data-source-object-of-ole-db-provider-msdasql-for-linked-server-?forum=sqldatabaseengine
Linked Server pra que serve e quando utilizar?: http://imasters.com.br/banco-de-dados/sql-server/linked-server-pra-que-serve-e-quando-utilizar/
Thanks Manoj…I used the approach #1 and it worked..
Thanks a lot.. I solved the similar issue with approach #2.. and it worked
Thanks! very useful info.
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?
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.
May have to run the following on the source server:
grant execute on xp_prop_oledb_provider to public
Turns out if you have a field defined as TIMESTAMP, it cannot be interpreted by SQL 2012
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…
Thanks a lot! Is there any way to link servers FROM SQL2000 TO SQL2012?
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.
Will this same approach work on sql server 2014?
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!
Manoj, I was able to get the linked server to work on sql server 2014 using approach #2. Thanks!
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
Thanks
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 🙂
You rocks sir 🙂 its works like a charm. Thanks
Thanks a million, had to use the provstr
Thanks for the great workaround would this work for SQL 2000 and 2017?
Hi Pooja, I’ve not checked, but it should work !
Thanks top working solution
The first approach worked for me. Thank you so much. God gless
Hi, I’m trying to link from a SQL 2016 server to SQL 2000. The ODBC Connection on creates and tests successfully. However, the linked server test connection gives the error “Cannot initialize the data source object of OLE DB provider “MDASQL” for linked server “OPSPUB3”. OLE DB provider “MDASQL” for linked server “OPSPUB3” returned message “[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified”. (Microsoft SQL Server, Error: 7303).
Update: I was never able to query data on the SQL 2000 server from the SQL 2016 server using this (or any) method. I also have a SQL 2012 server running, and connecting to the SQL 2000 server using this method worked from there.