Sql-server – SQL errors Microsoft.ACE.OLEDB.12.0 on dev box, but works on server

excelsql serversql-server-2008-r2

I'm inserting into a .xls spreadsheet from TSql code. Runs fine as an Agent job and on command from the server, but not on my dev box.

Both complex and simple commands like:

Insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')  
SELECT 'hello'

Select * from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\test.xls;', 'SELECT * FROM [Sheet1$]')

SQL Server 2008 R2 64bit installed on Windows Server 2008 R2. SSMS installed, these commands work there.

When trying to run the same commands from another computer (my dev box), I can't do it, even though I've tried the suggestions below and elsewhere. I have Windows 7 64bit, SSMS 2014 installed here, but had the same issues with 2012 and 2008 R2.

When I run this command in SSMS on my dev box, does it send the entire command to the SQL server? Which security context does it run in? I really want to be able to develop these kinds of stored procs on my dev box, right now this is the only reason I ever RDP to my server, which I'd rather not do.

I've tried the steps listed here: [http://www.codeproject.com/Questions/692004/The-OLE-DB-provider-Microsoft-ACE-OLEDB-for-l] to no help.

EDIT: The error I'm getting is:
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)".

UPDATE: Turns out installing the 2007 Access Engine allows me to work with Microsoft.ACE.OLEDB.12.0 from my dev box, but only when they're stored directly on the SQL Server. My files are stored on a separate computer (accessed via share), and I still cannot work with those files. Looks like a double-hop permissions issue, but I still can't find it.

EDIT: I can do these queries from my dev box when logged in as SA, but not as myself.

Best Answer

Have you tried to give permissions to the temporary folder that may be in use for the user? (The root of the drive) It would be very common that your dev box would have permissions to C:\stuff and not on another box.

A simple way to test would be to use xp_cmdshell (if enabled, included the enable and disable stuff since its pretty dangerous):

exec sp_configure 'show advanced options',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
exec xp_cmdshell 'nul >> C:\test.xls'
go
exec sp_configure 'xp_cmdshell',0
go

exec sp_configure 'show advanced options',0

go
reconfigure

When I have an issue, I will get the response: Access is denied.

Also, if the issue is that the server cannot access the files locally and you try this method, cmdshell will fail because cmd doesnt handle UNC paths well. You could invoke powershell within xp_cmdshell (oh gawd), but I leave that to another SO answer https://stackoverflow.com/questions/16436405/how-to-run-powershell-in-cmd

Related Question