December 17, 2010 at 7:14 am
Problem
The reason that one is getting the error is that when the sql service account uses jetoledb, it uses impersonation. The user that is being impersonated is the user that submitted the query. However, the user that submitted query is unable to read/write to the sql server service temp directory.
Solution
(1) Use winternals' processmon to determine what temp directory the sql service is account is using.
(2) Grant 'Domain Users' read/write access to that temp directory and that will take care of the problem.
September 21, 2017 at 9:39 am
I had this happen on my surface pro 4 laptop after Windows did a major update, and I'm putting my thoughts here in case it helps anyone. My situation:
- Windows 10 64 bit
- SQL Server 2008 developer edition (32 bit) installed as non-default instance
- SQL Server 2008 R2 express (32 bit) installed as default instance
- Office 2010 (32 bit)
- Excel spreadsheets stored using xls format.
For a long time (probably hundreds of executions) I successfully used OPENROWSET to read Excel spreadsheets using the Microsoft.Jet.oledb.4.0 provider. A few days ago the laptop did a major update requiring several restarts, after which it created a windows.old folder. After the update I had the error "Microsoft.jet.oledb.4.0 returns unspecified error msg 7303". I googled and it seems the cause is insufficient permissions on the SQL Server service's temporary folder for the user running queries in SQL Server. I tried quite a few things, including changing the system wide TMP= and TEMP= environment variables to point to c:\temp, and assigning full rights on c:\temp to the "everyone" group. This didn't help. I also tried finding what temporary folders SQL Server uses, and I could not find any clear definition.
Eventually after a re-start this morning, OPENROWSET started working again. I'm not fully sure why but last night I was using Windows explorer to browse all the temp folders, and it said I didn't have permission to a folder and prompted me for the UAC rights elevation. Maybe this was the folder that SQL Server used and maybe the elevation in rights didn't take place until a restart. Also I am unsure if the permissions problem is with the user running the query, or is with the SQL Server service permissions (if the former, then using SQL Server authentication might be a work around). I suspect the problem has something to do with the windows.old folder, maybe Windows created a copy of the old temporary folder during the update and didn't assign the correct permissions.
Finally I realize this is a very old thread, but it's an annoying problem and the error message is unhelpful.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply