Showing posts with label systemwin. Show all posts
Showing posts with label systemwin. Show all posts

Sunday, March 11, 2012

Cannot create an instance of OLE DB provider "IBMDADB2" for linked server

System:Win 2003, SQL Server 2005, Using an AD win account that is not a member of the Admin group on the server.

Error message from Management Studio query window:

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB provider "IBMDADB2" for linked server "Sname".

Event messages associated with this error:

App Event ID: 19036

The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components. If the problem persists, contact product support for the OLEDB provider.

Sys Event ID: 10016

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {2206CDB0-19C1-11D1-89E0-00C04FD7A829}

to the user domain\user SID (S-1-5-21-126051702-1034962659-2130403006-7826). This security permission can be modified using the Component Services administrative tool.

I’m getting this error message when trying to run an openquery statement through a linked server to DB2. (SELECT * FROM OPENQUERY(Sname, 'SELECT * FROM tablename’))

The linked server has a remote login and password that it uses to connect to DB2.

I found this from another post on how to fix this error:

Expand Component Services - Computers - My Computer - DCOM Config Select MSDAINITIALIZE Right Click properties then security

Under Security - Launch Permission: enable Local Launch and Local Activation for your SQL Service account

Under Security - Access permissions: Allow System: Local Access and Remote Access.

After completing these steps I still get the error message but the events are no longer generated.I’ve also tried different variations of these steps.The only way I can get this to work is to either make the AD user a member of the Admin group on the server or by putting in a user account that has admin rights to the server in the MSDAINITIALIZE properties – Identity Tab – Run this App as this User.

Can someone please tell me the steps that I’m missing?

Additional info:

If I set the providers allowinprocess to 1 with this,

EXEC master.dbo.sp_MSset_oledb_prop N'IBMDADB2', N'AllowInProcess', 1

It responds with this error:

OLE DB provider "IBMDADB2" for linked server "LName" returned message " SQL10007N Message "0" could not be retrieved. Reason code: "2".

".

Msg 7303, Level 16, State 1, Line 1

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