sp_configure | SQL with Manoj

SQL Error – SQL Server blocked access to STATEMENT ‘OpenRowset/ OpenDatasource’ of component ‘Ad Hoc Distributed Queries’

December 25, 2016 Leave a comment

Today while executing a Stored Procedure which internally executes a remote query via Linked Server, I got following error:

Msg 50000, Level 16, State 127, Procedure spExecureRemoteQuery, Line 50

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.

The above clearly means that the “Ad Hoc Distributed Queries” option is disabled on the database instance.

–> Run below Query to check if this property is disabled, if will show you 0 value under Config & Run value columns:

sp_configure 'show advanced options', 1
GO
EXEC sp_configure
GO

–> To enable it run the below Query:

sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
GO
EXEC sp_configure
GO

…the value under Config & Run value columns shows 1, means that now the Property is enabled and you can execute your Remote/Linked-Server queries.

Categories: SQL Errors Tags: Ad Hoc Distributed Queries, sp_configure

SQL Error – Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option

June 26, 2015 Leave a comment

Error Message:

Executed as user: DOMAIN\Account. Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option. [SQLSTATE 42000] (Error 6263). The step failed.

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable clr enabled
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'clr enabled'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Categories: SQL Errors Tags: sp_configure

SQL Error – SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’

June 25, 2015 2 comments

Error Message:

Executed as user: DOMAIN\Account. SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of component ‘Database Mail XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Database Mail XPs’ by using sp_configure. For more information about enabling ‘Database Mail XPs’, search for ‘Database Mail XPs’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable Database Mail XPs
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'Database Mail XPs'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Categories: SQL Errors Tags: sp_configure

SQL Error – SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’

June 24, 2015 Leave a comment

Error Message:

Executed as user: DOMAIN\Account. SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online. [SQLSTATE 42000] (Error 50000). The step failed.

–> Resolution:

-- show advanced options
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- check if it has been changed
EXEC sp_configure 'xp_cmdshell'
GO

-- hide advanced options
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

Categories: SQL Errors Tags: sp_configure

SQL Error – Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 SQL Server blocked access because this component is turned off

April 14, 2015 Leave a comment

I was trying to execute a Windows Shell command from SSMS by using xp_cmdshell Extended Stored Procedure and encountered an error highlighted below in RED color:

EXEC xp_cmdshell 'DIR *.exe'

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure.

Even if you are a sysadmin and as by default this option is turned off after a new SQL Server installation, you need to manually turn on this option, let’s see how:

-- To change the advanced options:
EXEC sp_configure 'show advanced options', 1
GO
-- To update the current value for advanced options:
RECONFIGURE
GO
-- To enable the xp_cmdshell option:
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the new configured value for xp_cmdshell option:
RECONFIGURE
GO

–> If you want to suppress the messages returned by the xp_cmdshell extended Stored Procedure use the optional parameter no_output.

exec xp_cmdshell 'DIR *.exe', no_output

Please Note: If xp_cmdshell is executed within a BATCH and returns an error, the complete batch will fail. In earlier versions of SQL Server the batch used to continue executing.

If the user is not member of sysadmin role and want to use this extended SP then a Proxy account credential need to be created by using sp_xp_cmdshell_proxy_account. Check this KB article for the same: link.