'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "xxxx"

In our SQL Server 2017 we have a linked server to a Microsoft Access file/database.

There is a bunch of users who all log into SQL Server using Windows authentication, and this linked server is working fine for all but 1 user, this 1 user gets the error message in the title of this post.

This user can open the Access database directly, so they have permissions for the file itself. I have compared this user to one that doesn't have the problem and their SQL server account is set up in the same way in terms of server roles and database roles they are members of.

I thought it might be something to do with double hop authentication, so for some reason SQL Server might not be relaying the user's authentication when it accesses the Access database.

Our IT department have said that this user is set up the same as a different user who doesn't have the problem and they don't know what to do about it.

sql-serverfile-permissionswindows-server-2012linked-server


Solution 1:[1]

I hope this helps someone else, but I solved this by adding read & write permissions to directory:

C:\Users\SS1\AppData\Local\Temp (where SS1 is the user account that SQL Server runs as)

for the user who was having problems and that fixed it.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Paul

Related Questions