Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Does the comment below this article help? –  harrymc Jan 2 '12 at 15:27
    
This certianly gives me something to try. Thanks very much for your time - I will let you know how this goes... –  Killercam Jan 3 '12 at 10:45
    
Hi Harry, I am really under some pressure at the moment - I am going to try and look into this in detail by the end of the week. Thanks again... –  Killercam Jan 5 '12 at 10:10

2 Answers 2

up vote 1 down vote accepted
+50

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.

share|improve this answer
    
Sorry about the delay in the response. I have checked this on my local setup an it has seemed to do the trick. However, the problem takes a slightly different guise on a seperate setup so it will be interesting to see what happens there. Agian, thanks for your time and sorry about the lax response times - I have been upto my eye-balls! –  Killercam Jan 9 '12 at 9:00
    
harrymc. Thanks for your help so far and I apologise about my lax turn-around on responses. Despite my initial thoughts following a brief test, this has not solved the issue for me. The mass of coding I had to do is done and I now have some time to look into this issue with more depth. I enabled the 'sa' account, logged in and reran the above setup queries. I logged out of SQL and of Windows 7, and back in as a standard user, again I get the "linked server null" error. Any other ideas? –  Killercam Jan 11 '12 at 16:12
    
Before giving up on this one, have you checked the permissions on the temp folders (there are two : user & system), against accounts used to run the executables. –  harrymc Jan 11 '12 at 16:17

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.