question

IE-0764 avatar image
0 Votes"
IE-0764 asked IE-0764 commented

SSIS Proxy and Credential Manager

I’m using a Proxy for SSIS executions in SQL Server Agent with a stored credential (domain user, “Mydomain\SSISExecutor”). The proxy works fine in SQL Server Agent, when authenticating to SQL servers, fileshares etc.

I’ve added an Azure File Share credential to the “Mydomain\SSISExecutor”s credential manager using cmdkey, but jobsteps configured to use the SSIS Proxy running packages that tries to access the Azure File Share fails with “The username or password is incorrect”.

Adding the same credential, using cmdkey, to the domain user that is running the SQL Server Agent service, “Mydomain\SQLSrvAgentRunner” – and then executing the job step with SQL Server Agent – the package succeeds in accessing the Azure File Share.

It seems like the Proxy is not reading the credential manager when executing the job step, but the SQL Server Agent does.

How can we set this up so that the Proxy can use the stored credentials in the Credential Manager when executing SQL Agent Job.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered IE-0764 commented

Hi @IE-0764 ,

SQL Server Agent uses Proxies to define the security context for job steps. Basically, a proxy is an object that provides SQL Server Agent access to stored credentials for a Windows user. When running a job step that is configured to use a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step using that security context.

Check create-a-sql-server-agent-proxy about creating SQL Server Agent Proxy with credential.

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, this is how it is already configured. But, when executing the job step using the proxy, it is not picking up stored credentials in the credential manager for that (underlying) account, to be able to authenticate to an Azure File Share - authentication fails. When running as SQL Server Agent, the stored credentials in the credential manager (for the service account running service) is used, and is able to authenticate to the Azure File Share. Which is the problem.

0 Votes 0 ·