Linked Server to MS Access Database

Question

Linked Server to MS Access Database

stephenmbell

SSC Eights!

Points: 926
More actions
August 5, 2021 at 9:17 pm

#3917285
Hello all -
I am hoping someone here can help shed some light on what I am seeing in my environment. I have a SQL Server 2014 Standard (12.0.4100.1 (X64)) installation.
On this server, I created a linked server to a Microsoft Access Database called Property, which lives on a separate windows file server. The linked server is also called Property. Everything here is joined to an Active Directory.
When I am logged onto the SQL Server locally, open SSMS I run the query:
```
select * from property...table
```
I am able to retrieve data.
When I open SSMS on my local PC and connect to my SQL Server and run the same query it errors. The error I get is "Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "property".
Similarly - I have a SQL Agent job that run the same query, it get another error:
Executed as user: DOMAIN\svc_sql_agent. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PROPERTY". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "PROPERTY" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412). The step failed.
Can anyone provide any insight as to why this is happening?

Thanks
Steve

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16010 More actions · Answer 1

it is highly likely that its one (or both) of 2 things

1 - server does not have required SPN's and is not authorized to delegate your credentials. (google for kerberos constrained delegation)

2 - your user does not have access to the SQL Server account temp folder (its an annoying things with the ACE driver)

stephenmbell SSC Eights! Points: 926 More actions · Answer 2

@frederico_fonseca - Thank you for the reply.

I've done a bit of reading on the Kerberos constrained delegation / SPN. And I have seen the access to the temp folder in some posts researching this. I've got a couple of questions.

My SQL Server Service runs under service account domain\svc_sql. My SQL Agent account runs under domain\svc_sql_agent. If I understand the delegation correctly, I create a SPN for domain\svc_sql:
```
setspn -s MSSQLSvc/sqlserverhost.domain.com:1433 svc_sql
setspn -s MSSQLSvc/sqlserverhost:1433 svc_sql?
```
This should permit the svc_sql account to perform constrained Kerberos delegation.
As for the temp account, on the SQL Server, I navigate to c:\users\svc_sql\AppData\Local and add "Authenticated Users" and "modify" permissions to the temp folder.

Should I have to restart the SQL service or anything?

Thanks

frederico_fonseca SSCoach Points: 16010 More actions · Answer 3

if you had to create the spn's I would advise restarting the service.

stephenmbell SSC Eights! Points: 926 More actions · Answer 4

Ok so now I've got the SPN setup:

spn-sql

I've got the delegation setup:

spn-delegation

I've got the permissions set on the Temp folder for the SVC_SQL user:

spn-permissions

When I run the query select * from property...table I am receiving the following:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "property" returned message "The Microsoft Access database engine cannot open or write to the file '\\fileserver\share\property\property.accdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "property".

Is this because the access database is located on a separate server? Or did I not follow the proper steps?

Thanks

Steve

frederico_fonseca SSCoach Points: 16010 More actions · Answer 5

Regarding the kerberos delegation - you may have misunderstood it.

Imagine the following scenario

User (PC) -> SQL Server -> stored proc writes to file on File Server -> File Server

in order for the credentials of the user to be supplied to the file server the SQL Server Account needs to be able to delegate the credentials of the user

So on the delegation tab of the SQL Server Account the services to delegate to would be the File Services (CIFS) on the File Server, not the MSSQLSvc on the SQL Server itself.

This may be why you getting the error so I would look at this first.

stephenmbell SSC Eights! Points: 926 More actions · Answer 6

I must really be crossing some wires here. I created an SPN for CIFS:

setspn -A CIFS/fileserverver svc_sql
setspn -A CIFS/fileserverver.domain.local svc_sql

Restarted the SQL Service on my sql server. Still could not connect. In addition, it broke anyone from accessing our fileserver.

Am I over complicating this?

Thanks

Steve

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 7

Steve,

You need to install the Access Database Engine - that will install the ACE provider for OLE DB.

HTH

stephenmbell SSC Eights! Points: 926 More actions · Answer 8

Thanks for the reply @william. I've got that installed already.

S