Problem with UPDATE over Linked server using Microsoft OLE DB Provider for ODBC drivers for MS Access

Question

Hi,

I'm currently using SQL SERVER 2019 with ODBC driver for MS Access (installed via AccessDatabaseEngine2010 64bit) for

linked server.

On 3 different machines where SQL SERVER 2019 Express is installed (Windows 10) select function works:

SELECT * FROM OPENQUERY(MYODBC,'SELECT qty from stock')

and UPDATE

UPDATE OPENQUERY(MYODBC,'SELECT qty FROM stock') SET qty = 2

but on the one machine (Windows Server 2016) SELECT works but UPDATE gives us the error:

OLE DB provider "MSDASQL" for linked server "ildodbc" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot update. Database or object is read-only.".
Msg 7343, Level 16, State 2, Line 183
The OLE DB provider "MSDASQL" for linked server "ildodbc" could not INSERT INTO table "[MSDASQL]".

It's the same database and on all 4 machins we are logged with sa account to SQL Server, on windows with admin account.

Any idea what can be the cause for this.

Thanks

Answer 1

We tried with ACE OLE DB and it was the same thing, on our machines it worked, on theirs not, tbh it worked if we were logged

locally in SSMS on the server, but the soon as we tried to call a stored procedure from another PC on the network, the procedure returned
permission denied.

This sounds like a double-hop problem. Then again, E:\LegacyDB certainly sounds like a local disk, so I cannot really see how would happen here.

Also, double-hop problem appears when you use Windows authentication. SQL Server then tries to impersonate the actual Windows user, but this does not work if the login was by NTLM and not Kerberos, or Kerberos is not set up properly.

Answer 2

How come you are using MSDASQL + ODBC driver? Wouldn't it be more natural to use the ACE OLE DB provider? That would be one less moving part, at least. Not that I think it would resolve the error.

My guess would be with the permissions for the service account for SQL Server, either within Access, or the file permissions on the Access file. I think so, because access to the database would be by that account. At least when it comes to the file. For permissions inside the database it is likely to be different if you have set up login-mapping.

Note: I have no knowledge about Access as such.

Answer 3

You cannot perform an update using OPENQUERY that way. What you can do is reference the linked server directly:

UPDATE s
SET qty = 2
FROM {linkedserver}.schema.stock s

Of course, as written this will update all rows in that table since you do not have a where clause. Another method would be EXECUTE AT - and provide the update statement to be executed at the remote system.

Answer 4

To continue:

We tried with ACE OLE DB and it was the same thing, on our machines it worked, on theirs not, tbh it worked if we were logged
locally in SSMS on the server, but the soon as we tried to call a stored procedure from another PC on the network, the procedure returned
permission denied. With ODBC driver for MS Access and DSN at least we could read the data but, can't update or delete or insert.

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

EXEC sp_addlinkedserver
@Testta = 'AccLink',
@srvproduct = 'Access',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'e:\LegacyDb\test.accdb';
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'AccLink',
@useself = 'false',
@rmtuser = 'Admin',
@rmtpassword = '';

--- show tables
EXEC sp_tables_ex N'AccLink';

-- get Article table

SELECT *
INTO Article
FROM AccLink...Article;

Answer 5

Small update,

nudged by ErlandSommarskog answer I googled a bit about folder permissions with SQL SERVER and find this post by Pinal Dave

https://blog.sqlauthority.com/2018/06/24/how-to-find-service-account-for-sql-server-and-sql-server-agent-interview-question-of-the-week-179/

with that I found exactly the name of the service account and then by following instructions from this post also by Pinal Dave

https://blog.sqlauthority.com/2018/08/25/sql-server-fix-create-file-encountered-operating-system-error-5-access-is-denied/

a gave permission to that E:\LegacyDb folder and voila update worked.

Now I will also test with ACE OLE DB to see if this fixed the problem we had with it and get back to you.