When configuring the Microsoft SQL Server Trigger Agent, OLE Automation is not detected

 Symptom

Unable to configure the Microsoft SQL Server Trigger Agent from the TaskCentre Configurator tool as it does not detect the OLE Automation flag from the database.

The Configurator log shows the following error:

TCConfigurator.AddIns.SQLTriggerAddIn.SQLAgentHelper::IsOLEAutomationEnabled: System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing aconnection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 – Could not open a connection to SQL Server).

Note: To enable configurator logging set LogLevel to 4 and LogType to 6 under the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Orbis Software\TaskCentre\Logging. Having set these keys the Configurator log file is stored in the TaskCentre temp folder. Default is C:\.

Cause

The SQL Server instance is not configured to allow connections using Windows Authentication over shared memory.

Background

The Configurator uses Windows Authentication over shared memory to connect to SQL Server instances.

Resolution

Configure the SQL Server instance to allow connections using Windows Authentication over shared memory. You may wish to refer this action to your Database Administrator (DBA) or alternatively refer to the relevant MSDN articles.

In principle you need to complete the following:

  • Enable Shared memory as an SQL server network protocol.
  • Configure SQL Server to use Windows authentication.
  • Create a Windows authenticated account that can log on to the SQL Server and also has rights to run the TaskCentre Configurator.