IDBInitialize::Initialize returned 0x80004005 Error while running query against linked server to Access or Excel

Are you receiving below error when you run a Sql Server Job which connects to MS Access or Excel through Linked Server?

"Executed as user: . OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed."

Don't worry you have come to the right place!

To resolve this issue you need to first understand how Linked Server in SQL Server works.

Whenever a query is executed against linked server, SQL Server creates a temporary DSN for linked server in the TEMP Directory of the login using which SQL Server Agent Service is running.

For Example:

If Sakthi\SQLUser is the login account configured for SQL Server Agent, then by default the TEMP directory for this user will be C:\Documents and Settings\SQLUser\Local Settings\Temp

So SQL Server will create a temporary DSN here while performing Linked Server Operations, Now if you don't have permission to access this folder and when you are trying to execute a query against linked server then you will receive the error message stated above.

Solution:

1.Log on to the computer(Remote Desktop Login) by using the SQL Server start up account. (Ex:Sakthi\SQLUser)
2.Create a folder named Temp in the operating system installation directory.(Ex:C:\Temp)
3.Permit full access to a non-administrator account on the Temp folder. (i.e Add "Everyone" and give full permission)
4.Set the value of the TEMP and TMP user variables of the SQL Server startup account to the newly created Temp folder. To do so, follow these steps:
a. Right-click My Computer, and then click Properties.
b. Click the Advanced tab, and then click Environmental Variables.
c. In the User variables for Logon User list, click TEMP, and then click Edit.
d. In theVariable Value box, type C:\Temp as the location of the new Temp folder, and then click OK.
e. Repeat steps c and d to set the value of the TMP variable.
f. Click OK two times.
5.Log off, and then log on to the computer by using SQL Server startup account.
6.Restart the SQL Server services.

Now if you run the job or query which is creating this issue you will get result as SUCCESS!