none
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Access denied.

    Question

  • Hi,

    I have created a linked server connection from a SQL 2005 instance to an Access 2010 DB file located in a network filesystem. This linked server connection works fine from my DEVELOPMENT environment and I can query it's tables from SQL Management studio.

    However when I script out the linked server connection and create it in our PROD environment I receive the following error:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Access denied.

    Msg 7301, Level 16, State 2, Line 1

    Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "".

    Does anyone know how I can resolve this?

    The interesting this is that if I login to my PROD SQL instance using either SQL authentication or the windows service account authentication, the linked server query works.

    However when I login with my normal domain account I get the error. I've temporarily granted the 'everyone' group full control to the Temp folder of the service account but this doesn't have any effect.

    Thanks very much for any advice.

    Phil

    Thursday, December 19, 2013 12:07 PM

Answers

  • Hi Philip,

    MSDAINITIALIZE is a COM class that is provided by OLE DB. This class can parse OLE DB connection strings and load/initialize the provider based on property values in the connection string.

    MSDAINITILIAZE is initiated by users connected to SQL Server. If windows authentication is used to connect to SQL Server, then the provider is initialized under the logged in user account. If the logged in user is a SQL login, then provider is initialized under SQL Server service account. Based on the type of login used, permissions on MSDAINITIALIZE have to be provided accordingly.

    When these permissions are not set for the logged in users, we get Access Denied errors as you posted above. To deail with this issue, please refer to the following article below:
    Permissions needed to set up linked server with out-of-process provider: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, December 27, 2013 5:02 AM
    Moderator

All replies

  • check whether you have office 2010 installed on your dev environment and not on production, it is not able to obtain the provider


    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    INSQLSERVER.COM Mohammad Nizamuddin

    Wednesday, December 25, 2013 12:23 PM
  • Hi Philip,

    MSDAINITIALIZE is a COM class that is provided by OLE DB. This class can parse OLE DB connection strings and load/initialize the provider based on property values in the connection string.

    MSDAINITILIAZE is initiated by users connected to SQL Server. If windows authentication is used to connect to SQL Server, then the provider is initialized under the logged in user account. If the logged in user is a SQL login, then provider is initialized under SQL Server service account. Based on the type of login used, permissions on MSDAINITIALIZE have to be provided accordingly.

    When these permissions are not set for the logged in users, we get Access Denied errors as you posted above. To deail with this issue, please refer to the following article below:
    Permissions needed to set up linked server with out-of-process provider: http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, December 27, 2013 5:02 AM
    Moderator
  • Hi Elvis,

    Many thanks for your advice. I followed the steps in that article, rebooted my PROD server and now the linked server connection works using my own domain account!

    I'm not sure exactly which permission it was, but after changing something in Dcomcnfg, this seems to have fixed the problem.

    Thanks again.

    Phil

    Monday, January 06, 2014 12:28 PM