3

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.

  • 1
    One possible cause might be insufficient permissions on the TEMP folder for the account under which the SQL Server service is running. See my Stack Overflow answer here for more details. – Gord Thompson Nov 24 '14 at 13:43
  • I tried to add permissions to the TEMP folder without success. If i add the user to the Administrator group, it work (only for an AD user, not SQL Server user) – Alexandre Jobin Nov 25 '14 at 13:58
  • How about the permissions on the folder in which the .mdb file resides? Does the troublesome user have Change permissions on that folder (and the .mdb file itself) too? – Gord Thompson Nov 25 '14 at 14:02
  • Yes i've also tried to add permissions on the folder where the .mdb resides. – Alexandre Jobin Nov 25 '14 at 18:41
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.