none
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "<My Linked Server>".

    Question

  • We are allowing users to pull data from a DB2 server with a SQL Server 2005 stored procedure using an OPENQUERY statement. Something like this:

    Select * From OPENQUERY(<My Linked Server>,'SELECT * FROM DSNW.MWI_UND_REQ_APS')

    The users are granted permission to access the stored procedure using windows authetication via a windows network group.

    Recently we discovered that one user in this network group is unable to successfully run this stored procedure. He receives the following error:

    7303 - Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "<My Linked Server>".

    I have no idea why this user would get this error. There is nothing that I know of that would make him different than any other user. The problem is definitely in his ability to use the linked server. I have logged in under his ID on SQL Management Studio and tried to run any number of OPENQUERY statements against this linked server while logged in as this user, but have had no success getting results back from any of them. When I log back in as myself or any other user the queries all work fine.

    I should add that the user has no problem accessing data from regular SQL tables, only from the DB2 server via this linked server.

    I am at a loss as to how to give this user the access that he needs. I even tried creating a new linked server by scripting the existing linked server and changing the name. The user is still unable to access the data even using the new linked server. I don't know what else to check!

    Can you offer any suggestions?

     

    Friday, October 12, 2012 7:57 PM

All replies

  • Hi rsconnolly,

    This problem could be Login and password sent to the provider is invalid.

    Please verify that there is a valid login and password configured for the linked server and the current SQL Server login through sp_addlinkedsrvlogin. Try to ping the SQL Server machine from the machine where you are executing the linked server query to see if it successfully connected.

    Please refer to:

    http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/be2fa878-64a8-4555-b418-f05ea9b64f42/

    http://msdn.microsoft.com/en-us/library/aa226395(v=sql.80).aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 15, 2012 8:31 AM
    Moderator
  • Thank you for your reply - The login and password configured for linked server are valid as evidenced by the fact that everyone else in the network group is able to successfully use the linked server to pull information. There is just one user who is unable to do this - even though he is a part of the same networkgroup that has been granted permissions to the SQL Server database. I have been unable to think of a way to troubleshoot this one user and find out where his connection is failing (where everyone else is succeeding) and why. Any thoughts on how to do this?

    Robert Connolly

    Monday, October 15, 2012 12:56 PM