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.
Version : 6.x
Component : Microsoft SQL Server
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:
To grant the ICA service access to the proxy, follow this procedure:
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
Confirm the proxy is enabled by following this procedure:
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
If the proxy is not enabled (Enabled = 0), follow this procedure:
USE msdb;
GO
EXEC dbo.sp_update_proxy @proxy_name = N'Bay Dynamics AD Connector Proxy', @enabled = 1;
GO
If the Bay Dynamics AD Connector Proxy is not configured to use the Bay Dynamics AD Connector Credential, follow this procedure:
USE msdb;
GO
EXEC sp_update_proxy @proxy_name = N'Bay Dynamics AD Connector Proxy', @credential_name = N'Bay Dynamics AD Connector Credential';
GO
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: