question

Kev Riley avatar image
Kev Riley asked

Location of user account temp dir for service accounts (Jet drivers on 64-bit)

Yet another "Jet drivers on 64 bit" question........ Had a scenario where a query using OPENROWSET wouldn't work on the newly built 64-bit server. And of course, a lot of the answers here (thanks to @Pavel) pointed me in the right direction - [Insert data from Excel file through OPENROWSET function]( http://ask.sqlservercentral.com/questions/75297/insert-data-from-excel-file-through-openrowset-function) - [import excel to sql]( http://ask.sqlservercentral.com/questions/74179/import-excel-to-sql) - [SSIS Excel dumping]( http://ask.sqlservercentral.com/questions/73948/ssis-excel-dumping) And despite downloading and installing [Microsoft Access Database Engine 2010 Redistributable]( http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255), I still had an error. **EDIT** : I should add this is the error I was getting > OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)". **END EDIT** This [post]( http://sqlserverpedia.com/blog/sql-server-bloggers/too-many-bits/), and subsequently this [post]( http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/b9ad4df2-b256-4a33-911b-a06001250f9e) got me going in the right direction - essentially the account running the query needs permissions to the folder that is the temp directory of the account running the SQL Server service. I was running this on my local instance, and normally the directory for a normal user is C:\\Users\\*user.name*\\AppData\\Local\\Temp, but my SQL instance had been configured to run as Network Service - where is this account's temp directory? I managed to workaround the issue by changing the service account to a domain user. This then got me thinking, on the live server, SQL is running under LocalSystem - again where would I find this account's temp directory. So finally, the question.... where is the 'user' temp directory for - Local Service (NT AUTHORITY\\LOCAL SERVICE) - Network Service (NT AUTHORITY\\NETWORK SERVICE) - Local System (NT AUTHORITY\\SYSTEM) and does it differ across Windows Server 2008, Server 2008R2, Windows 7, etc....
64-bitopenrowsetservice-accountjet
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn_Melton avatar image
Shawn_Melton answered
I believe the path would be under: C:\\Windows\\system32\\config\\systemprofile\\AppData\\Local\\Temp I checked an Windows XP and Windows 7 and that is the directory. I don't believe it has changed but have not confirmed it. **EDIT** Test done so far on Windows 7 machine. Setup PowerShell script to execute 2 commands...

$env:USERNAME | Out-file Testit.txt
$env:TEMP | Out-File Testit.txt -Append
Results: - Run as System (NT AUTHORITY\\SYSTEM) = Hostname / C:\\Windows\\TEMP - Run as Network Service (NT AUTHORITY\\NETWORK SERVICE) = Hostname / C:\\Windows\\SERVIC~2\\NETWOR~1\\AppData\\Local\\Temp - Run as Local Service (NT AUTHORITY\\LOCAL SERVICE) = Hostname / C:\\Windows\\SERVIC~2\\LOCALS~1\\AppData\\Local\\Temp
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
One directory for all 3 service accounts?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I presume the 'SERVIC~2' is ServiceProfiles?
0 Likes 0 ·
Shawn_Melton avatar image Shawn_Melton commented ·
Yep. That is what I am presumm'n.
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
According to [this Stackoverflow post][1] the location does differ from pre Win7/2008 and post Win7/2008. Windows XP (and I would think Windows 2003) has an entry under "Documents and Settings" Windows 7/2008/2008R2 uses C:\\AppData or C:\\Windows\\Temp Great question BTW (+1) [1]: http://stackoverflow.com/questions/3637605/where-can-a-service-running-under-the-local-service-account-save-its-state
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Domi avatar image
Domi answered

I wasted many days looking for solution of error "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". [SQLSTATE 42000] (Error 7303)" and so on.

Thank you for advices, you gave me great new idea!

On our standalone MSSQL2014 @ Windows 2012R2, for export to Excel file SQL Agent Job needed write privileges for temporary folder of SQL Server (of the same instance). In our case "C:\Users\MSSQL$<instance>\AppData\Local\Temp".

When it has it, OLE DB Jet exporting to Excel file works normally (as ran manually from SSMS :)


Without these privilages the same job generated this stupid error:

"Executed as user: NT SERVICE\SQLAgent$<instance>. Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Nieokreślony błąd." <it means: "Unknown error" or similar>. [SQLSTATE 01000] (Error 7412). The step failed.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Seamus avatar image
Seamus answered

@Domi - Thanks to everyone, especially Domi - this was driving me nuts but giving SQLSERVEAGENT permissions to that folder FINALLY fixed it.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.