Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' denied

[theomen]

Hi All,

I'm having problems deploying a vb.net application onto a production server, which appears to be to do with permissions.

I have written a windows service that polls a folder for files, when a file is dropped into the folder, the application launches a stored procedure to import the data from the files (Excel spreadsheets) into an SQL Server database.

Locally this has been working great for months. However, trying to put it onto a server, I started getting the following error:

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

The code that generates this error is:

SET @strSQL = 'SELECT * INTO [' + @target + '] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @filename + ';HDR=YES;IMEX=1'', ''SELECT * FROM [' + @worksheet + '$]'')'

I've looked at the microsoft knowledge base at

http://support.microsoft.com/kb/327489,

but the registry entry is already setup correctly.

Are there any permissions I need to set for the SQL Server user to allow this ad hoc access, and stop this error? I don't want to set the application up to use the "sa" account.

 

[theomen]

Correction, doing what the microsoft article said did fix it, I was just looking in the wrong place The problem is now sorted.