1

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?

1

It looks like that at the time I'm writing this there is no way to pass Windows security credentials when using a SQL Server user. The SQL Server user will always use the credentials of the Windows account the service is running with.

In most cases it's not even an actual user as it's just a service account that can't be bound to a domain controller to grant additional permissions.

My solution is to just move those Access files to the same server running SQL Server and open them locally.

1

I solved this problem with the following SQL script. My Access is 2013 MS Access 64-bit and SQl Server is 2014.

Create linked server with access no password access

EXEC master.dbo.sp_addlinkedserver 
@server = N'LINKEDSVR',
@srvproduct=N'Smarthr', 
@provider=N'Microsoft.ACE.OLEDB.12.0',
@datasrc=N'path of db'

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

Create linked server with access password

exec sp_addlinkedserver 
    @server = 'TestLinkServer', 
    @provider = 'Microsoft.ACE.OLEDB.12.0', 
    @srvproduct = 'Access',
    @datasrc = 'path of db', 
    @provstr = ';PWD=yourpassword'

exec sp_addlinkedsrvlogin 
    @rmtsrvname = 'TestLinkServer',
    @useself = 'FALSE',
    @locallogin = null, 
    @rmtuser = 'Admin', 
    @rmtpassword = null

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.