#Sql server connection string pooling code
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: I wrote this web service as a WCF web service that was configured for Kerberos authentication.
Here is a screen shot of the WhoAmI() test:
#Sql server connection string pooling windows
Returns both the thread identity and current windows identity. WhoAmI() – Returns the identity of the caller. My test client presented a menu and gave the user an option of calling one of two operations: 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. 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.Īt 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. 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 setup these machines in my trusty hyper-v environment I used for the Sharepoint 2010 farm. This second web service would then connect to a SQL Server database on a third machine: This web service would then connect to another web service on a second machine. The test I came up with was to write a test client which connected to a web service on another machine. However, at this point I was pretty sure I was right because it *seemed* right given everything I knew.īoy, was I in for an eye opener. 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.Īfter 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. 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. 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 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. I told him that I didn’t know for sure but I didn’t *think* Kerberos authentication would affect connection pooling. It's ASP.NEt application.A manager at my company asked me this question. My application supports SQL Server 2008, 20. How can I identify which connection is open due to leakage and which is hanging around there due to connection pooling? Does SQL Server terminates unused connection after some time regardless of the Connection Lifetime value? I am wondering why I get this result when Connection Lifetime is zero. I keep my application idle for some time (15-20 minutes) and then I see that sp_who2 does NOT show any connection with my database. Connection Lifetime having value zero means SQL server should not close connection ever, right? This means that it's default value 0 will be used if I am right. I am using connection pooling but Connection Lifetime is not specified in the connection string. The status for all connections is Sleeping and Command value for all of them is AWAITING COMMAND.
After some operations, when my application is idle (user not doing any activity), I see 7 connections with my database in the result set returned by sp_who2. I am trying to identify SQL connection leaks in my application.
0 kommentar(er)
