Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB Provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Msg 7302, Level 16, State 1, Line 1 Cannot create an instance of OLE DB Provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Msg 7302, Level 16, State 1, Line 1

Cannot create an instance of OLE DB Provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)”.

Application error Sounded like:

The OLE DB initialization service failed to load. Reinstall Microsoft Data Access Components.

If the problem persists, contact product support for the OLEDB provider.

Problem:

An open rowset operation from a login works fine on this linked server but fails for anybody else.

Cause:

The failed login did not have appropriate permissions to remotely activate the COM Class.

Solution:

1) Grant permissions to the COM Class provided by OLE DB.

Start —>Control Panel —> Administrative Tools —> component Services
Expand Component Services —> expand Computers —> Expand My computer —> Expand DCOM config
Locate MSDAINITIALIZE and right click
Open Properties —> Security
In the “Launch and activation Permissions” click “customize” and “edit”
Add the login/User which failed to do the openrowset and in the permissions page tick ‘Local Launch’,’remote Launch’,’Local Activation’,’Remote Activation’ Click OK to close the permission page
Click ‘Apply’ and ‘OK’ on the MSDAINITIALIZE properties to close the window.

2) Grant Read Write access to the Login/User to the ‘temp’ directory on the server.

This resolved my issue without recycling sql services, give a try and if this doesn’t work restarting the sql service will definately help.

This entry was posted in SQL Server, Troubleshooting and tagged , , . Bookmark the permalink.

Leave a Reply