The Microsoft Access database engine cannot open or write to the file \\fileserver\db\access.mdb

Question

Hi,
I have Windows Server 2012 with SQL 2012 Standard SP1. I am using linked server, and Access Database Engine 2010 Redistributable to access my database file made in Microsoft Access (.mdb) from network file server.

EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'MyLinkedServer', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\\myfileserver.mydomain.com\files\mydatabase.mdb'

My SQL service is running with domain service account MYDOMAIN\SQL1$ , i have added Full control for file share and NTFS permission on my file server folder (C:\Files).

When I open (as domain admin with UAC elevated permissions) on my DB server SQL Management studio, I can browse tables and everything works.

The problem is, if I open SQL management studio (as domain admin with UAC elevated permissions) on my File server or any other computer, when trying to browse my linked server i got error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Synesis_3PRO2013". (Microsoft SQL Server, Error: 7303)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.00.3000&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476
------------------------------

When I try to place simlpe Select SQL query I got error:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MyLinkedServer" returned message "The Microsoft Access database engine cannot open or write to the file '\\myfileserver.mydomain.com\files\mydatabase.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".

I do not have any other program using my access database, and user has full control. I am trying to use in security mode - "For a login not defined in the list above, connections will be made without using a security context", i have also tried all four options. I am confused becouse it works from SQL server but from any SQL client domain member computer/server it does not work.
I have same problem in another environment where I have Windows Server 2008 R2 and SQL 2008 R2 SP2.
Please help.

---

-- Hrvoje Kusulja

Answer 1

The only account important for the NTFS permissions is the account of your SQL Server Windows service. The only additional problem I can think of is - but untested in this configuration - that the ACE engine here also checks the Trusted Locations, which Access normally does. Take a look at this registry tweaks.

Answer 2

NTFS must be fine since it works from same server using same accounts.
As I understand, adding my access file to Access trusted location could be a problem. I have tried now to add my access database file location to trusted locations for user which is my SQL service user (Windows Service - AD managed service account MYDOMAIN\SQL1$) and my test user which I use to connect to sql server as a client from sql management studio. (Account is Domain Admins and have full permissions on SQL server also)

I have added this .reg:

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security]

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Documents]
"LastPurgeTime"=dword:01592874
"DisablePromptOpenNetworkTrustedDocuments"=dword:00000000

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations]
"AllowNetworkLocations"=dword:00000001

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location0]
"Description"="My file server"
"AllowSubFolders"=dword:00000001
"Path"="\\\\myfileserver.mydomain.com\\files\\"

I have done this for SQL service account user and my personal test account as I said. I have tried to logoff and restart sql service and all servers also.

The same problem still persists.

Anyway, thank you for giving me a hint.

Answer 3

Any new proposition or solution for my problem ? I have still same error.

---

-- Hrvoje Kusulja