July 27, 2011 at 7:34 pm
I've got some devs trying to do an export from sql 2008R2 to excel 2007 directly via TSQL.
Why not SSIS? Because the idea is to create a data driven framework that allows one to easily add new queries to the set of available exports.
A quick web search on anything like this topic will quickly reveal how surprisingly difficult this is to set up. I'll note what I did, and then where I got stuck. I think I know the cause for the sticking point but would be thankful if someone could confirm my hunch.
1) Download the 64 bit ace driver and install on the sql server: http://www.microsoft.com/download/en/details.aspx?id=13255
2) Restart the sql service
3) In object explorer on the instance, navigate to server objects > linked servers > providers.
4) Right click on microsoft.ace.oledb.12.0
5) Check "allow in process"
6) Allow ad hoc distrubuted queries (sp_configure)
7) Create an excel file on a share local to the sql server.
8) Grant permission to this share to the (integrated) windows user who will be executing the TSQL.
9) Have the user run the following query.
select * from openrowset (
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=\\LOCAL_SERVER\SHARE\FILE.xlsx;HDR=YES',
'select * from [Sheet1$]'
)
This works. Hooray! You can even do inserts.
However, if you attempt the same thing using 'Database=\\OTHER_SERVER\SHARE\FILE.xlsx' then the query fails with the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
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)".
My hunch is that the query to the remote share fails because the AD account of the integrated user running the query has not been trusted for delegation. Of course, it is unlikely that one would set up all users who may need to run queries like this as trusted.
I tried to confirm this by creating a procedure with an "execute as" clause in the declaration, executing as dbo (which IIRC will ultimately map to the service account once we hit windows, and this account is trusted for delegation in our environment):
create procedure spOPENROWSET with execute as 'dbo' as begin
set nocount on
select * from openrowset (
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=\\OTHER_SERVER\SHARE\FILE.xlsx;HDR=YES',
'select * from [Sheet1$]'
)
end
Interestingly the create procedure statement fails with the same error.
July 28, 2011 at 6:03 am
allmhuran (7/27/2011)
...Why not SSIS? Because the idea is to create a data driven framework that allows one to easily add new queries to the set of available exports.
...
Why not create data driven SSIS package?
However I think if your task is "allow easily add new queries to the set of available exports", than you don't need data driven SSIS. It is easy enough to create new SSIS package to extract dat to Excel.
July 28, 2011 at 12:34 pm
It's been my experience that the SQL Server service account is what would need access to the excel file. Not the user running the query.
July 29, 2011 at 9:36 pm
Data driven SSIS: yes, I did suggest this as a consideration (I'm not the one actually developing or driving the development of the tool). I think the difficulty with that idea is the problem of having your sources and destinations "strongly typed". Having the source query come from a variable whose value is set via a SQL configuration was what I was thinking, but whether the destination component in the data flow would "like" this, I'm not so sure. In my experience SSIS can be a pain in this area.
On the permissions: My initial guess (I don't use the openrowset type functions much) was that the service account would be the execution account too, but when I set up a few things for the devs one was a share to which the service account had permission, but the operation would not succeed for them until I granted NTFS and share permissions to the dev group. This is also what led me to believe that kerberos was the problem with the remote share (since the service account is trusted for delegation, but the dev accounts are not)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply