SQL Server Agent – Proxy Account

Background

We are developing some utilization reports.  Though, the reports and the accompanying programmable objects are not nearly ready for Production deployment, we need to get our hands on realistic and more recent data from our Production environment.

And, so what we have decided to do is prepare a SQL Server Agent job that runs on a periodic schedule and downloads more recent data from the Production DB unto our Development database.

Why this Post?

In a SQL Server installation, there are two baseline accounts; the account the SQL Server Engine is running under; and the account that the SQL Server Agent is running under.

As a rough sketch, in most cases, the engine account is effectual.  And, the Agent account is only manifested when a job is running.  The Agent connects to the engine and acts as a surrogate to run a job.

In a heavily used SQL Instance, there could be need to delegate security and not have all activities be marshaled under the singular account of the lone SQL Agent.

For that purpose, we can  use Proxy Accounts.

Requirements

There are a couple of things that I know we need right off the bat.  And, those are :

  • A locked-down Active Directory Service Account
    • I personally don’t like the old classic native SQL Account with exposed passwords
    • I prefer AD Account locked down with a bit higher password requirements.  If abused or compromised, you can disable them in one place
    • They are also much easier to audit – determine when they were last used
    • Easier to follow and trace through your Network, as well
    • They can also be locked down to specific machines
  • Database Objects
    • List of database tables that we will be reporting on
    • Review the table sizes and presence of primary/distinguishing keys, timestamp /last update datetime
  • Refresh Type
    • Full refresh or incremental refresh?

 

Scripts

Here is a quick rundown on the scripts that we will be creating…

  • Linked Server Creation Script
    • This script creates a so called symbolic link to the other server; in this case our Production DB Server
  • Stored Procedure Script
    • This is  a Stored Procedure script that will contain our insert/delete/update/merge/BULK INSERT statements
  • SQL Server Agent Operator
    • This script will register the  list of interested parties that should be notified, in case of scheduled jobs failure
  • Database Job
    • The container job that will initiate the data transfer step and notification alert in case of failures

 

Provision Account

 

Let us grant our proxy account access to our Subscriber DB

Grant access to SQL Instance

use [master]
go
if SUSER_SID('LABDOMAIN\datarunner') is null
begin

     print 'Granting Account access to SQL Server ... ' 

     create login [LABDOMAIN\datarunner] from windows; 

     print 'Granted Account access to SQL Server'
end
else
begin
    print 'Account exists'
end
go

 

Grant Access to User Database


/*
    Grant access to specific database
*/
use [database]
go

if DATABASE_PRINCIPAL_ID('LABDOMAIN\dataRunner') is null
begin

    print 'Granting Account access to ' + db_name()

    create user [LABDOMAIN\dataRunner]
    from login [LABDOMAIN\dataRunner];

    print 'Granted Account access to ' + db_name()

end
else
begin

    print 'Account exists in '+ db_name()

end
go

 

Linked Server Creation Script

Using SQL Server Management Studio, we created the Linked Server:

Linked Server Properties – Security

LinkedServerProperties_Security

 

Linked Server Properties – Server Options

LinkedServerProperties_ServerOptions
Test the Connection

TestConnection

 

Transfer Script

Here is what our transfer script looks like.


set identity_insert on dbo.[customer] on;

declare @customerID bigint

set @customerID = isNull( 
                           select max([customerID])
                           from [PROD].[salesDB].[dbo].[customer]
                        ), 0)

insert into dbo.[customers]
([customerid], [customerName])
select [customerid], [customerName]
from  [PROD].[salesDB].[dbo].[customer]
where [customerID] > @customerID

set identity_insert on [dbo].[customers] off;

 

SQL Server Agent Operator

We have a script that creates a SQL Server Agent Operator account.

I will encourage that we beforehand  have created an email distribution list and use that distribution list;  thus we avoid needing to edit jobs as DBAs come and go.

NewOperator

 

Database Job

Job Step – General

JobStep-General

 

Job Step – Advanced

JobStep-Advanced

 

Explanation:

  • Notice there are two “Run as” drop-down fields
    • On the “General” page, the “Run as” is available when the “Job Step” is other than a “Transact-SQL Script (T-SQL)”
    • On the “Advanced” page, the “Run as user” is available when the Job Type is “Transact-SQL script (T-SQL)”

 

Execute Job

So everything is in place. We kicked off the job and got an error.

Here is the error:

 

Textual:

Executed as user: LABDOMAIN\datarunner. Access to the remote server is denied because the current security context is not trusted.
[SQLSTATE 42000] (Error 15274).  The step failed.

Image:

JobHistory

 

Remediation

The problem is that when we set a Job Step to execute through a proxy account, the system will subjugate the permission of the job’s owner,

and assume the permissions of the proxy account.

This can have system wide security ramifications and at day’s end, we will blame the wrong person, the running user; which for that step will be

the Proxy Account.

And, so we are being forced to vouch for the entire database or at minimum the SQL Step that we are running.

Notice that SQL Server CLR has the same requirement.

Again, the requirements are:

  • The database has to be marked trustworthy
  • or, the programmable object invoked should be signed

 

Easy Way Out

The easy way out for us, is to mark our Database as Trustworthy.


   alter database [database] set trustworthy on;

 

Errors

Review of errors we encountered and how we addressed each:

SQL Severity File Version Error Message Fix
 14 916 EXECUTE AS USER’ failed for the requested user ‘LABDOMAIN\datarunner’ in the database ‘HRDB’.  The step failed. Create SQL Server Login and Create individual databases users
16  15274  Executed as user: LABDOMAIN\datarunner. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274).  The step failed. Set referenced database to trustworthy

 

Additional Reading

Impersonation and Credentials for Connections
https://msdn.microsoft.com/en-us/library/ms131068.aspx

In the SQL Server common language runtime (CLR) integration, using Windows Authentication is complex, but is more secure than using SQL Server Authentication. When using Windows Authentication, keep in mind the following considerations.
By default, a SQL Server process that connects out to Windows acquires the security context of the SQL Server Windows service account. But it is possible to map a CLR function to a proxy identity, so that its outbound connections have a different security context than that of the Windows service account.

In some cases, you may want to impersonate the caller by using the SqlContext.WindowsIdentity property instead of running as the service account. The WindowsIdentity instance represents the identity of the client that invoked the calling code, and is only available when the client used Windows Authentication. After you have obtained the WindowsIdentity instance, you can call Impersonate to change the security token of the thread, and then open ADO.NET connections on behalf of the client.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s