SSIS Proxy Credential Ignored

Saransvan 106

If I run a SSIS package through a SQL Agent job using a runas SSIS proxy account the package doesn't work - fails with a required connection (which it has permissions to).

However, if I run SQL Agent using the same account as the proxy and choose SQL Agent runas in the SSIS job step, it all works.

Package works if run manually from the same account as well.

It just doesn't seem to be picking up the proxy credentials, can anyone suggest why please?

SQL version is 2016 Microsoft SQL Server 2016 (SP2-CU14)

Thanks.

Jeffrey Williams 1,891 Reputation points

2021-02-15T22:41:53.69+00:00

What isn't working - is it a specific task in the package?
Saransvan 106 Reputation points

2021-02-16T08:37:35.95+00:00

No, the package is not using the credentials. I can tell as I'm getting permission issues when it's run through a SQL Agent job.
Jeffrey Williams 1,891 Reputation points

2021-02-24T16:59:59.887+00:00

To repeat - what task is failing in your package? Is it just the connection - and if so - what are the details of that connection and the actual error you are getting? In some cases - making a connection using a different (proxy) account requires additional setup for that account.
Saransvan 106 Reputation points

2021-02-24T17:18:56.913+00:00

I don't get an error specifically, it just hangs. But then I see a connection error in the event log (am connecting to another DB system) . To re-iterate, if I run SQL Agent with the account that I was trying to use as a proxy, everything works, so it's not anything to do with the permissions on the proxy account as I've proved that. If I try to run with the proxy then it's still trying to use the normal SQL Agent account which doesn't have access, and that's what doesn't work. It just doesn't appear to be picking up the proxy.

The only thing I can think of that might be part of the issue, is that SSISDB has been restored from another server as part of a package migration. All the proper steps were followed re keys etc, and the packages all run fine - except through SQLAgent with a proxy.
Jeffrey Williams 1,891 Reputation points

2021-02-24T17:26:46.673+00:00

SQL Server Agent jobs run in the context of the owner of the job - when the owner of the job is not a sysadmin. If the owner of the job is a sysadmin - then the job runs in the context of the service account running the agent service.

It seems you have a break somewhere in permissions - probably the agent service account does not have the required permissions at the OS level to launch or access the connection information for that database system.
Saransvan 106 Reputation points

2021-02-25T08:37:55.077+00:00

Yes I know this. I have tried different owners of the job (with/without sa), all to no avail. The Agent service account is fine.
In the end, as I couldn't resolve this, I have had to give the agent account permissions to the external source/dest and given up on using the proxy.

2 answers

Monalv-MSFT 5,896 Reputation points

2021-02-05T02:55:52.54+00:00

Hi @Saransvan ,

May I know if you create credential firstly?
If no, please create it. We can refer to the following links and pictures:

1.Create a Credential
Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server.

2.Create a SQL Server Agent Proxy
A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential.

3.How to: Configure an SSIS Package to Execute as a Proxy Account

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
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.
Please sign in to rate this answer.
Monalv-MSFT 5,896 Reputation points

2021-02-24T03:11:53.71+00:00

Hi @Saransvan ,

May I know if you choose SQL Server Integration Services Package when creating new proxy?

Best Regards,
Mona

Saransvan 106 Reputation points

2021-02-24T08:45:26.733+00:00

Yes of course.
Sign in to comment
Saransvan 106 Reputation points

2021-02-05T08:55:24.717+00:00

Obviously yes I have created a credential, as per the title of the question.
Please sign in to rate this answer.
Monalv-MSFT 5,896 Reputation points

2021-02-12T10:03:31.11+00:00

Hi @Saransvan ,

It just doesn't seem to be picking up the proxy credentials, can anyone suggest why please?

May I know if you choose the credential when editing the sql proxy?

Best Regards,
Mona

Saransvan 106 Reputation points

2021-02-12T10:11:49.217+00:00

Yes of course.
Sign in to comment

Share via

SSIS Proxy Credential Ignored

2 answers