OpenRowset Msg 15281, Level 16

On some servers when you try to load an Excel file using OpenRowset, you will get this delightful error:

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator
can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, search for ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.

This scary looking error is actually due to a default setting in SQL Server for security reasons. This protects your server from any rogue activity.

However sometimes you really need to load a file using OPENROWSET as you may not have access to SSIS.

The error message was very helpful and the solution was found on Books Online, follow this link to see more information.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/ad-hoc-distributed-queries-server-configuration-option?view=sql-server-ver15

We need to enable on the server ‘ad hoc distributed queries’. This is accomplished using the commands below.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

The Microsoft Books Online has this important note: Enabling the use of ad hoc names means that any authenticated login to SQL Server can access the provider. SQL Server administrators should enable this feature for providers that are safe to be accessed by any local login.

Now I try and run my load again and it succeeds.

I query the table and see all of the rows got loaded.

Some people recommend you change the settings back after you do the load (import), but if I am the only person that has access to the server I will leave them enabled.

The command below will disable the ability to run the OPENROWSET query.

EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 0
RECONFIGURE
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s