0

We're running SQL Server 2014 with all the latest service packs. Our users heavily rely on OPENROWSET for importing XLSX, CSV and TXT files. Example:

SELECT * FROM OPENROWSET ('MSDASQL','DRIVER={MICROSOFT access TEXT DRIVER (*.TXT, *.CSV)};', 'SELECT * FROM F:\IMPORT\MyTable.CSV')

The configuration of the drivers/providers worked fine for all users for years. However, to troubleshoot a memory dump issue (occurring once every 2-5 days), it was recommended that we upgrade both the ACE (Access Database Engine), as well as MSDASQL (SQL ODBC) Drivers.

After the driver upgrade, we noticed that queries with OPENROWSET statements would hang, causing us to restart the SQL server.

We noticed Allow inprocess was selected, as corrective measure, we unselected this, which successfully stopped the crashing/hang-ups.

enter image description here

Although OPENROWSET queries now run fine for me (as an admin user), however every other user that uses Windows Authentication now gets this error message and can't use OPENROWSET at all.

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "MICROSOFT.ACE.OLEDB.12.0" for linked server "(null)".

Points to mention:

  • This issue happens with all drivers/providers:

      MSDASQL                     Microsoft OLE DB Provider for ODBC Drivers
  Microsoft.ACE.OLEDB.12.0    Microsoft Office 12.0 Access Database Engine OLE DB Provider
  Microsoft.ACE.OLEDB.16.0    Microsoft Office 16.0 Access Database Engine OLE DB Provider

  • Yes, the users have access to the necessary files/folders.

  • I've ensured Disallow Ad-Hoc Access is unchecked for all providers.

  • Stand-alone SQL logins (non-Windows Authentication) users surprisingly do not have any issues

  • We don't have the resources to flip our processes/scripts to ingest data via SSIS packages.

  • The registry values reflect EXACT desired provider settings:

enter image description here

Depth of Field
  • 307
  • 2
  • 16
  • 1
    Do the Windows users have permissions granted to them in Component Services > Computers > (Server Computer) > DCOM Config > MSDAINITIALIZE > Properties > Security? – AlwaysLearning Sep 20 '22 at 02:34
  • Admistrators have Full Access. Users have Read-only access. Should users have FULL access? – Depth of Field Sep 20 '22 at 18:20

1 Answers1

0

We solved this (Thanks @alwaysLearning for pointing me in the right direction) We had to adjust the security settings in the DCOM configuration:

This Article explains it in great detail!

https://www.sqlservercentral.com/articles/setting-up-linked-servers-with-an-out-of-process-oledb-provider

Depth of Field
  • 307
  • 2
  • 16