The Long and Winding Road

Okay, so here’s a real rabbit hole of a journey trying to solve a security hole on SQL server. I got through new and interesting topics, but I inevitably found out that no, it can’t be done. (Isn’t that always the way?)

The Problem

In the current version of our product, we have multiple databases that house related but conceptually distinct information on the same instance of SQL server. We also extract information from remote client servers for aggregation using Linked Server objects. IP information varies from client to client, so dynamic SQL must be used to define the remote host in the query.

We have a tool that runs all of our scheduled jobs through OSQL rather then using SQL Server agent. At regular intervals during the day, we need to have the job runner log in with a service account and run a stored procedure that queries across several of our databases and to client databases. We want to tightly control security on this account since it will be accessing a database with sensitive information and will be talking with client databases, which also have sensitive information.

Small beginnings

My initial plans for implementing improved security were fairly straight forward, we encapsulate all of the actual logic into Stored Procedures that only DBA’s have permission to alter and we give our service account exec permission on a single databases that contains these stored procedures. In this way, our service account does not actually have permission to read sensitive data (or any data whatsoever) and it can still run jobs.

The problem here is that we need to use Dynamic SQL since the actual client server we will be querying is not know until run time (where the name is pulled from a table). Dynamic SQL
executes as if the user calling the procedure execute the query itself. As noted in the documentation. So we would still have to give the account permission to see read everything in order to run the Stored Procedure. Bummer.

However, this pointed me to my next possible solution.

I’m not me, I’m that guy over there!

Impersonation can be used to allow a user execute something that he does not normally have permission to execute. In particular, Stored Procedures can be written using an ‘Execute as’ clause.

--Arbitrary parameters here

--Arbitrary code here


This causes the SP to run as if it was called by the specified user account rather then the actual caller. Cool, now I can set up a secure proxy account and write the SP to use that account rather than the calling account. Once again, the service account will only have exec permission and will not be able to read any data. Exactly what we need, right?

Well, no. As it turns out, our proxy user is just that, a user and users only exist on a single database. A login can be mapped to users on different databases but the user accounts themselves only exist (and have permission on) a single databases. Since we are querying across multiple databases, no user can be set up with permission to access data in other databases.

After that harsh lesson in user accounts on SQL server, I found another possible solution using signed Stored Procedures.

Signed with a kiss… and 256 bits encryption

A stored procedure can be signed with a certificate, and then a user can be created form that same certificate. This certificate user can be granted the necessary permissions to run the SP. When someone run’s the SP, the permissions of the certificate user are added to there own during the execution. See here.

What makes this different is that, the certificate can be backup and moved to a different database and then a new user on that database can be created with that same certificate. This user can be grant AUTHENTICATE SERVER permission at which point it can authenticate for both users by way of the certificate verifying it to both databases.Documentation here.Blog post here.

The upshot is that our service account can be granted nothing but execute permission and access the data on both databases through a certificate. The SPs need to be resigned every time they are updated, but its a small price to pay for a secure database.

As you might have guessed, this too has a problem.

Et tu, linked server?

Linked server objects have a security context. The options boil down to creating mapping for logins on the server (specifying what login they use on the remote server) and defining what to do for anyone who is not one of these logins. The options are: don’t allow a connection, make a connection with no security context, use the user’s current security context (AD token or SQL username and password), or use a specified separate login.

We want to only grant permissions to our signed user so that we avoid letting the service account access remote servers, but our signed user does not have a login, it only exists as database user(s). As a result, it cannot be mapped in the linked server object. It also has no security context of its own. The only viable option would be to set the linked server to use a specified login for all users not on the mapped list which should pick up our certificate user. However, this opens the door for any account to get access to the remote server since any account would now be given that mapping for the remote server.

In short, if we’re going to use a linked server object, we will have to use something with an actual login.

Back to impersonation

Previously, my impersonation attempt was simply impersonating a database user. However, it is possible to impersonate a server login. See here. This should allow me to solve the problem by adding a line in the stored procedure that causes the service account to impersonate another login that has the necessary permissions while not granting the account direct permission on the database.

As always… no, not quiet. In order to impersonate a login, an account must be granted the impersonate permission on that login. Since the impersonated login has access to read data, and our service account has permission to impersonate that login, there is nothing stopping our service account from reading sensitive data through that account aside from ignorance of the impersonation permission. Security through obscurity is simply bad practice, so this solution isn’t going to work.

Where do we go from here?

So far all of my approaches have failed due to the complexity of our needs. So, what now?

I have looked into alternate ways of access remote data that avoid linked server objects. This would allow us to use a certificate user approach. The solution I see now is using OPENROWSET, which does not required a linked server and simply allows the call to directly state the login credentials, to make remote calls. Documentation. This, however, would require us to rework how we do things since we’d now have to store our remote credentials differently. We’d also have to look into encryption and security on those stored passwords.

In the end, I’m still looking for an ideal solution but, I think we may wind up simply having to do things differently or to have slightly looser security then I’m looking for. I’m not sure, but I do know it was quiet the ride getting even this far.