In my first post on this subject, I wrote about a problem that occurs in the ADO.Net connection pool when a .Net application authenticates its callers to SQL Server using their Kerberos ticket. I am now going to present a solution to this problem and an actual implementation of the solution.
Most applications today are n-tiered applications with a data tier which accesses the database on behalf of the frontend user. The n-tiered application uses Kerberos so that the frontend user’s identity is passed through the application’s tiers to the data tier and used to authenticate the user to the database.
Since the data tier’s database connection is established under the credentials of each frontend user, the application receives two important benefits: data auditing accuracy and a tighter more focused security model. SQL Server audit trails are able to record data operations and tie them to each individual user. In addition, each user’s operations are performed under their specific database permission set.
The problem with this pattern is in the connection pool. The ADO.Net connection pools are indexed by the user and by the database so in the above example, there would be 3 connection pools. One pool for User1, one pool for User2, and one pool for User3 which drastically affects the application’s scalability since a database connection would have to be created for each user.
One solution to this problem is to use Microsoft’s Trusted Subsystem pattern. In this pattern, the data tier would authenticate to the database using its own service account and operate against the database on behalf of each frontend user. By using this pattern, the connection pooling problem is solved because the data tier’s service account is the only account which authenticates to the database. However, there is at least one drawback to using the data tier’s service account to authenticate to the database: The SQL Server auditing module can no longer tie the actions it records to the individual frontend users. In addition, the data tier’s service account would need enough permissions in the database to accommodate all users from the least privileged user to the most privileged user.
One way around the audit problem is to not use the SQL Server auditing module and replace it with one at the data tier level. Since the data tier gets the Kerberos ticket, it knows who is requesting the data and can therefore write the audit trail entries itself. But why re-invent the wheel when SQL Server has a tried and true enterprise level auditing system?
In order to solve both the audit trail and the permissions problem, the Trusted Subsystem approach should be used and the data tier’s service account should be granted impersonate privileges on each frontend user account. Each time the service account accesses the database for a user, it should issue an Execute As Login=xxx statement, perform the necessary operations, and then issue a Revert statement. This way, the SQL Server auditing module can accurately record operations per user and the database operations the service account performs are constrained by each user’s permissions.
In order to prove this solution in a way as close to the real world as possible, I setup an environment with the following virtual machines in a development domain I named POPEDEV:
||2008 R2 x64
||Domain controller – POPEDEV domain
||2008 R2 x64
||2008 R2 x64
||2008 R2 x64
||2008 R2 x64
||SQL Server 2008 (running under Network Service)
||Development and user workstation
My test involved a web service to return a set of products from a SQL Server Products table. Each of the web services had a service operation named GetProducts(). I created three web services: Hop1, Hop2, and BDE. The client called Hop1.GetProducts() which called Hop2.GetProducts() which called BDE.GetProducts(). I created three web services and therefore three hops to exercise the Kerberos ticket delegation process. The Kerberos ticket which was created on the workstation would be passed to Hop1 and then to Hop2 and finally to BDE.
I deployed the following web services to each web server:
||Application Pool Identity
||Proxy web service to call Hop2.
||Proxy web service to call the BDE.
||Business Data Engine. This is the data tier.
The client application on WORK1 would call the Hop1 web service on WEB1 which would do nothing except call the Hop2 web service on WEB2. The Hop2 web service would do nothing except call the BDE web service on WEB3. The BDE web service would be my data tier and would connect to the database as a trusted subsystem to return the requested data to Hop2 which would return the data to Hop1 which would return the data to the client on WORK1.
I wrote my web services in WCF and configured them to use the WsHttpBinding with message security and Kerberos authentication.
For my testing, I created 5 domain users: USER1 – USER5 and wrote the client application to be able to impersonate one of those 5 domain users before calling Hop1. This way, I could simulate any of the 5 users being the frontend user. In addition, I created the domain user POPEDEV\BDE which would be the data tier’s service account user.
When the Kerberos ticket is passed from one the Hop1 web service to the Hop2 web service, Hop1 is basically delegating to Hop2 the user’s request for data. By the same token, when Hop2 called the BDE and passed it the Kerberos service ticket, it was delegating to the BDE web service. However, the ability to delegate from one machine/service default ability in Active Directory. Each machine/service must be given explicit delegation permissions.
This is called constrained delegation and is a security feature of Active Directory. The opposite would be open delegation where, when a process received a user’s identity in a Kerberos ticket, could call any other service local or remote on that user’s behalf. This could present a whole host of security problems if an attacker were able to launch a service within the organization and induce users to call it.
So I needed to setup the delegation settings on WEB1, WEB2, and WEB3 to be able to delegate to WEB2, WEB3, and SQL Server service as shown below:
In the screen shot above, I gave WEB1 permission to delegate to the service HTTP/web2.popedev.com because the HOP2 web service on WEB2 was running under the Network Service account.
Delegating to the BDE Web Service
The last set of delegation permissions was for the Hop2 web service on WEB2 to be able to delegate to the BDE web service on WEB3. This wasn’t quite as straight forward as permitting WEB1 to delegate to WEB2 because the BDE web service was running under the service account POPEDEV\BDE.
First I added the service principal name HTTP/web3.popedev.com to the POPEDEV\BDE account:
Next I enabled WEB2 to delegate to that same service name:
Running the WhoAmI Test
The first test I wanted to perform was to verify that the Kerberos ticket was being passed between the web servers and would make it all the way to the BDE web service.
I created a method named WhoAmI() in each web service which would return the current thread, windows, and other identities. It returned 4 identities as follows:
If the Kerberos service ticket was being passed from web service to web service, then these four methods should return the name of the frontend user (e.g. POPEDEV\User2). Except that the BDE web service, which was not impersonating its users to the SQL Server would return POPEDEV\BDE for the Windows identity because POPEDEV\BDE was its service account identity.
In addition, I ran NetMon on WEB1 and WEB2 to watch for Kerberos packets as another verification step.
I ran my console tester and the opening menu asked which operation I wanted to run. I chose option 0, the WhoAmI operation.
After choosing the WhoAmI operation, the console tester asked which user to impersonate. I chose POPEDEV\User4.
After choosing which user to impersonate, the console tester called the WhoAmI method on Hop1. In the screen shot above, you can see that the Hop1 and Hop2 web services see the user as POPEDEV\User4. The BDE web service shows the windows identity as POPEDEV\BDE and the thread identity as POPEDEV\User4.
The next screen shot shows the NetMon captures with the packet filter set to watch for Kerberos frames. As you can see, WEB1 requested http/WEB2.POPEDEV.COM and WEB2 requested http/WEB3.POPEDEV.COM which is exactly what I would have expected.
WEB1 NetMon trace:
WEB2 NetMon trace:
After seeing the results of the WhoAmI test and the captured frames in NetMon, I was confident that Kerberos was being used to authenticate all the way to the BDE web service.
Running The Full Test
For the full test, I wanted to prove that any user could request data from the data tier and the audit trail would record the select operation under that user and I wanted to prove that the data tier was using a single connection pool.
The data which the BDE web service returned was a set of products in a table I named Products. Here is a sample of what the BDE needed to run in order to return the products correctly:
Execute as Login= ‘Popedev\User3’
select * from Product
The first line ‘Execute as…’ enabled the BDE to run the select statement underneath the identity of the frontend user whose identity came across in the Kerberos ticket. Remember that the BDE connected as its service account so that a single connection pool would be used. In addition, the BDE web service needed to run the SQL underneath the frontend user’s identity so that the audit trail would be accurate and that the SQL operations would be run under the frontend user’s security.
The last line ‘Revert’ reverted the connection back to the BDE web service’s service account identity.
Here is a shot of the current database connections before I ran the test. There are 2 connections to the master database and 1 connection to the tempdb database. After running the test, I should see a new connection to my test database which I named kerbtest.
I ran the console tester and told it I wanted operation 1 – GetProducts and to impersonate POPEDEV\User3.
The console tester next asked whether I wanted to use LINQ or ADO.Net to get the products. I chose LINQ and it returned 7 products.
The SQL Server activity monitor now shows process 54 which was my BDE web service’s connection. Notice that process 54 is under the BDE web service’s service account POPEDEV\BDE and not POPEDEV\User3 who is the frontend user in my test.
This next screen shot shows the audit trail for session 54. Notice the SESSION_SERVER_PRINCIPAL_NAME is POPEDEV\BDE because that is who opened connection 54. The DATABASE_PRINCIPAL_NAME is POPEDEV\User3 because that is who the console tester was running under.
I then ran my test again but this time I told the tester to impersonate POPEDEV\User5 and to use ADO.Net instead of LINQ:
Here is a screen shot of the SQL Server activity monitor. Notice that there is still one session – 54:
The next screen shot shows the audit trail. The 2 entries reflect session id 54 as excepted and the DATABASE_PRINCIPAL_NAME reflects POPEDEV\User5 and POPEDEV\User3. In addition, the statement is different because I used LINQ the first time and ADO.Net the second time.