Configuring SQL Server Agent proxy account

Question

Hi,

today it was necessary to create a proxy account to run an SSIS package.

It was working, after removing the SSIS package and deloying the SSIS package again it works manually, but when we try to schedule the SQL Agent JOB it fails. After configuring the proxy account it worked successfully.

PS: Integration services is on server B and the database that the SSIS package works on is on server A. That is, the credentials I use within the connection string are from server A.

I honestly don't understand why I need to enter a username and password in the string configuration, if my connection string has an option of (Integrated Security=SSPI). ]:(

Error:
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'domain\hostname$'.".

String connection:

Data Source=hostname.domain;Initial Catalog=DB_Samples;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

We have other SSIS packages that are using SQL Agent without proxy, using the same project settings (EncryptSensitiveWithUserKey).

In the procedures that we found says to create the proxy account mapped with the user (SA), however we were concerned about the security risk that this can generate for the environment.

Doubts:

1 - Why do we need to use the (SA) account?

Ref.: https://www.youtube.com/watch?v=Z--t2MUF5E4

2 - Can you do the mapping using an unprivileged account?

3 - If privileges are needed, which ones are needed?

Thanks.

Answer 1

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'domain\hostname$'.".

This happens because SQL Server Agent runs under an account local to the machine, for instance NT Service\SQLSERVERAGENT, and you are using integrated security. Since that account does not exist on the other machine, the login will be for the machine account.. (The one with $ at the end.) Normally, you don't create logins for machine accounts.

When you set up proxy accounts with Agent, that is a typically a Windows domain account, so that Agent spawns a subprocess with that account, logging on to Windows with the stored credentials. Because this is a true Windows login, that process can now successfully log in with Integrated Security. I describe the steps to do this here: https://www.sommarskog.se/perm-hijack.html#agentjobs.

Now, you are talking about a proxy using SA, which is an SQL login, so I am not sure we are talking about the same thing. I will need to add the caveat that I don't know SSIS, so if this is a proxy specific to SSIS, there maybe something I have missed.

Nevertheless, I like to answer your doubts:

1 - Why do we need to use the (SA) account?

You would need to use SA, only if the package needs to perform actions that requires membership in the sysadmin role.

I did not watch the video, as it was a tad long for me. But I sampled a few seconds some minutes into the presentation, and I noticed that the presenter talked about using sa. Maybe he used sa as an example to keep things simple? (Not a very good idea in my opinion.)

2 - Can you do the mapping using an unprivileged account?

Of course.

3 - If privileges are needed, which ones are needed?

That depends on what the package is doing. Access to the database in question seems obvious. SELECT, INSERT etc permissions on tables may. Or only permissions to run stored procedures.

Answer 2

I need to start with repeating that I don't know SSIS, so I may be saying things that are incorrect with regards to SSIS. If so, I hope that someone will correct me.

I'm working from the assumption that there is not much difference between running an SSIS package that accesses a remote instance, than running a BAT file that uses SQLCMD to run something on a remote computer. And the latter is something I've done a couple of times. (However, in this scenario there is nothing similar to EncryptSensitiveWithUserKey, so that is a part that I will have to skip.)

Let's say that the .BAT file reads:

SQLCMD -S OTHERSERVER -d somedb -E -i"somesqlscript.sql" -I -b

(Remember that -E stands for integrated security. -I forces QUOTED_IDENTIFIER, and -b is needed so that a failure in the SQL script also signals a failure in the job.)

If you run this as a plain CmdExec job from Agent with no proxy, the connect will be with the service account for SQL Server Agent. If this is a domain account and this account has access to OTHERSERVER connection will succeed. If SQL Server Agent runs under a local machine account, such as NT Service\SQLSERVERAGENT (which often is the case), the access will be made through the domain account for the computer, that is domain\hostname$ as you had in the error message. You can add domain\hostname$ as a login on OTHERSERVER, but as you may guess, it is not good practice security-wise.

One option to address this is to change authentication to SQL authentication:

SQLCMD -S OTHERSERVER -d somedb -U somelogin -P pwd -i"somesqlscript.sql" -I -b

The obvious drawback here is that username and password are stored in cleartext in the .BAT file. This can possibly be handled better with that encrypt option in SSIS, but I still don't really like it. But to some extent that is a matter of taste, and it may be easier for you to create an SQL login than to ask the AD admin to create account to be used for a proxy.

And that is the other option: you ask the AD admin to create Windows login, and then you create a credential that maps to this Windows login and password. And then you create a proxy in Agent that uses this credential, and you set up the job to run as the proxy. (Please see https://www.sommarskog.se/perm-hijack.html#agentjobs for details.)

That Windows login will of course need to have access to OTHERSERVER and somedb. But here is the cool thing: there may already be AD group created for users with access to the database. Then you ask the AD admin to add the Windows user to that AD group, and you are all set. At least as long as the SSIS package does not require elevated permissions.

My preference is certainly for using a proxy with a Windows login, but I cannot deny that there is an element of what works best at your local site.