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
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!
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.
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?
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.