Skip to content
+44 (0) 330 99 88 700
Sign In Register
Ready to Talk?

Unable to run an SQL Server trigger: SQL Server blocked access to procedure ‘sys.sp_OACreate’ of component OLE Automation Procedures

Table of Contents

Symptom #

Unable to run an SQL Server trigger, the following error message is received:

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.

Cause #

OLE Automation is disabled for the specific SQL Server instance containing the database table that is being modified.

The TaskCentre configurator will display a red cross against the SQL Server OLE Automation pane located: Event Agents -> Microsoft SQL Server Trigger Agent -> Configuration -> Prerequisites

Capture 1(1)

Resolution #

Enable OLE Automation.

It is recommended to enable this setting via the TaskCentre configurator with the aim being to obtain a green tick against the setting once the operation is successful.

TaskCentre Configurator #

  1. Under SQL Server OLE Automation click Modify. This will open the OLE Automation Settings.
    3
  2. Use the drop down menu to select the required SQL Server instance.
  3. Select Enable OLE Automation followed by OK.Once complete a green tick will appear in the pane indicating that the setting is now active.
    Capture2

Below outlines the procedure for setting OLE Automation via SQL Server Management Studio.

SQL SERVER 2005 (see below for 2008/2010/2012/2014) #

  1. Start -> All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration. Within the “SQL Server Surface Area Configuration” tool select “Surface Area Configuration for Features”.
  2. Tick the “OLE Automation” property checkbox.
    Capture4

SQL SERVER 2008/2010/2012/2014 #

  1. From within SQL Server Management Studio right-click the server node in the object explorer and choose Facets from the context menu.
  2. Within the View Facets window select “Surface Area Configuration” from the Facet drop down list.
  3. In the list of properties for this facet set “OleAutomationEnabled” to “True”.
    Capture5
Was this article useful?