As a SQL Server user, you may have encountered situations where you needed to execute specific SQL Agent jobs and check their job history. In such cases, it is important to understand the permissions required for executing these jobs and the potential security risks involved.
Let’s explore the permissions required for different types of SQL Agent job steps and the implications they have on security.
T-SQL Job Step
When creating a T-SQL job step, you have the option to specify the job owner as either a sysadmin or a non-sysadmin with specific permissions. Surprisingly, both types of job owners can successfully execute the job. The sysadmin account uses the credentials of the SQL Agent service, while the non-sysadmin account uses its own credentials.
Additionally, there is an option for sysadmin users to declare the job step to be run under another user’s context. This allows the sysadmin account to act as a proxy for the specified user. However, this option does not have any effect when set for a non-sysadmin account.
Powershell Job Step
For Powershell job steps, there are several tests to consider. These include whether the job owner is a sysadmin, whether a proxy account is used, and whether the Powershell script specifies Windows or SQL authentication.
When using the INVOKE-SQLCMD cmdlet with Windows authentication, the SQL Agent service account is used to perform DML actions. On the other hand, when using SQL authentication, the account specified in the cmdlet’s -Username and -Password parameters is used.
Surprisingly, a Powershell script can be executed in SQL Agent without either the job owner or the Run As account having access to the msdb or the target database. All that is needed is for the SQL authenticated user to have access to the object being executed.
SSISDB Job Step
SSIS jobs running in SQL Agent introduce further complications when it comes to permissions. In addition to the job owner and the Run As account, there is an option for “Log on to the server” which only allows Windows authentication.
If the package is stored in the SSISDB catalog, permissions are required on the SSISDB database. Furthermore, the package itself may connect to a database using a connection manager within the package.
After extensive testing, the following observations were made:
- The job owner does not play a role in setting the permissions.
- If the job owner is not a sysadmin, a proxy account is needed for the SSIS job step.
- The account specified in the Run As option within the job step is the one that executes the package and needs permissions in the SSISDB catalog.
- When the package uses a connection with Windows authentication, the account specified in the Run As option is used and needs permissions for the actions performed by that step.
- When the package uses a connection with SQL authentication, the account specified within the connection manager in the package needs permissions for the actions performed by that step.
Security Hole
It is important to be aware of the potential security risks associated with running SQL statements in SQL Agent. If overprivileged accounts are used, coupled with SQL Agent jobs, it can lead to unintended consequences.
For example, if all SQL Agent jobs are owned by the SA account and use the SQL Agent service account as the Run As option, a user could exploit this setup to elevate their permissions and gain access to data they shouldn’t be able to see.
Conclusion
When creating SQL Agent jobs, it is crucial to set the correct permissions to ensure the security of your database. Using highly privileged accounts to execute jobs can be exploited, potentially leading to unauthorized access to sensitive data.
By understanding the permissions required for different job steps and being mindful of potential security risks, you can ensure the integrity and confidentiality of your SQL Server environment.