Access Linked Server error "The provider did not give any information about the error."

Question

A little information.

I'm running SQL SERVER 2008 R2 64bit. I have Microsoft.ACE.OLEDB.12.0 provider installed.

On my production server I've setup a linked server to the access db using the provider listed above and everything works great.

Copy the Access db to our dev environment and try to setup linked server useing the same provider. Test Connection comes back as success Select from linked server comes back with

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYdb" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MYdb".

Both servers are running same version of SQL Server. Both are Windows NT 6.1 64bit OS.

When I set AllowInProcess to 1 the test connection fails.

Any help would be greatly appreciated as Google is failing me.

Thanks,

Chris

Answer 1

I found the issue but I'm not sure why its happening.

To query the linked server I have to right click SSMS and select Run as Admin. Then I can query the access db.

I dont know why this is the case as I'm remoted into the machine. I'm an administrator on the machine. I created the Linked Server to the Access Database which resides on the C:\Drive of the server.

But this will at least allow me to hand run my code. Since this is my development server I think that will suffice at least for now.

Answer 2

This is happening due to permissions on the 'Network Service' folder that SQL server uses for Linked Server setup. Inorder to access the linked server using another account (assuming that you have the server permissions set correctly) you will need to do the following:

Open Windows Explorer and navigate to the following folder (follow the next steps carefully as you are now dealing with system files) ‘C:\Windows\ServiceProfiles\NetworkService\AppData\Local’. This directory contains a folder called ‘Temp’, right click on this and select ‘Properties’. In the following dialog click the ‘Security’ tab. If you can see the Standard User account that you want to setup to use the Access Database Engine, this means that it already has some privileges on this folder, if it is not in the ‘Group of user names:’ list, it needs to be added. Either way, click the ‘Edit…’ button underneath the ‘Group of user names:’ list, this will launch the ‘Permissions for Temp’ dialog. If you need to add the Standard User to the permissions group do so using the ‘Add..’ button, making sure the account name is spelt correctly in the following ‘Select users or Groups’ dialog. Once the Standard User is on the permissions list, select that user and in the ‘Permissions for Standard User’ selection-menu check the ‘Full Control’ box under the ‘Allow’ column. Click OK; click OK again to exit this menu.

You should now be able to run access queries as any user that you provide permissions for in this way.

You will also have to make sure that you have run the following queries in Management Studio to correctly configure the Access Engine

-- These can be run as one batch.
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', 
    'DynamicParameters', 1 
GO

-- Run each of the following queries individually.
EXEC sp_configure 'show advanced options',1
reconfigure
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
Reconfigure

I hope this helps.