My diary of software development

Posts tagged ‘Oracle’

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();

            result.Append(GetDecoratedFunctionName("ExecuteDBCommand"));
            try
            {
                using (SqlConnection conn = new SqlConnection(dbcs))
                {
                    conn.Open();

                    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));

                    conn.Close();
                }

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

            return result.ToString();
        }

        #endregion

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();
            result.Append(GetDecoratedFunctionName("ExecuteSQLServerDBCommand"));
            try
            {
                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();
        } 

        #endregion 

Results

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.
Advertisements