Lesson Learnt while Configuring Security on a Job Running through Linked Server

This case taught me a very useful lesson about how impersonation works when executing a SQL Server Job using a linked server. If this scenario is new for you too, I hope you enjoy learning as much as I did.

In this case my customer was trying to run a stored procedure using a linked server from a local SQL Server 2005 instance to a remote SQL Server 2000. The stored procedure ran just fine when executed from SQL Server Management Studio (SSMS) but failed when ran as a SQL Server Agent Job.

These kind of mistakes are common in SQL Server when the SQL Server Agent account does not have enough permissions to execute the required Job. For example, when running a T-SQL statement that performs some kind of action on a remote server’s share, the credentials used to logon on the remote share are the ones of the user that opens SSMS; in other words, the user we have used to logon in our Windows session. However, when the statement is executed within a SQL Server Job, the credentials being used are the ones for the SQL Server Agent account, which is the account in charge of running jobs. This can be found on the following link:

[…] if you create a job that requires a drive letter or a universal naming convention (UNC) path, the job may run under your Microsoft Windows NT® 4.0 or Windows® 2000 user account while testing it. When SQL Server Agent then runs the job, it may not have the necessary permissions, drive letter configurations, or access to the required drive to execute the job.

There is additional information that needs to be taken into account when running a statement in SQL Server Management Studio vs. running the T-SQL code within a SQL Server Agent Job, this is described in Knowledge Base article KB269074:

The security context in which the job is run is determined by the owner of the job […] If the job is owned by an account (either a SQL Server login or a Windows NT authenticated login) that is a member of the Sysadmin role, the SQL Agent job runs under the context of the account used to start the SQL Agent service.

When the job is not owned by an account with sysadmin privileges, we can find a problem similar to the following one when running the job:

Remote access not allowed for Windows NT user activated by SETUSER. [SQLSTATE 42000] (Error 7410). The step failed.

There is a Knowledge Base article that explains how to workaround this error (see KB811031). In our case, the error was quite different though:

Error: 7416, Severity: 16, State: 1
Access to the remote server is denied because no login-mapping exists

I did a basic review to understand what was the configuration of both the SQL Server Agent Job and Linked Server, in our case this was what we had:

• Job owner was the sa SQL Server account
• Job was made up of a single step with a simple stored procedure execution that took care of copying several rows from a remote table (2000) into a local table (2005)
• A Windows Domain account was used to run SQL Server Agent service in both the SQL Server 2000 and 2005
• Both servers was located on the same Windows Domain and the Agent service account was the same.
• In both SQL Server 2000 and 2005 there was a SQL Server account called "elearning" associated to an "elearning" login. "elearning” login had “datareader" and "public" roles

Concerning the linked server security configuration, my customer only had the "elearning" login on the security properties for the Linked Server, any other login attempt was denied:

 

In this case we discovered that adding the SQL Server Agent account to the list of authorized users in the linked server security properties allowed us to run the job successfully. This makes sense because the SQL Server Account credentials are the ones used to execute the job in this case however, this lead to a security problem since a non-authorized user on the remote server could use the linked server connection to perform actions he has no rights to perform by default. In other words, having the SQL Server Agent account as authorized on the linked server allows the use of that linked server connection by any job where the owner is part of the sysadmin group.

My first thought was to use a SQL Server Agent Proxy account to work around the issue but T-SQL statements cannot make use of Proxies:

Job steps that execute Transact-SQL do not use SQL Server Agent proxies. Transact-SQL job steps run in the security context of the owner of the job.

In our case the job owner was sa account so the Job was being run under the credentials of SQL Server Agent account however, the linked server only granted remote access to the "elearning" login. In addition to this, when configuring the advanced Job step properties using SSMS, the GUI does not allow to specify an alternate SQL Server login, only allows to specify an alternate SQL Server user:

However, the EXECUTE AS documentation clearly states that using both SLQ Server user accounts and logins are a valid approach:

SQL Server 2005 supports the ability to impersonate another principal either explicitly by using the stand-alone EXECUTE AS statement […]. The stand-alone EXECUTE AS statement can be used to impersonate server-level principals, or logins, by using the EXECUTE AS LOGIN statement. The stand-alone EXECUTE AS statement can also be used to impersonate database level principals, or users, by using the EXECUTE AS USER statement.

The solution for the customer’s requirements was as easy as using the EXECUTE AS LOGIN instruction in the T-SQL code for the stored procedure:

How the SQL Server Agent and linked server security work is not difficult once you make sense of all the pieces in the puzzle. Knowing where these pieces fit will help you to better asset your SQL Server’s security.