SQL Agent Permissions

Problem

I was contacted by a user asking if it was possible for his team to be able to execute specific SQL Agent jobs and check the job history. This was made possible by creating a SQL authenticated login and changing the job owner to this new account.

But this got me thinking about who actually has permissions when executing SQL Agent jobs. For example, who actually needs the SELECT\INSERT etc. permissions when a Powershell script is run using SQL Agent under a proxy account? Does the Job Owner or the RunAs account need access to the package for a SSIS job? I was surprised by the results and the security hole that can be opened (which I will reveal at the end).

The test involved running an INSERT statement into a table using a TSQL, Powershell and SSIS job step (as these are pretty much the only steps I use). The code to create the table is...

CREATE DATABASE [JobOwner_Perms_Test]
GO

CREATE TABLE [dbo].[JobOwner_Perms_Test] (
[Col1] int NOT NULL,
[Col2] varchar(max) NOT NULL,
[LoginName] varchar(max) NULL DEFAULT (suser_sname()),
[User_Name] varchar(max) NULL DEFAULT (user_name()))

TSQL Job Step

I created two jobs, the first with the job owner as a sysadmin and the second with the job owner as a non-sysadmin but with permissions to INSERT into the table.

Both jobs executed successfully, with the sysadmin account job using the credentials of the SQL Agent service and the non-sysadmin account using job owner credentials.

I was surprised that the RunAs option, which allows a proxy account to be used, wasn't accessible for the TSQL job step. I was further surprised that there was a capability for sysadmin users to declare the job step to be able to be run under another users context which is found under the Run As User on the Advanced tab.

Setting this to a valid user allows that account to be used as a proxy for the sysadmin account (where col1 = 3)(although it doesn't have any affect when set for a non-sysadmin account). Not sure why the RunAs option in the general tab doesn't allow this...

POWERSHELL job step

For the Powershell test, I used the INVOKE-SQLCMD cmdlet to run the INSERT statement.

Windows
INVOKE-SQLCMD -ServerInstance localhost -Database JobOwner_Perms_Test -Query "INSERT INTO [dbo].[JobOwner_Perms_Test](Col1, Col2) VALUES (n, 'XXXX')"

SQL
INVOKE-SQLCMD -ServerInstance localhost -Database JobOwner_Perms_Test -Username -Password -Query "INSERT INTO [dbo].[JobOwner_Perms_Test](Col1, Col2) VALUES (n, 'XXXX')"

There were quite a few tests to consider. For example, whether the job owner is a sysadmin or not, whether a proxy account is used and if the Powershell string specifies whether to use Windows or SQL authentication.

The first bunch of tests used SQL Server Agent Service as the RunAs value...

With the following results....

First off, all the jobs with a job owner of non-sysadmin failed with the error explaining that non-sysadmin accounts need to use a Proxy account to run.

The results show that using the INVOKE-SQLCD cmdlet with windows authentication will use the SQL Agent service account to perform DML actions while the account specified using the cmdlets -Username and -Password parameters will be used for SQL authentication.

Next up was running the cmdlet using proxy accounts, one that was a sysadmin and another that had minimal permissions on the object.

The outcome (see screenshot below) shows that using windows authentication within the cmdlet will use the proxy account for the DML. Using SQL authentication in the cmdlet uses the accounts specified under the -Username and -Password parameters. One surprising result of these tests was that a Powershell script can be executed in SQL Agent without either the Job Owner or Run As accounts having access to msdb or the target database. All that's needed is the SQL Authenticated user to have access to the object being executed.

What errors can be received? If the Proxy account doesn’t have access to the database or doesn’t have permissions to insert into the table, the following errors will be returned...

The INSERT permission was denied on the object 'JobOwner_Perms_Test', database 'JobOwner_Perms_Test', schema 'dbo'

The error information returned by PowerShell is: 'Cannot open database "JobOwner_Perms_Test" requested by the login. The login failed. Login failed for user '\ProxyNoSysadminLogin'

The same goes when using SQL authentication and the SQL accounts don't have access\permissions.

SSISDB Job Step

SSIS jobs running in SQL Agent add further complications when it comes to the permissions. Aswell as having the Job Owner and RunAs account, there's an option for “Log on to the server” which gives the choice of Windows or SQL authentication but as far as I can tell, only allows Windows.

If the package is stored in the SSISDB catalogue, then permissions are required on the SSISDB database. Finally, the package itself may be connecting to a database using a connection manager within the package.

To test, I created two packages that both ran an execute SQL task, which executed a stored procedure to INSERT a row into a table. The first package used Windows Authentication within it to connect to the database. In the second package, I created two parameters that are passed into the package to be used in an expression to build a connection string that uses SQL Authentication to connect to the database.

After A LOT of testing, with many different scenarios, I made the following observations...

• The job owner takes no part in setting the permissions. 
• If the job owner is not a sysadmin, a proxy account is needed for the SSIS job step. 
• The account in the Run As option within the job step is the one that executes the package and needs permissions in the SSISDB catalogue to do this. 
• When the package uses a connection with Windows Authentication, the account in the Run As option is used and needs permissions for whatever that step does. 
• When the package uses a connection with SQL Authentication, the account specified within the connection manager in the package needs permissions for whatever that step does.

Security Hole

The reader may've already spotted the problems that can arise with running SQL statements in SQL Agent. A combination of over privileged accounts coupled with a SQL Agent jobs can be give some nasty results.

Many places I have worked at have set all SQL Agent jobs to be owned by SA and use the SQL Agent service account as the Run As option in the job step. Taking the TSQL job step as an example, lets run through the following scenario....

A super user called Steve Bull requests for a stored procedure he has created in his own schema in a user database to be executed every night, which adds a row into a table. The table contains a datetime column and a second, DEFAULT, column that adds the users LOGIN name.

The DBA creates the job, setting the owner as SA, and informs Steve that it will be executed every night from tonight.

This works fine. Steve checks the table and can see that the user executing the SQL Agent job is the a the SQL Agent service account, presumably with sysadmin privileges.

Steve then goes and alters the stored procedure and waits for the nightly SQL Agent job to run.

Which leaves Steve as a sysadmin on the instance.

Conclusion

Setting the correct permissions within SQL Agent is essential when creating jobs. Using highly privileged accounts to execute jobs can be exploited, leading to the possibility of a user being able to elevate their permissions and being able to access data that they shouldn't be able to see.

Linked Server Security

Problem

When creating Linked Servers, security often gets overlooked.  This can lead to accounts on one instance having access, sometimes with elevated permissions, to a second instance.

Security Options

Linked Servers offer the following security options....

• Local server to remote server mappings
• Not be made
• Be made without using a security context
• Be made using the logins current security context
• Be made using this security context

Lets take a look at each one...

Local server to remote server mappings

This setting takes precedence over the other Linked Server security settings and gives the ability to map specific logins on the local instance to logins on the remote instance.

The login on the local instance can be either SQL or Windows authenticated but unfortunately, Windows Groups cant be used. The local login can be mapped to logins on the remote instance, where the permissions of the remote login would then be used.

Accounts can impersonate the same account on the remote server. For SQL logins, the username and password need to be exact matches on both instances. For Windows logins, a Service Principal Name (SPN) needs to be created on both instances. As this is done in Active Directory and very often not the responsibility of the DBA, this can be missing which can cause errors. I will go into more detail about SPNs later.

All other security settings are used for any accounts that are NOT specified in the local to remote server mappings.

Not be made

This setting denies access to the remote server for all logins except the ones specified in the local server to remote server mappings.

 

Be made without using a security context

Books online has the very unhelpful description for this setting...

Be made without using a security context
Specify that a connection will be made without using a security context for logins not defined in the list.

My impression was that accounts would connect and use the privileges assigned to the public server role. Unfortunately, I have never been able to test this out as I always get the following error when trying to create the Linked Server with the setting...

If anyone knows how to make this setting work, please get in touch :-)

Be made using the login's current security context

This setting uses the same login connected to the local instance to connect to the remote instance. When using a SQL authenticated user, the username and password need to be the same on both instances. For Windows authenticated users, they can have direct access or be part of an AD group on the remote instance.

This setting gives the user access on the local instance to everything they have access to on the remote instance. If a user should only be accessing, from the local instance, a subset of the database\objects they have access to on the remote instance, then this may not be the correct setting to use.

Be made using this security context

This setting uses a SQL authenticated login on the remote instance for all connections using the Linked Server. A remote login and password need to specified within the Linked Server security options. Its not possible to use this setting with a Windows login or AD group.

I've seen people use the SA account for this setting, probably to overcome SPN issues with one of the other settings or the complexity involved with granting multiple accounts access to specific databases\objects on the remote instance. Making the remote login SA (or any login with sysadmin priveleges) will effectively allow the local login SA privileges on the remote instance.

an example...

Set the “Be made using this security context” to an account with sysadmin permissions. Then create an account on the remote instance with no permissions. I have used a login called LinkerServerTest.

USE master
GO
CREATE LOGIN LinkedServerTest WITH PASSWORD = N'LinkedServerTest',
DEFAULT_DATABASE = [master],
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF
GO

Execute the following stored procedure on the local instance...

EXEC [INSTANCE_B_DATABASE_1].MASTER.dbo.sp_addsrvrolemember 'LinkedServerTest' , 'sysadmin'

Now check the permissions that LinkerServerTest has...

(For this to work, the rpc out in the server options page needs to be set to true)

Service Principal Name (SPN)

SPNs are vital when using Linked Servers with Windows Authentication. The following error can be received when using a Linked Server, which normally means an SPN hasnt been created...

MSDN describes SPNs as...

A service principal name (SPN) is a unique identifier of a service instance. SPNs are used by Kerberos authentication to associate a service instance with a service logon account. This allows a client application to request that the service authenticate an account even if the client does not have the account name.

There are many articles on the internet that can explain SPNs and Kerberos authenication and the “double hop” problem much better than I can (this one is pretty good https://shuggill.wordpress.com/2015/03/06/configuring-sql-server-kerberos-for-double-hop-authentication/). The way I like to think about this problem is...

If you are using Window authentication on a laptop\desktop\application computer to connect to a SQL instance on another computer (one hop) which then uses a Linked Server to connect to an instance on another computer (second hop), you will need to create an SPN for the connection to work.

The above scenario is probably how most people are connecting and without SPNs, Windows accounts will not be able to gain access to the remote instance through the Linked Server.

I use the following two methods to find out if an SPN has been created.

1 – Check the SQL error log.

You'll either get good news...

or bad news...

2 - Microsoft have produced the Kerberos Configuration Manager tool to help with the detection and creation of SPNs. The tool can be downloaded from...

https://www.microsoft.com/en-us/download/details.aspx?id=39046

Creating SPNs goes above what I'd like to talk about here, so instead use this link to show you how...

https://msdn.microsoft.com/en-us/library/ms191153.aspx

Server Options

There are a couple of settings within the security options that can alter the way Linked Servers work. Both settings can be set to either true or false.

• Data Access – When set to false, this disables the Linked Servers ability to be used to retrieve data in a 4 part naming convention or using OPENQUERY SELECT statement.

• RPC out - When set to false, this disables the Linked Servers ability to be used to execute stored procedures.

DMVs

The following two DMVs give you information regarding the Linked Servers and login mappings on the instance.

• sys.servers - This gives the information on the Linked Servers that have been created on the instance. The column is_linked will show a 1 for any linked servers that have been created.

• sys.linked_logins – This gives the information on the local logins and the mappings they have on the remote instance

How should a Linked Servers security be set?

Like pretty much everything else in SQL Server, there isn't a one size fits all and you have to tailor the options to your needs. For me, I will always try to set up a Linked Server per database and explicitly set the each user to map to a specifically created SQL authenticated user that only has access to a single database with the correct permissions. Any login not specified in the local server to remote server mappings, wont be able to use the Linked Server. This way, only the logins which need access will be able to access the remote server and also SPN problems can be bypassed. Obviously, the downside to this method is that there can be many Linked Servers created with many users which can make the management burdensome.

Multiple Linked Servers for 6 databases on 2 instances.

Specific accounts mapped to SQL authenticated users.

Summary

After creating a Linked Server, time should be spent to work out what logins need to access it and what permissions will be required on the remote instance. Taking short cuts with Linked Server security can make an instance accessible to many more people than originally intended.

Using Logins and Certificates to evelate Permissions

Problem

A common question I get asked by SQL developers is to be able to query the sys.dm_db_index_usage_stats table to see what indexes are being used. As happy as I am that SQL developers are taking an interest in their deployments, this requires the VIEW SERVER STATE permission which opens up a lot of DMVs and can be a potential security concern. This scenario is ideal for creating a login mapped to a certificate which will allow the SQL developer access to the DMV without directly granting elevated permissions.

Solution

The following steps outline how to create a stored procedure that can be executed by users to return index usage stats results for the database they are accessing.

Create a Stored Procedure

The Stored Procedure (sp) uses the db_id() function to only return results from the current database and joins with sys.indexes to make the results more readable. Creating the sp in master with a name that starts with sp_ allows the stored procedure to be called from any database.

IF EXISTS(SELECT 1 FROM sys.procedures WHERE name = 'sp_IndexUsageStats_PerDatabase')

BEGIN

DROP PROCEDURE sp_IndexUsageStats_PerDatabase

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC sp_IndexUsageStats_PerDatabase

AS

BEGIN

SELECT db_name(database_id) as DatbaseName, object_name(ius.object_id) as   ObjectName,

        i.name, user_seeks, user_scans, user_lookups, user_updates,

        last_user_seek, last_user_scan, last_user_lookup, last_user_update

        FROM sys.dm_db_index_usage_stats ius

        INNER JOIN sys.indexes i

        ON i.index_id = ius.index_id AND i.object_id = ius.object_id

        where database_id = db_id()

END

If a user with limited privileges (for example, a user called iain who is part of the db_owner role in AdventureWorks2014) tries to execute the SP, the error that “The EXECUTE permission was denied on the object” will be returned.

This can be easily rectified by granting the execute permission on the SP to public...

USE master
GO
GRANT EXEC ON sp_IndexUsageStats_PerDatabase TO public
GO

which now changes the error message to...

Create a Certificate 

The certificate needs to be created in the master database.

USE master
GO
CREATE CERTIFICATE CertificateElevatePerms
ENCRYPTION BY PASSWORD = 'Walt3rZ3nga'
WITH SUBJECT = 'General cert to be used for processes that need a higher perms';
GO

Add a Signature

The signature ties the stored procedure to the certificate. Both need to be created before the signature can be set and if the stored procedure is altered, the signature needs to be reset. The password needs to be the same as the one used to create the certificate.

ADD SIGNATURE TO OBJECT ::sp_IndexUsageStats_PerDatabase
BY CERTIFICATE CertificateElevatePerms
WITH PASSWORD = 'Walt3rZ3nga' ;

Create a Login

A login which uses the certificate needs to be created next.

Books online describes a login mapped to a certificate as...

Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server. You can create a login from a certificate or asymmetric key only when the certificate or asymmetric key already exists in master.

USE master
GO
CREATE LOGIN CertificateElevatePerms_Login
FROM CERTIFICATE
GO

Grant the necessary permissions to the Login to be able to execute the SP

USE master
GO
GRANT VIEW SERVER STATE TO CertificateElevatePerms_Login ;
GO
GRANT AUTHENTICATE SERVER TO CertificateElevatePerms_Login ;
GO

Results

Running the same procedure as earlier now returns some results. Here the user has db_owner access to the AdventureWorks2014 database.

If a user only has access to specific objects rather than db_owner role (for example, just the JobCandidate table) then only the metadata for this object will be displayed.

USE [AdventureWorks2014]
GO
ALTER ROLE [db_owner] DROP MEMBER [iain]
GO
GRANT SELECT ON [HumanResources].[JobCandidate] TO [iain]
GO