How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?

Question

I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete statements on the Access database without any problem but when i use a non-admin user, i can only do Select queries. The Insert, Update and Delete queries doesn't work and i get the following error message:

Error message when i use a SQL Server login:

Cannot execute the query "SELECT * FROM inv_lign" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "AccessFileTest". The provider could not support an interface required for the UPDATE/DELETE/INSERT statements.The provider indicates that conflicts occurred with other properties or requirements.

Here's the query:

DELETE FROM
OPENQUERY(AccessFileTest,
    'SELECT * FROM inv_lign')

I have also tried the 4 parts query without success:

DELETE AccessFileTest...inv_lign;

Here's how the Microsoft.ACE.OLEDB.12.0 provider is configured:

• Dynamic parameter = true
• Allow inprocess = true
• Added the key DisallowAdHocAccess = 0 in the registry

Here's how the Linked Server AccessFileTest is configured:

• In the Security tab, i have selected "Be made using this security context"
• The remote login is "admin" without any password

So how can i do Insert/Update/Delete statements on an Access 2003 database using the Microsoft.ACE.OLEDB.12.0 provider?

What i have tried so far:

• I've been able to make it work ONLY if i use an AD user that is not sysadmin of SQL Server but who is Administrator of my server. If the user is not admin of the server, i get the error message Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".. I then tried to just add this user to have permission on the TEMP folder and on the Access file without success. So i don't know what specials rights i have to give to get this working with an AD User.

• If i use a SQL Server login, i'm stock on the error message as stated before.

Answer 1

The problem came from an insufficient privileges on the server where the Access file is stored. Here's how you should configure your server depending of the login type that you are using:

If you are using SQL Server login:

• Give to the SQL Server Database Engine service account permissions to read and write (depending if you want to make CRUD on that file) on the folder where the Access file reside.

If you are using Windows authentication login:

• Give to this user login permissions to read and write :
  • on the folder where the Access file reside.
  • on the temp folder of the SQL Server Database Engine user account. For exemple, if the SQL Server Database Engine service account is domain\sqlEngSvc, the read/write permissions should be set to the C:\Users\sqlEngSvc\AppData\Local\Temp folder.