OPENROWSET problem connecting to Access database

[NilsBevaart]

Hi all,

For a flexible import in our SQL database (SQL 2000 on a Windows 2003 server) we use OPENROWSET. In our development on a local instance of SQL Server, we have no problems with a connection to Accesss. However, when we deploy the same code with the same Access database to our test server we get an error indicating to use a Linked Server.

We set all neccessary parameters for allowing ad hoc queries and encounter no problems with queries to different SQL Servers and Oracle databases. Also, when we execute the query to the Access database on a local disk from a job, it works fine. Run the same query to an Access database on a fileshare from a job results in an error.

How come the OPENROWSET selection cannot be run from the query analyser or to an Access database on a fileshare? What security settings are blokking?

We use the following query:
SELECT top 10 *
FROM
OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Testdata\test.mdb';
'admin';'',Table1)

Thanks for all your help,
Nils

 

[onpnt]

There is nothing wrong with your query. It will probably be either file system permissions or security configured in Access or the file isn't there

The error would be helpful

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[NilsBevaart]

We get a relative blank error:
Server: Msg 7415, Level 16, State 1, Line 1

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

This indicates a security issue to the database or filesystem, but I can connect to the drive using xp_cmdshell 'dir xxx'

What security issues can I check and fix?

 

[onpnt]

Does this key have a 1 or 0 in it?

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Providers\Microsoft.Jet.OLEDB.4.0

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[onpnt]

Sorry.....for "AllowInProcess"

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}