48

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', see "Surface Area Configuration" in SQL Server Books Online.

I tried to enable Ole Automation Procedures as:

sp_configure 'show advanced options', 1 

GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'show advanced options', 1 
GO 
RECONFIGURE;

When I am executing query, I'm successfully getting output. But when trying through windows forms, I'm getting this error. Please help me

Blag
  • 5,818
  • 2
  • 22
  • 45
FM Plus
  • 550
  • 1
  • 5
  • 12
  • 1
    [Sql Server Blocked access tot procedure](http://sqlsolace.blogspot.com/2009/09/sql-server-blocked-access-to-procedure.html) I did a google search on your error what's preventing you from doing the same.. here is the link – MethodMan Jan 20 '15 at 18:44
  • 2
    What does this return: `SELECT value_in_use FROM sys.configurations WHERE name = 'Ole Automation Procedures';`? Did you try using `RECONFIGURE WITH OVERRIDE;`? – Aaron Bertrand Jan 20 '15 at 18:46
  • Here, I can send sms by executing the query, but when tried through windows forms, I,m getting this error. – FM Plus Jan 20 '15 at 18:50
  • 2
    Well first let's figure out what on earth you're "solving" with sp_OA anyway. This might seem like a valid approach in SQL Server 2000 but there are much better ways to do whatever you're doing and still not have to use the buggy, memory leak-y and deprecated sp_OA procedures. – Aaron Bertrand Jan 20 '15 at 19:34
  • ^^ @AaronBertrand: Im trying to send sms using query by calling an API. when executing through query, Im hetting the output. But when tried though windows forms in C#, Im getting above error. Hope you get me – FM Plus Jan 21 '15 at 02:43
  • CLR is a much better option than sp_OA. http://jethvamohit.blogspot.com/2010/03/clr-stored-procedure-calling-external.html – Aaron Bertrand Jan 21 '15 at 15:27

4 Answers4

38

The following example shows how to view the current setting of OLE Automation procedures.

EXEC sp_configure 'Ole Automation Procedures';
GO

The following example shows how to enable OLE Automation procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
Hadi Salehy
  • 917
  • 11
  • 10
15

Try to run this 3 

sp_configure 'show advanced options', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'Ole Automation Procedures', 1 
GO 
RECONFIGURE; 
GO 
sp_configure 'show advanced options', 1 
GO 
RECONFIGURE;
sansalk
  • 4,595
  • 2
  • 36
  • 37
10

Enabling the Ole Automation procedures is only part of the problem. You need to grant execute:

GRANT EXECUTE ON master..sp_OACreate to [??]
GRANT EXECUTE ON master..sp_OASetProperty to [??]
GRANT EXECUTE ON master..sp_OAMethod to [??]
GRANT EXECUTE ON master..sp_OADestroy to [??]

[??] must be a user/role with access to master.

Jim
  • 864
  • 1
  • 8
  • 16
9

The Ole Automation Procedures option is also configurable through the SSMS by right-clicking the server instance of interest, select Facets to open the View Facets dialog. On the General page, in the Facet drop-down list, select "Surface Area Configuration" . OleAutomationEnabled is an option in the list of Facet properties displayed. True = On. (Depending on what you're doing, you may also need the XPCmdShellEnabled setting enabled.) As others have written, I'm not suggesting that one should enable this setting, just how to do it through the interface.

FreeText
  • 339
  • 3
  • 7