Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "".

Posted on 2013-12-12
Medium Priority
Last Modified: 2013-12-21
We have just moved our primary SQL server to a new server (SQL 2012  Standard). I did this via log shipping and then failing over to the new server so it became the primary as due to the size of the database and time restiction of when the move could be made I chose log shipping to limit any down time.
Everything went to plan with one exception. One of the linked servers we use from the server to a SQL 2000 server using a ODBC connection is not working when being called upon in stored procedures. You can see the catalogs and run queries on it but we are getting the below error's. I have googled for 4 days and tried various things but to no avail. Part of me thinks this is due to double hop?
The DBA before me used synonyms and did not document any of the work he did when he first set up the original server so this has been a nightmare. I have ensured that all logins etc... have the same permissions as they did on the old server but I still cannot get this to work.
When we use a try catch in the stored procedures being called it brings back "Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "". Error Number 7303.
But when they are using the linked server from their end (they use a linked server on the SQL 2000 server, it is then executing part of the stored procedures on the SQL 2012 server but when it goes back out to reference the SQL 2000 linked server it brings the below error).
"Mail (Id: 1078) queued. OLE DB provider "MSDASQL" for linked server "Server" returned message "[Microsoft][ODBC SQL Server Driver][SQL Server] cannot open user default database. Login failed.". OLE DB provider "MSDASQL" for linked server "Server" returned message ""[Microsoft][ODBC SQL Server Driver][SQL Server] Cannot open user default databse. Login failed.". String data, right truncation, string data, right truncation"
Any help would be greatly appreciated as everything I have found so far has not helped.
Question by:Box-It
  • 4
LVL 20

Expert Comment

by:Marten Rune
ID: 39720651
You need the native client for SQL2008 (it can speak SQL2000'ish).

look at: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/2e02c603-e28d-49eb-b073-548c59732b5d/linked-server-from-sql2012-to-sql2000?forum=sqlsetupandupgrade

Download the native client and use the sp_addlinked server script. You'll be fine.

Regards Marten
PS SQL 2000 hasn't been supported for nearly 9 years, time to do something about that application maybe.

Author Comment

ID: 39721192
Hi Marten,
Thank you for your response.

I have the native client for 2008 installed. The linked server works without issue. It is only when it is used in a stored procedure that goes from SQL 2000 to 2012 via a linked server and then back to 2000 via another linked server that the error occurs.

The linked server works without issue otherwise.

I have the linked server pointing to a DSN. I have however experimented by setting the linked server to use SQLNCLI10 and point it to the server. This however means that I need to use distributed transactions. As the linked server was working without this previously on the old server I am reluctant to change it.

We are looking to move away from 2000 but we cannot quickly so I need to resolve the current issue until we are ready to move to a new server and away from SQL 2000 finally.


Author Comment

ID: 39721451
Have you heard of any issues like this in regard to the version of the driver? I have now seen that my colleague before me used the 32 bit SQLNCLI10 client and not the 64 bit version.

I have the 64 bit SQLNCLI10 client installed.

Accepted Solution

Box-It earned 0 total points
ID: 39723449

This is not fully resolved but I have put in a work around. I have been unable to negate this error from occurring.

I have bypassed any possibility of double hop or permissions (although the permissions all look identical) by using SQL authentication.

I have set up a SQL login on both servers and used this in the linked server. I was reluctant to do this as I wanted to mirror the old server but I have done this as an interim to get it working. I will investigate further and resolve the issue and add a post once fully resolved rather than using a workaround which is far from ideal.
Many thanks for your comments



Author Closing Comment

ID: 39733429
I have had next to no responses and have put a work around in place which has resolved the issue although not ideally as I would have liked to have kept the new server exactly like the old one but for now all is working. I have not seen anyone mention what I have done to bypass the error so there is no one else I can give the points to.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question