My diary of software development

Posts tagged ‘SQL Server’

Does Kerberos authentication affect SQL Server connection pooling? Part 2

The Problem

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.

The Solution

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.


Solution Implementation

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:

Host Operating System Role
DC1 2008 R2 x64 Domain controller – POPEDEV domain
WEB1 2008 R2 x64 IIS
WEB2 2008 R2 x64 IIS
WEB3 2008 R2 x64 IIS
SQL1 2008 R2 x64 SQL Server 2008 (running under Network Service)
WORK1 Win7 x86 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:

Host Web Service Application Pool Identity Role
WEB1 Hop1 Network Service Proxy web service to call Hop2.
WEB2 Hop2 Network Service Proxy web service to call the BDE.
WEB3 BDE POPEDEV\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.

Constrained Delegation

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/ 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/ 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:

  • ServiceSecurityContext.Current.PrimaryIdentity.Name
  • ServiceSecurityContext.Current.WindowsIdentity.Name
  • WindowsIdentity.GetCurrent().Name
  • Thread.CurrentPrincipal.Identity.Name

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.

Does Kerberos authentication affect SQL Server connection pooling?

The Question Submitted To Me

A manager at my company asked me this question. I told him that I didn’t know for sure but I didn’t *think* Kerberos authentication would affect connection pooling. I put on my thinking cap at this point and told him that the connection pool in an ADO.Net application (he was concerned with only .Net apps) was keyed on the connection string so, I told him, if the connection string didn’t change between users then a single connection pool would be used for all users.   

I went on to tell this manager that with Kerberos authenticated users, the connection string would be a trusted connection string without specific credentials for each user. Since the connection string would be the same trusted connection string for all users, there would be one connection pool for all of the Kerberos authenticated users.   

After telling him the above information, he asked me to put together a test to document my findings so I set about to create a test which could prove this one way or the other. However, at this point I was pretty sure I was right because it *seemed* right given everything I knew.   

Boy, was I in for an eye opener.   

Overview Of My Test

The test I came up with was to write a test client which connected to a web service on another machine. This web service would then connect to another web service on a second machine. This second web service would then connect to a SQL Server database on a third machine:   


I setup these machines in my trusty hyper-v environment I used for the Sharepoint 2010 farm.   

To validate my theory, I decided to create 5 domain users: user1 – user5 and run each of them through my test one by one. I figured at the end of the test, the SQL Server would have either 5 seperate connections for each user or it would have one session which was shared by each user in the connection pool.   

At this point, I had been doing a lot of reading on Kerberos and I ran across a document from Microsoft which flat out told me that Kerberos authentication would defeat the connection pool. However I still thought I was right and decided that I’d ignore that document, maybe they were taking about an issue that didn’t apply to this environment. Besides, I was in the midde of setting up this test and I wanted to finish it.   

Running The Test

My test client presented a menu and gave the user an option of calling one of two operations:

WhoAmI()  – Returns the identity of the caller. Returns both the thread identity and current windows identity.

Here is a screen shot of the WhoAmI() test:

WhoAmI() Test Results

ExecuteSQLServerDBCommand()  – Accepts 2 paramaters: Database Connection String and a SQL string. This method connects using  the database connection string, executes the SQL, and returns the SPID of the current DB connection and the number of rows affected by the executed SQL.
Here is a screen shot of the ExecuteSQLServerDBCommand() test:

ExecuteSQLServerDBCommand() Test Resuts


SQL Server Configuration

I’ll start with the SQL Server endpoint of my test because its going to be easier to explain each endpoint by starting at the end and working my way back to the test client.   

My SQL Server was a SQL Server 2008 instance running on Windows Server Standard 2008R2. I created a simple database named KERBTEST and gave all 5 domain users access to it:   

KERBTEST SQL Server Database

WCF Web Service 2

I wrote this web service as a WCF web service that was configured for Kerberos authentication. This web service was the final endpoint before the SQL Server and it contains the two operations WhoAmI() and ExecuteSQLServerDBCommand(). A partial block of the code in the WCF Web Service 2 is shown below:

        #region IService2 Members

        [OperationBehavior(Impersonation = ImpersonationOption.Required)]
        string IService2.WhoAmI()
            string result = String.Format("{3}System.Security.Principal.WindowsIdentity.GetCurrent().Name = {1}{0}System.Threading.Thread.CurrentPrincipal.Identity.Name = {2}{0}"
                , Environment.NewLine
                , System.Security.Principal.WindowsIdentity.GetCurrent().Name
                , System.Threading.Thread.CurrentPrincipal.Identity.Name
                , GetDecoratedFunctionName("WhoAmI"));

            return result;

        [OperationBehavior(Impersonation = ImpersonationOption.Required)]
        string IService2.ExecuteSQLServerDBCommand(string dbcs, string commandSQL)
            StringBuilder result = new StringBuilder();

                using (SqlConnection conn = new SqlConnection(dbcs))

                    SqlCommand cmd = new SqlCommand("select @@SPID", conn);
                    string spid = cmd.ExecuteScalar().ToString();

                    cmd = new SqlCommand(commandSQL, conn);
                    int rowsEffected = cmd.ExecuteNonQuery();
                    result.Append(String.Format("SQL Server connection established:{0}\tSPID = {1}{0}\tCommand executed. Rows effected = {2}{0}"
                        , Environment.NewLine
                        , spid
                        , rowsEffected));


            catch (Exception ex)
                result.Append(String.Format("{0}{1}", Environment.NewLine, ex.ToString()));

            return result.ToString();


WCF Web Service 1

I wrote this web service as a WCF web service also that was configured for Kerberos authentication. This web service had the same operations as Web Service 2 (WhoAmI() and ExecuteSQLServerDBCommand()).

The WhoAmI() operation did the same as the WhoAmI() operation in the second web service and then called WhoAmI() on the second web service.

The ExecuteSQLServerDBCommand() operation did nothing in this web service except call the ExecuteSQLServerDBCommand() operatin in the second web service.

A partial block of the code in the WCF Web Service 1 is shown below: 


        #region IService1 Members 

        [OperationBehavior(Impersonation = ImpersonationOption.Required)]
        string IService1.WhoAmI()
            ServiceReference2.Service2Client svc = new WcfService1.ServiceReference2.Service2Client();
            svc.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation; 

            string result = String.Format("{3}System.Security.Principal.WindowsIdentity.GetCurrent().Name = {1}{0}System.Threading.Thread.CurrentPrincipal.Identity.Name = {2}{0}{4}"
                , Environment.NewLine
                , System.Security.Principal.WindowsIdentity.GetCurrent().Name
                , System.Threading.Thread.CurrentPrincipal.Identity.Name
                , GetDecoratedFunctionName("WhoAmI")
                , svc.WhoAmI()

            return result;

        [OperationBehavior(Impersonation = ImpersonationOption.Required)]
        string IService1.ExecuteSQLServerDBCommand(string dbcs, string commandSQL)
            StringBuilder result = new StringBuilder();
                ServiceReference2.Service2Client svc = new WcfService1.ServiceReference2.Service2Client();
                svc.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;
                result.Append(svc.ExecuteSQLServerDBCommand(dbcs, commandSQL));
            catch (Exception ex)
                result.Append(String.Format("{0}{1}", Environment.NewLine, ex.ToString()));

            return result.ToString();



I ran my test and executed 2 ExecuteSQLServerDBCommand() operations. One for user1 and one for user2. Remember that ExecuteSQLServerDBCommand() would return the SPID of the current connection so if I was right and Kerberos authentication did not affect the connection pool, then the same SPID would be returned for each user in my test.

Here is a screen shot of that test:

Full Test Results

The test proved that Kerberos authentication affected the ADO.Net connection pool. I have a friend who often uses the single word phrase “sigh…” to appropriately sum up most situations. So that’s what I said at this point. Sigh…

The reason Kerberos authentication affects the connection pool is that ADO.Net keys the connection pool on not just the connection string, but also the user identity. So if 100 users connect to the database with a secure connection string, then 100 pools will get created, each with a single connection string.
I told the manager who tasked me with settling this issue my findings. He and I then presented them to a VP who promptly told us that in Oracle, this would not happen. I didn’t believe the VP but didn’t tell him. After all, I was zero and one in my competitions to prove my theories over someone more knowledgable.
I wont go into my research on the Oracle issue, but I did quite a bit of research and found an answer. Unfortunately after finding the answer, I moved from zero and one to zero and two. Oracle has the ability to allow User2 to connect through User1’s connection and perform operations on it so there is no need for 2 seperate connections.

Calling a WSE web service from a SQL Server CLR UDF

My current project is to document and lay the foundation for communicating with web services from several different clients. Well, that’s pretty much a common activity for most developers today but the twist here is that the web services are implemented with SAS Analytics Server v9.2 and one of the clients will be SQL Server 2005. 
I set about putting up a lab to work in consisting of a SQL 05 virtual machine, a SAS 9.2 virtual machine, and my development workstation. Writing a web service client in SQL Server is fairly straightforward: just write a CLR user defined function and install it. Here are some good articles on that:
Implementing web services in SAS is a challenge and I’ve not gotten completely through it yet but I do know that SAS web services are secured using WSE 3.0.  So, while waiting on SAS support to help me get the SAS server up and going, I figured I would write a WSE secured service in .Net and work through the issues of calling in from a SQL UDF. Wahoo! I thought, as I’ve pretty much forgotten how to work with WSE since I picked up WCF. But 1/2 day later I found that web services are like remembering how to ride bikes and learning to ride that multi-wheeled, multi-geared contraption of spokes, levers, arms, and seats out of a Dr. Suess book called WCF didn’t make me forget how to ride my WSE road bike I keep in the garage.
I quickly wrote and deployed an ASMX web service secured with WCF and wrote a simple .Net console client which communicated with it. Great, I thought, now all I have to do is lift my code from the console app and drop it into a SQL UDF and I’ll be done. Alas, I found that a SQL UDF cannot have an associated WSE policy configuration file. You see, normally a WSE client has the luxury of a policy configuration file (by default named wse3PolicyCache.config) to maintain the necessary policies and their assertions for calling the service. However a SQL UDF comes packaged as an assembly .dll so there can be no configuration files associated with it.
When I first began to work through this problem of no configuration file, I thought there would be 2 paths I could take: creating the policies found in the configuration file in code or using the ConfigurationManager class to build a placebo of my console app’s physical configuration file in memory. I tried the second path first and quickly realized that it just wasn’t possible. So next I began to research the first route and stumbled across an MSDN article that did exactly what I wanted – After the heavanly light dimmed back down to normal and the choirs of angels stopped singing, I began to implement it and now I have a CLR UDF in my SQL Server which can call a WSE secured web service.