Sql-server – How to connect to a password protected Access 2010 database from SQL Server 2014 x64

I have the following situation:

• An Access 2010 database (.accdb) on a shared folder of a remote server.
• A domain account that can access the shared folder and files.
• A SQL Server 2014 x64 running on a different server on a whole different network that isn't part of the domain the Access file lives in.
• The user accessing SQL Server 2014 is a SQL Server user and not a Windows user.
• The SQL Server 2014 box can connect to the shared folder's server through a VPN

What I want to achieve:

• I want to add the Access 2010 database as a linked server in my SQL Server 2014

I've installed the Microsoft.OLEDB.12.0 driver for x64 on the SQL Server box and they work correctly for local Access 2010 files.

I added the Access database as a linked server with the following commands:

EXEC master.dbo.sp_addlinkedserver 
@server = N'ACCESSDB',
@srvproduct=N'ACCESSDB', 
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'\\192.168.100.64\accessdb$\MyAccessDb.accdb',
@provstr=N';PWD=THEPASSWORD;Mode=Read'

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ACCESSDB',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'theremotedomain\theuser',
@rmtpassword='theuserpassword'

Those commands work fine and don'r return any error.

The moment I try to test the connection from SQL Server Management Studio, I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESSDB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESSDB".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ACCESSDB" returned message "Not a valid account name or password.". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.3720&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

What's wrong with this? Is there any way to debug what's going on between SQL Server and the linked server?

Update

If I add the Access password information to the linked server logins like this:

EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'WINMARK',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'admin',
@rmtpassword='THEPASSWORD'

the previous error goes away and a new one pops up:

TITLE: Microsoft SQL Server Management Studio
------------------------------

The test connection to the linked server failed.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WINMARK" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WINMARK".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "WINMARK" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.". (Microsoft SQL Server, Error: 7399)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=10.50.3720&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Apparently I am the only user connected to that database so I'd say that the file isn't open in exclusive mode by someone else, neither by me as I am accessing it in read-only mode through the Mode=Read parameter in the provider string.

What's that workgroup information file that it is referring to? Is that the actual problem or could it be a more broader error message and the problem might lie somewhere else?