Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is usually due to the current user not having access to the Temp folder inside the NetworkService folder. Since we are running this in Windows Server 2008, the directory structure is quite different than what most are eluding to. I have allowed access to every temp folder in the "root:\Documents and Settings\" directory, and to no avail.

This is the procedure I am using to add the linked server:

EXEC sp_addlinkedserver 
@server = N'OS_Access', 
@provider = N'Microsoft.ACE.OLEDB.12.0', 
@srvproduct = N'',
@datasrc = N'C:\RTBData\Data\OS.mdb';
GO

It creates the linked server with no problem, but I am not able to view the tables/views of the database. Likewise, my ASP.NET application cannot access it either.

I have tried both ACE and JET(64-bit)(by installing the Data Connectivity Components for Office), and they both do not work. I have also tried configuring the ACE provider with "Dynamic Parameter" and "Allow InProcess" to true.

Additionally, I tried upping the memory usage by the MSSQLSERVER services by adding "-g512;" in front of "startup parameters" string in SQL configuration manager to rule out memory issues.

If anyone could shed some light on this that would be fantastic! Thanks!

share|improve this question
1  
Have you tried installing the Data Connectivity Components for Office for that provider? microsoft.com/en-us/download/details.aspx?id=23734 –  Max Vernon Mar 15 '13 at 21:19
    
I apologize, that is what I meant by using ACE and 64-bit JET. I will edit the question to reflect. –  JuniperAsh Mar 15 '13 at 21:24
    
No apology necessary. I just installed that package, and it doesn't work for me either. –  Max Vernon Mar 15 '13 at 21:32
add comment

1 Answer 1

up vote 1 down vote accepted

I fixed the problem by disabling UAC within the control panel. I am unsure what changes this made, but it might help others!

share|improve this answer
add comment

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.