SQL Server 2008 R2 (x86) and the Access Engine

Question

All,

I have set up the Access Database Engine for SQL Server 2008 R2 (Service Pack 1) on many machines running x64 Windows 7 without any problems running as 'Administrator' (and setting up SQL Server as 'sa'). I have also installed the Access Database Engine on a x86 based machine running Windows 7 and SQL Server 2008 R2 Express Edition (x86) again without problems. However, when I attempt to setup the Access Database Engine using the Developer Edition of SQL Server 2008 R2 under x86 Windows 7 it does not work!? It works for Express, but not for Developer. I have attempted a full reinstall of SQL, but still it does not work, throwing the following error:

OLE DB provider "Microsoft.Ace.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". 
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 "(null)".

The machine information is (obtained from the command line running 'systeminfo'):

OS Name:              Microsoft Windows 7 Professional
OS Version:           6.1.7601 Service Pack 1 Build 7601
OS Configuration:     Standalone workstation
System Type:          X86-based PC
Processor(s):         1 Processor(s) Installed. 
                      [01]: x64 Family 6 Model 42 Stepping 7 GenuineIntel ~2501Mhz

To setup the Access Engine having successfully installed the 32-bit version, I go into management studio and run the following queries:

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', N'DynamicParameters', 1 
GO

-- Then each of the following separately.
EXEC sp_configure 'show advanced options',1
reconfigure
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

One of the queries I usually run to establish whether or not the Linked Server is configured correctly is:

SELECT * FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'J:\Data\thisDir\thatDir\someAccessFile.mdb'; 'admin';'',thisTable);
GO

This process works for all configurations including SQL Express 2008 R2 both (x86 and x64) with the Access Database Engines (x86 and x64 respectively), but for Developer Edition on x86 Windows 7 it does not, why?

Has anyone come across this who can offer me some advice as to how to solve this issue?

Thanks very much for your time.

Answer 1

In this post the problem was the permissions on the Temp folder :

My solution was to log into the server once using the service account for SQL Server. Everything worked immediately thereafter, and I replicated this on a second server.

I read elsewhere that the Ace and Jet engines both require access to the Temp folders to do their work. I suspect logging in once was necessary to “wire-up” access to the Temp folder.

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.