SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'

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

4 Answers

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 

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; 

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.

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.