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.
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?
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
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
Linked Server Properties – Server Options
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.
Job Step – General
Job Step – Advanced
- 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)”
So everything is in place. We kicked off the job and got an error.
Here is the error:
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.
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;
Review of errors we encountered and how we addressed each:
Impersonation and Credentials for Connections
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.