ICA service account is unable to use the CmdExec subsystem

Products

Information Centric Analytics

Issue/Introduction

When running the Information Centric Analytics (ICA) job Bay Dynamics AD Connector Job to import user, group, and computer information from Active Directory (AD) into the ActiveDirectoryDW database, the job fails and the following error is captured in the SQL Server Agent job history log:

Unable to start execution of step 1 (reason: JobOwner <domain>\<account> doesn't have permissions to use proxy 5 for subsystem CmdExec). The step failed.

Environment

Version : 6.x

Component : Microsoft SQL Server

Cause

The ICA service account under which the Bay Dynamics AD Connector credential runs has not been granted permissions to access the proxy, or the proxy has been disabled.

Note that the ICA service account needs to be granted the Windows security user right Logon as batch job and be assigned the sysadmin role in Microsoft SQL Server. If your local security policies prohibit the use of the sysadmin role, follow the procedure provided in the Information Centric Analytics Administrator Guide to configure the account to operate without the sysadmin role:

https://techdocs.broadcom.com/us/en/symantec-security-software/information-security/information-centric-analytics/6-6/Administrator-Guide/install_privileges/ssms_permissions.html

Resolution

To grant the ICA service access to the proxy, follow this procedure:

Open SQL Server Management Studio (SSMS)
Connect to the Database Engine hosting the RiskFabric database
Click the New Query button in the SQL Standard toolbar, or select File > New > Query with Current Connection
Copy the following statement and paste it in the new query editor window, replacing [<domain>\]<ica_service_account> with the ICA service account name:
```
USE msdb;
GO

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'Bay Dynamics AD Connector Proxy', @login_name=N'[<domain>\]<ica_service_account>';
GO
```
Execute the query by pressing the F5 key, selecting Execute from the SQL Editor toolbar, or selecting Execute from the Query menu

Confirm the proxy is enabled by following this procedure:

In SSMS, click the New Query button in the SQL Standard toolbar, or select File > New > Query with Current Connection

Copy the following statement and paste it in the new query editor window:

USE msdb;
GO

SELECT     p.[name] AS "Proxy_Name",
           p.[enabled] AS "Enabled",
           c.[name] AS "Credential_Name",
           c.credential_identity AS "Credential_Identity"
FROM       dbo.sysproxies AS p
INNER JOIN sys.credentials AS c
        ON p.credential_id = c.credential_id
WHERE      p.[name] = 'Bay Dynamics AD Connector Proxy';
GO

Execute the query by pressing the F5 key, selecting Execute from the SQL Editor toolbar, or selecting Execute from the Query menu

If the proxy is not enabled (Enabled = 0), follow this procedure:

In SSMS, click the New Query button in the SQL Standard toolbar, or select File > New > Query with Current Connection

Copy the following statement and paste it in the new query editor window:

USE msdb;
GO

EXEC dbo.sp_update_proxy @proxy_name = N'Bay Dynamics AD Connector Proxy', @enabled = 1;
GO

Execute the query by pressing the F5 key, selecting Execute from the SQL Editor toolbar, or selecting Execute from the Query menu

If the Bay Dynamics AD Connector Proxy is not configured to use the Bay Dynamics AD Connector Credential, follow this procedure:

In SSMS, click the New Query button in the SQL Standard toolbar, or select File > New > Query with Current Connection

Copy the following statement and paste it in the new query editor window:

USE msdb;
GO

EXEC sp_update_proxy @proxy_name = N'Bay Dynamics AD Connector Proxy', @credential_name = N'Bay Dynamics AD Connector Credential';
GO

Execute the query by pressing the F5 key, selecting Execute from the SQL Editor toolbar, or selecting Execute from the Query menu

Under certain rare circumstances, the following error may be returned when modifying the proxy:

Alter failed for 'Bay Dynamics AD Connector Proxy'. (Microsoft.SqlServer.Smo)

Additional Information:

    Object reference not set to an instance of an object. (Microsoft.SqlServer.Smo)

To resolve this error, follow this procedure:

In SSMS, navigate in Object Explorer to SQL Server Agent > Proxies > Operating System (CmdExec)
Right-click the Bay Dynamics AD Connector Proxy and select Delete
The Delete Object window will appear
Check the box labelled Reassign to: and select SQL Server Agent Service
Click the OK button
The Delete Object window will close
Right-click the folder SQL Server Agent > Proxies > Operating System (CmdExec) and select New Proxy...
The New Proxy Account window will open
Enter the following values on the General page:
Proxy name: Bay Dynamics AD Connector Proxy
Credential name: Bay Dynamics AD Connector Credential
Click the OK button to save the proxy and close the New Proxy Account window
In Object Explorer, navigate to SQL Server Agent > Jobs
Right-click the Bay Dynamics AD Connector Job and select Properties
The Job Properties - Bay Dynamics AD Connector Job window will open
Select the Steps page
Click the Edit button
The Job Step Properties - Data Import Step window will open
On the General page, select Bay Dynamics AD Connector Proxy from the Run as: dropdown menu
Click the OK button to save the change and close the Job Step Properties - Data Import Step window
Click the OK button to save changes and close the Job Properties - Bay Dynamics AD Connector Job window