The OLE DB provider Microsoft.Ace.OLEDB.12.0 for linked server (null) reported an error. Access denied.

Fri, Nov 30, 2012 One-minute read

I’m noting this here primarily because I’m almost certain to trip over this again in the future. If you want to use the ACE or JET providers to read an e.g., Excel spreadsheet to an e.g., SQL Server table, then something like this is possible. Assuming you had a simple Excel workbook (Book1.xlsx) and in Sheet1 you had

Col1 Col2
Test Test2

You could use this to load it:

SELECT * INTO #tmpTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=C:TempBook1.xlsx','SELECT * FROM [Sheet1$]')<br /> GO<br /> SELECT * from #tmpTable<br />

However, you will potentially hit some errors.

  1. If you’re on a 64-bit SQL Server, you will need this installed: http://www.microsoft.com/en-us/download/details.aspx?id=13255
  2. You’ll need to enable some bits:

`

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.4.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.4.0', N'DynamicParameters', 1
GO
`

  1. And here’s the kicker. You’ll need read to grant read access to everyone on C:Users{SQL Service account name}AppDataLocalTemp.

Edit: note - Read only access on the above folder didn’t seem to cut it. We granted Full Control which did. I wouldn’t advocate giving full control to Everyone, but…