Import Excel using OPENROWSET only works for SA

Question

Import Excel using OPENROWSET only works for SA

souLTower

SSCarpal Tunnel

Points: 4026
More actions
January 25, 2013 at 3:53 pm

#269733

In our development environment web application we're uploading excel files to the SQL server and importing them using openrowset. This was working fine. We reinstalled on another SQL server and the web app stopped working (all other DB related functionality still worked).
Log in via SSMS and run the command as SA, works great. As any other user you get the OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Can someone please help? The command obviously works on the server, just not as any user other than SA. I've tried changing permissions on the source folder, the SQL temp directory... Man, I'm stuck.
Thanks

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

"Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Check the security settings for the linked server. Is security set to "without security context", ""login's current security context", or "this security context"?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

You also may wish to check out these links:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/b41cc278-87e8-44a2-b7c3-12ef603cb964

http://stackoverflow.com/questions/9687631/openrowset-with-excel-file

And also make sure the file isn't currently open while running your process, this error can pop up if the file is open while it attempts to access it!

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 3

How can I check the security context? There is no linked server because we;re using openrowset.

I looked at stack - I would think if it was trying to access a folder that doesn't exist it would fail for the SA account as well. Same with the SP reconfigure statements. We've run those once. They should be for the server, correct? I see that they create registry entries.

Thanks for the response!

ST

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 4

Um...no linked server? Are you certain? I am under the impression you can't import data from EXCEL without using a linked server or some System DSN (which then would still point to a linked server).

I mean via SSMS, how would SQL be made aware of a file from the filesystem?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 5

We're running this in a stored procedure:

SELECT

myFields FROM

OPENROWSET(

'Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=\\myServer\UploadedFile\WorkLoad_Dist.xlsx', [Sheet1$])

I've also used a direct path to the file system. It works for SA, it works for a public account, it fails for a domain user account that has sysadmin permissions.

I looked under linked servers, all I have is a provider folder

Totally stumped.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 6

Oh ok, gotcha - very true, linked server not needed here...all I can suggest is that you need to verify that domain users have access to the file folder being accessed 🙁

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 7

I take that suggestion back!

Verify these things are true:

1. Install Office 2007 Data Connectivity Components (http://www.microsoft.com/download/en/confirmation.aspx?id=23734) - which I'm sure is already installed as it currently works when runing as 'sa'

2. Ensure "Ad Hoc Distributed Queries" is enabled on the server this is running on - you can turn this on by running:EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 GO

3. Change your OPENROWSET query to be:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=\\myServer\UploadedFile\WorkLoad_Dist.xlsx;HDR=Yes','SELECT * FROM [Sheet1$] ')

I was getting the same issue you were running running it as myself (sysadmin) when connecting as my windows authentication. After verifying the things above, it worked.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 8

Thank you. I thought about that when I woke up this morning, that making the SQL call in a string will cause the execution to be done by the SQL account and not the account that is running the query. I'll check and let you know.

Thanks

ST

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 9

No joy.

I'm wondering if it's caused my the domain permissions. I can create a public user with no special privileges on the SQL server and perform this operation with no problem. connecting using windows authentication, even as an admin user, no joy.

Thanks for the help. I'm not giving up... yet

ST

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

Long time no see, ST.

Who is the actual database owned by? If it's (hopefully) someone with "SA" privs, try adding WITH EXECUTE AS OWNER to the stored procedure and give the user privs just to execute the stored procedure (if they can't already).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 11

Hi, Jeff. Nice to hear from you again as well.

I tried your approach. It didn't work but may shed light on the overall issue. I get the following error:

Access to the remote server is denied because the current security context is not trusted.

Any thoughts?

ST

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 12

To anyone else who felt this pain here's the solution. First of all, ProcessMon is your friend. Seems that in Win Server2008 SQL Server 2008 R2 was impersonating the login that was initiating the process. My spreadsheet was being copied to:

C:\Windows\ServiceProfiles\NetworkService\AppData\Local\temp

Allowing my web server to read/write to this directory solved the issue.

Whew!

ProcessMon

http://technet.microsoft.com/en-us/sysinternals/bb896645

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 13

Interesting! I thought running the script to "Allow In Process" would have fixed that - good to know when dealing with a web server 🙂

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 14

It IS interesting.

Basically, if the user running the script or procedure is a DB user, the network service account (or whatever the SQL Server is running as) does the work. It has access to the directory.

However, if the account is a windows account, SQL impersonates that user. If you have multiple users that need access to the server and the process, you can put them all in a group and add permissions to the group.

My fear is that this solution is fragile. What if, in a SQL upgrade, the "working directory" is changed? The solution fails. I looked for a way to NOT have the proc impersonate the user but couldn't find it. I looked into EXECUTE AS but it specifically doesn't allow you to execute as network service. I didn't look as far as mapping to a credential. That may be an approach that I try.

ST