Today I was struggling with linking a Microsoft Excel file to my SQL Server for importing data from Excel files. Being stuck with the error:

Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

The main issues with MS SQL Server and Excel as Linked Server objects are in the areas of:

• 32/64 bit incompatibility between Office and SQL Server
• Wrong OLE DB driver or not installed
• Additional options required for the OLD DB Provider (Dynamic Parameter and Allow Inprogress)
• Access rights to the TEMP folder of the process running SQL Server.

In my case the service is running as NT Service\MSSQL$CAPACITYCENTER. Based on the information found I verified modify access rights for this account under

• C:\Users\MSSQL$CAPACITYCENTER\AppData\Local\Temp

All was supposed to be ok, but the error remained. So I decided to run Sysinternals Process Monitor to check the SQLServr.exe process for failing file activities:

Although the user MSSQL$CAPACITYCENTER has Full Control on this folder, the LOCAL Service and NETWORK SERVICE account also need Write and Modify permissions to this folder. After adding these the error resolved.