Problem creating a Linked Server to MS Access 97 database from SQL Server 2019
-
Hi there
I need help on creating a Linked Server to an MS Access 97 database from a SQL Server 2019 express instance.
We have a SQL Server Express 2019 instance and we are trying to create a Linked Server to an MS Access 97 database located on a network drive.
Now I know that people will say that Access 97 is 32 bit and SQL Server is 64 bit, however we were able to create successfully a linked server from SQL Server 2019 express to an Access 97 database located on the C drive of the SQL Server. We were able to browse the linked server and look at data from the Access database .
But also crucially we had that MS Access 97 database open at the same time and there was no locking issues.
So more information:
Access 97 database network location : \\lonsql01\G_Drive\Sirius\Database\Sirius_Copy.mdb
User: TestCompany\SQLDeveloper01 (Windows Account)
Steps:
1) We gave the user SQLDeveloper01 permissions on the Access 97 network location (Read & Execute/List Folder Contents/Read)
2) In SQL Server express 2019, we set the User up with Windows Authentication, Server Role of Public , mapped to the master database as Public user
3) Then we proceeded to create the linked server with the following properties:
Provider : Microsoft Office 12.0 Access Database Engine OLE DB Provider
Product Name: OLE DB Provider for Jet
Data Source: \\lonsql01\G_Drive\Sirius\Database\Sirius_Copy.mdb
Then Security set up as shown in the screenshot
Then Server Options:
But i then get the error message
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?
------------------------------
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 "Sirius_Copy".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Sirius_Copy" returned message "The Microsoft Access database engine cannot open or write to the file '\\lonsql01\G_Drive\Sirius\Database\Sirius_Copy.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.". (Microsoft SQL Server, Error: 7303)
The database at this point has not been opened
-
You also need to grant the SQL Server service account permission to the network share and the file. If you're using a Domain service account, it'll be easy, it's just another user, if you're using a local service account, you're going to probably find it easier to switch to a domain account.
The user (SQLDeveloper01) may have permission to it, but it's the SQL Server service account that's actually trying to "touch" the Access file. As a point in fact, you could even remove the permissions for SQLDeveloper01 and only grant the SQL Server service account access and it'd work.
- jasona.work wrote:
You also need to grant the SQL Server service account permission to the network share and the file. If you're using a Domain service account, it'll be easy, it's just another user, if you're using a local service account, you're going to probably find it easier to switch to a domain account.
The user (SQLDeveloper01) may have permission to it, but it's the SQL Server service account that's actually trying to "touch" the Access file. As a point in fact, you could even remove the permissions for SQLDeveloper01 and only grant the SQL Server service account access and it'd work.
OK so the SQL Server service account has full access to the network share and file but we are still not getting any luck.
We also set the user SQLDeveloper01 to be the SQL Server Service Account that logs onto the server and this still didnt work
-
Two more thoughts, then, and a comment:
- Check the Event Logs on the server that has the Access files on it, especially the Security Event Log. Look for any failures, especially from the SQLDeveloper01 account
- Do you have some sort of anti-malware / anti-virus on the servers, that might be configured to scan files on access? If so, I've had issues with such causing problems trying to ETL in large Excel files.
The comment is, I would not suggest making the service account and the account used to connect to SQL the same account. That's just begging for security issues down the road. If it's an isolated, no outside access test environment you can get away with it, but even then, it'd be bad practice and creating bad habits.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply