Ole Db Driver For Sql Server Does Not Support Connections To Sql Server 2000 Or Earlier Versions
Charise Farag
Use the "Microsoft OLE DB Provider for SQL Server" (SQLOLEDB) OLE DB provider, not SQL Server Native Client 11.0" (SQLNCLI11) OLE DB provider to access SQL Server 2000. Although SQLOLEDB is deprecated, you'll need to use it for SQL Server 2000, which is unsupported anyway.
Here is how I fixed it today using SQL Server 2014 on Windows 8.1 Pro. I was trying to add a Linked Server for a remote SQL Server 2000 instance. While using the SQL Server Native Client 11.0 I received the same error as the OP.
Then I tried creating an ODBC data source on my machine, using "ODBC Driver 11 for SQL Server" and got the following error. "ODBC Driver 11 for SQL Server does not support connections to SQL Server 2000 or earlier versions"
At last I created a new Data Source using "SQL Server" driver in the New Data Source wizard, NOT "SQL Server Native Client 11.0" driver. The Data Source was successfully created. And then I used this newly created data source as "Product name" and "Data source" both, in "New Linked Server" dialog box in the Management Studio, and selected "Microsoft OLE DB Provider for ODBC Drivers" as the provider. In the "Linked server" field, I put the IP address of the remote SQL Server. It successfully created a new Linked Server in my SQL Server 2014 Management Studio. I hope this answer will help anyone who lands here in search of an answer for this issue.
I just got my first chance to install SQL Server 2012 on a test machine. I also installed all of the management and Visual Studios components. I created a copy of one of my SSIS 2008 packages and converted it to 2012. The conversion worked jsut fine, but I'm now getting an error stating that connections to SQL Server 2000 and earlier versions are no longer supported.
Apparently, the SQL Server Native Client 11.0 (the one that ships with SQL Server 2012) does not support SQL Server 2000. All you have to do is change the data source to use a .Net Provider (SqlClient Data Provider) instead of the Native OLE DB provider. The .Net Provider probably doesn't perform quite as well as the Native OLE DB but it was definitely a lot less work that having to build a bunch of new ETLs to stage the data and a lot less risky too.
SQLState: 08001 Error Code: 22 Message: [Microsoft][ODBC Driver 13 for SQL Server]ODBC Driver 13 for SQL Server does not support connections to SQL Server 2000 or earlier versions. SQLState: 08001 Error Code: 22 Message: [Microsoft][ODBC Driver 13 for SQL Server]Client unable to establish connection SQLState: 08001 Error Code: 22 Message: [Microsoft][ODBC Driver 13 for SQL Server]ODBC Driver 13 for SQL Server does not support connections to SQL Server 2000 or earlier versions. SQLState: 08001 Error Code: 22 Message: [Microsoft][ODBC Driver 13 for SQL Server]Client unable to establish connection
Please do check if you use the 32 or 64 bit version. If you use the 32 bit version do check that all dependancies are working. The program is here:
in case of count (on windows) grab a program called depends and run it.
It has helped me out many times when dependancies were not complete (on windows!).
On linux you can use or ELF Library Viewer
I can see no documentation to prove this, but I suspect you are seeing the results of deprecation of 2000. Most 2000 database server management is not possible in 2012 as it is more than 2 versions back, so you may not be able to link a 2000 to a 2012 in this fashion. But again, that's just a guess and not based upon documentation or an actual test.
Been a while since I had to deal with distributed transactions over linked servers. I found the problem to be in the configuration of MSDTC. I wish I could provide more information, but all I can suggest is Googling (or Binging) MSDTC configuration and see what you get.
The SQL Server Native Client (SQLNCLI11) that ships with SQL Server 2012 does not support connections to SQL Server 2000 an earlier. I found this out during testing of our upgrade to SQL 2012. We upgraded our 2008 R2 servers in-place and it appears to have left the SQLNCLI10 provider in place but if you did a fresh install of SQL Server 2012 on a new server you may not have this provider. Does your 2012 server have the SQLOLEDB provider listed under Linked Server Providers in SSMS? If so, you could try using that.
I created a new ODBC data source using SQLNCLI10 then created a linked server to SQL 2000 using this new ODBC data source on a server with SQL 2012 and although it displays the DBs and tables under the Linked Server, when I use it to query in TSQL, I get this error:
Invalid use of schema or catalog for OLE DB provider "MSDASQL" for linked server "MYSERVERNAME". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
I am not sure why I am getting this error when the linked server with SQL 2008 is doing fine and there is not MS DTC error also linked server using the same SQL 2000 server with SQL 2008 server is working fine as well so I don't think there is issue with configuring MSDTC.
Thanks a lot for the response. Yes I am picking SQLNCLI10 and not SQLNCLI11. I am able to see the tables and views once the connection is established, but I am not able to do the SELECT as it gives the MC DTC error.
The same SQL 2000 and SQL 2012 are working fine with SQL 2008 when we create linked server, so I dont think there is issue with MC DTC configuration... What you think might be the issue? Do you suggest that I should install SQl2008 and then upgrade to SQL2012 instead of installing a new copy?
We had similar issues(same error) a few jobs ago when upgrading the OS and SQL same day. Maybe you did, or didnt also upgrade the OS, but our problem was the DTC co-ordinator. We dug and dug blaming sql until finally found an error with enough info to reveal MSDTC was the problem.
We added both machine names and tweaked DTC on both servers, doing nothing to sql at all, if it's enabled already maybe you need other changes. Each jump in versions has impending consequences and I've learned never discount cause something worked version to version especially in this case, being way apart.
Just curious since there is no upgrade path from 2000 to 2012 and 2000 compat mode is not even supported in 2012. That in mind makes me think that linked servers from 2012 down to 2000 are not supported either.
OLE DB provider "MSDASQL" for linked server "TEST2" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)
Any idea why am am I getting this error... I created a new ODBC connection "DSNTEST1" which connects with no issue... I followed your suggestion.... I used this driver to create the ODBC "SQLSRV32.DLL (11/20/2010)". Also, I forgot to mention that SQL 2012 is installed in Virtual Machine...
Several known vulnerabilities have been reported against Secure Sockets Layer (SSL) and earlier versions of Transport Layer Security (TLS). We recommend that you upgrade to TLS 1.2 for secure communication.
No known vulnerabilities have been reported for the Microsoft TDS implementation. This is the communication protocol that's used between SQL Server clients and the SQL Server database engine. The Microsoft Schannel implementation of TLS 1.0 (regarding the known vulnerabilities that have been reported to Microsoft as of the publication date of this article) is summarized in the Schannel implementation of TLS 1.0 in Windows security status update: November 24, 2015.
Use the following table to determine whether your current version of SQL Server already supports TLS 1.2 or whether you have to download an update to enable TLS 1.2 support. Use the download links in the table to obtain the server updates that are applicable to your environment.
You have to install the following .NET hotfix rollups to enable SQL Server features like Database Mail and certain SSIS components that use .NET endpoints that require TLS 1.2 support like the Web Service task to use TLS 1.2.
Yes. SQL Server 2016, SQL Server 2017 on Windows, and SQL Server 2019 on Windows versions ship with TLS 1.0 to TLS 1.2 support. You have to disable TLS 1.0 and 1.1 if you want to use only TLS 1.2 for client-server communication.
SQL Server 2019 has the same level of support as SQL Server 2016 and SQL Server 2017, and SQL Server 2019 supports older versions of TLS. SQL Server 2019 RTM is shipped with TLS 1.2 support, and no other update or fix is required to enable TLS 1.2 support.
No known vulnerabilities have been reported for the Microsoft TDS implementation. Because several standards-enforcement organizations are mandating the use of TLS 1.2 for encrypted communication channels, Microsoft is releasing support for TLS 1.2 for the widespread SQL Server installation base.
Yes. SQL Server encrypts the username and password during login even if a secure communication channel isn't used. This update is required for all SQL Server instances that don't use secure communications and that have all other protocols except TLS 1.2 disabled on the server.