Microsoft sql server ошибка 15281

Let’s see how to fix the error “15281: SQL server blocked access to procedure” in this article. Bobcares, as a part of our Server Management Services offers solutions to every query that comes our way.

Error 15281: SQL Server Blocked Access To Procedure

Once we install the SQL server and try to execute a query statement, we may see the following error:

SQL Server blocked access to procedure ‘procedure’ of component ‘component’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘component’ by using sp_configure. For more information about enabling ‘component’, see “Surface Area Configuration” in SQL Server Books Online.

The error details are as follows:

Message Number: 15281

Severity: 16

How to fix error 15281: SQL Server Blocked Access To Procedure?

Due to security configuration, certain SQL Server features or procedures are blocked by default. As a result, some procedures are no longer available.

In order to use such procedures, a database administrator must log in to a SQL Server instance with System Administrator (SA) privileges and execute the TSQL statement listed below to enable that SQL Server functionality. In this section, we will see how we can fix it using an example, Database Mail XPs.

Error:

Fix:

USE MASTER
GO

SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

/* Enable Database Mail XPs Advanced Options in SQL Server */
SP_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO

SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

[Need help with another query? We’re available 24/7.]

Conclusion

In this article, we provide a simple solution from our Support team to fix the above error.

USE AdventureWorks2012
GO
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=(local);uid=test;pwd=test',
'EXEC AdventureWorks2012.[dbo].[uspGetManagerEmployees] 16');

USE master
GO
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
--OUTPUT

Posted in Errors, SQL SERVER | Tagged Msg 15281 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012 | 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.

I’m upgrading our reporting server from 2008 R2 32-bit to 2014 32-bit.

We have linked servers that are excel files. These are .xls files (although the problem persists when they are updated to .xlsm). The connects work fine (select & insert into); however, when we run a stored procedure to create a new tab in the excel file.

USE [SupplementaryReports]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spExecute_ADODB_SQL] 
    @DDL VARCHAR(8000), 
    @DataSource VARCHAR(500), 
    @Worksheet VARCHAR(200)=NULL, 
    @ConnectionString VARCHAR(255) = 'Provider=Microsoft.ACE.OLEDB.12.0; 
    Data Source=%DataSource; 
        Extended Properties=Excel 12.0' 
        AS 
        DECLARE 
            @objExcel INT, 
            @hr INT, 
            @command VARCHAR(255), 
            @strErrorMessage VARCHAR(255), 
            @objErrorObject INT, 
            @objConnection INT, 
            @bucket INT 

SELECT @ConnectionString =REPLACE (@ConnectionString, '%DataSource', @DataSource) 
IF @Worksheet IS NOT NULL 
    SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet) 
    SELECT @strErrorMessage='Making ADODB connection ', 
        @objErrorObject=NULL 
EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT 
IF @hr=0 
    SELECT @strErrorMessage='Assigning ConnectionString property "' 
        + @ConnectionString + '"', 
        @objErrorObject=@objconnection 
IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection, 'ConnectionString', @ConnectionString 
IF @hr=0 SELECT @strErrorMessage ='Opening Connection to XLS, for file Create or Append' 
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open' 
IF @hr=0 SELECT @strErrorMessage ='Executing DDL "'+@DDL+'"' 
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute', 
    @Bucket out , @DDL 
IF @hr<>0 
    BEGIN 
    DECLARE 
        @Source VARCHAR(255), 
        @Description VARCHAR(255), 
        @Helpfile VARCHAR(255), 
        @HelpID INT 

    EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output, 
        @Description output,@Helpfile output,@HelpID output 
    SELECT @strErrorMessage='Error whilst ' 
        +COALESCE(@strErrorMessage,'doing something')+', ' 
        +COALESCE(@Description,'') 
        RAISERROR (@strErrorMessage,16,1) 
END 
EXEC @hr=sp_OADestroy @objconnection 

GO

I get the following error

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 116

SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 116

SQL Server blocked access to procedure ‘sys.sp_OADestroy’ of component ‘Ole Automation Procedures’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ole Automation Procedures’ by using sp_configure. For more information about enabling ‘Ole Automation Procedures’, search for ‘Ole Automation Procedures’ in SQL Server Books Online.

This error often means that ad hoc distribtued queries are not enough. But they are (both ‘show advanced options’ and ‘ad hoc distributed queries’ are set to 1). We also have the provider Microsoft.ACE.OLEDB.12.0 with allow in process and dynamic paramteres enabled.

The excel files work perfectly with 2008 R2. The code itself hasn’t been changed. All that’s changed is upgrading to 2014. Does anyone have any suggestions? You know… besides not using SQL to connect to excel files since we don’t have the resources to change that.

Much obliged!

I have two boxes runing SQL 2005 that i’m not able to get Database Mail working on.  I’ve gone thru the Wizard to set up my profiles and system parameters,  I’ve created an operator in SQL Agent, I’ve enabled Database mail on SQL Server 2005 Surface
Area configuration as well as SQL Mail.  but when I right click on Database mail and Try to send a test Message i get the error below. 

TITLE: Microsoft SQL Server Management Studio

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

ADDITIONAL INFORMATION:

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’, see «Surface Area Configuration» in SQL Server Books Online.  (Microsoft SQL Server, Error: 15281)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=15281&LinkId=20476

——————————

BUTTONS:

OK

——————————

Please advise.

Thanks

I have two boxes runing SQL 2005 that i’m not able to get Database Mail working on.  I’ve gone thru the Wizard to set up my profiles and system parameters,  I’ve created an operator in SQL Agent, I’ve enabled Database mail on SQL Server 2005 Surface
Area configuration as well as SQL Mail.  but when I right click on Database mail and Try to send a test Message i get the error below. 

TITLE: Microsoft SQL Server Management Studio

——————————

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

ADDITIONAL INFORMATION:

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’, see «Surface Area Configuration» in SQL Server Books Online.  (Microsoft SQL Server, Error: 15281)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=15281&LinkId=20476

——————————

BUTTONS:

OK

——————————

Please advise.

Thanks