Troubleshoot SQL Server blocking access to the STATEMENT ' Openrowset/opendatasource ' of the component ' Ad Hoc distributed Queries '

An ASP. NET data import and export as required, the results reported the following error:

SQL Server blocked access to the STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component was shut down as part of this server's security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "surface area Configurator" in SQL Server Books Online.

Open the ad HOC distributed queries component and execute the following statement in the SQL Query Editor:

1 --Configure Permissions2 EXECsp_configure'Show advanced Options',1; 3 GO 4 RECONFIGURE; 5 GO 6 EXECsp_configure'Ad Hoc Distributed Queries',1; 7 GO 8 RECONFIGURE; 9 GO 

Execution Result:

Close the ad HOC distributed queries component and execute the following statement in the SQL Query Editor:

1 execsp_configure'Ad Hoc Distributed Queries',0;2 Go3 Reconfigure;4 Go5 execsp_configure'Show advanced Options',0;6 Go7 Reconfigure;8 Go

Troubleshoot SQL Server blocking access to the STATEMENT ' Openrowset/opendatasource ' of the component ' Ad Hoc distributed Queries '