Read Excel File in SQL Server with OPENROWSET or OPENDATASOURCE

Problem

Sometimes you need to quickly reference the content of an Excel file in a SQL Server T-SQL script, however, this may not be as simple as it seems. In this tip I'll offer solutions for a few of the most common problems that may affect you when trying to read an Excel file using OPENROWSET or OPENDATASOURCE with SQL Server.

Solution

Let’s try to read a simple Excel file from within T-SQL code using OPENROWSET and OPENDATASOURCE. 

First of all, let me describe a little bit about my environment:

• Windows Server 2012 R2 Standard virtual machine with SQL Server 2016 SP2-CU7 (13.0.5337.0) default instance.

• A very simple Excel file (simple.xslx) which consists of 2 columns and 3 rows of numeric data. I did not need to install Excel on the virtual machine.

• For the first few examples I’ll use a sysadmin account and Windows authentication. My windows account is a member of the Administrators of the machine.

• The samples will work with Microsoft.ACE.OLEDB.12.0. and Microsoft.ACE.OLEDB.16.0.

For the examples I’ll use this simple piece of code to test each option.  The Excel file resides in the "C:\data" folder.

Before you have any luck, you may encounter one of the error messages below.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered

If you try to run one of the above commands you may get this error message:

This message will also pop up if you try to setup a linked server to the Excel file (when you "browse the tables").

Or if you try to import the data using the SQL Server Import and Export Wizard.

The above errors state that the Microsoft Access Database Engine has not been setup on the machine.  There are several ways you can check if this provider is installed on your machine.

Method 1 - Expand Server Objects > Linked Servers > Providers in SSMS and you should see in the list of providers.

Method 2 - From an account that is at least a member of the setupadmin server role run:

Method 3 - Run this basic PowerShell code on the server:

If there is no "Microsoft.ACE.OLEDB" in your results you’ll have to download and install the Microsoft Access Database Engine 2010 Redistributable or the Microsoft Access Database Engine 2016 Redistributable. Both engines let you read .xlsx files and they both have a 32bit and 64bit version, so download and install what you need for your server.

After the installation "Microsoft.ACE.OLEDB.12.0" or "Microsoft.ACE.OLEDB.16.0" will appear in the liked server provider list and in the PowerShell script output (as SOURCES_NAME) depending on which version you install.  If you install the 2016 version, both of these will show up.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource'

Once you have the Microsoft Access Database Engine components installed and you try to run the T-SQL code you might get this error message.

You need to turn on the advanced server option "Ad Hoc Distributed Queries". You'll need a sysadmin / serveradmin account or the ALTER SETTINGS server-level permission to make the change.

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

The next error you might encounter is the error below from either the OPENROWSET or OPENDATASOURCE option.

This happens because the OLE DB provider does not work as an in-process server.  To fix this either use the GUI (right click on the provider name and choose Properties) and check the "Allow inprocess" box or run the following code. 

If you want to use the 2010 version run the following:

If you want to use the 2016 version run the following:

At this point, if you use a sysadmin account and run the code to read the Excel file you should be able to see the content.

Using OpenRowset or OpenDatasource as a non-privileged SQL Server User

Let’s try to read our simple Excel file using a non-administrative Windows account.

The "RegularUser" is only a member of the Users group on the local machine. The corresponding SQL login is member of the public server role. Currently "RegularUser" has "Read & Execute", "List Folder Contents" and "Read" rights on the folder where the file is situated.

The other settings are as before - the installed OLE DB provider is allowed to run "in process" and the SQL server option "Ad Hoc Distributed Queries" is "on".

Let’s try this for the regular user account:

The result will be:

The fix is not obvious. The ACE OLEDB provider properties are in fact registry keys. When you set a property to 1 with [sys].[sp_MSset_oledb_prop] or tick a box in the GUI you create the corresponding registry key and set its value to 1. When you set the property to 0 using these methods you don’t set the key value to 0. Instead, you remove the registry key. See the below portion of code from sp_MSset_oledb_prop, this is from line 103 and on, you can see where the value is being written or the key is being deleted:

However, you need a "0" registry key in order to be able to read the file.

The straightforward way to achieve this is to run:

and edit the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.13.MSSQLSERVER\Providers\Microsoft.ACE.OLEDB.12.0 to set the key value to 0. Again, as a "regular user", you’ll need the appropriate permissions.

If you want to avoid this, just execute the OPENROWSET / OPENDATASOURCE as an administrative Windows login.

Next Steps

• As you can see ad-hoc Excel querying is not as simple as it seems. Since the error messages can get really confusing, please be sure to check:

  • Whether the Microsoft ACE OLEDB provider(s) are installed on the host machine

  • Whether the "Ad Hoc Distributed Queries" SQL server option is "on"

  • The properties of the ACE OLEDB provider you’re using

  • The privileges of your account both in SQL SERVER and Windows

  • The syntax of your script and the spelling of the file name and path

• You may consider that a better option is to import the data into a SQL Server database using BCP, import wizard, SSIS, etc.

By: Diana Moldovan