Import Excel using OPENROWSET only works for SA

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Import Excel using OPENROWSET only works for...

21 posts, Page 1 of 3

1 2 3 »»»

Import Excel using OPENROWSET only works for SA

Rate Topic

Display Mode

Topic Options

Author

Message

souLTower

Posted Friday, January 25, 2013 3:53 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:52 AM
Points: 93, Visits: 424

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

Post #1411944

MyDoggieJessie

Posted Friday, January 25, 2013 4:19 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037, Visits: 3,761

"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; They'll drag you down to their level and beat you with experience" Wink

Post #1411948

MyDoggieJessie

Posted Friday, January 25, 2013 4:23 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1411950

souLTower

Posted Friday, January 25, 2013 4:29 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:52 AM
Points: 93, Visits: 424

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

Post #1411952

MyDoggieJessie

Posted Friday, January 25, 2013 4:43 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1411954

souLTower

Posted Friday, January 25, 2013 4:47 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:52 AM
Points: 93, Visits: 424

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.

Post #1411956

MyDoggieJessie

Posted Friday, January 25, 2013 5:22 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037, Visits: 3,761

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 Sad

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" Wink

Post #1411963

MyDoggieJessie

Posted Friday, January 25, 2013 5:31 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 2:19 PM
Points: 2,037, Visits: 3,761

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; They'll drag you down to their level and beat you with experience" Wink

Post #1411965

souLTower

Posted Saturday, January 26, 2013 7:35 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:52 AM
Points: 93, Visits: 424

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

Post #1412028

souLTower

Posted Saturday, January 26, 2013 7:58 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 14, 2013 3:52 AM
Points: 93, Visits: 424

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

Post #1412034

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions