Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using SQL Server 2008 Sp3

Lately I'm having errors in the SQL Logs:

Error: 18059, Severity: 20, State: 1.
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.

I have found via Profiler the relevant originated server and application.

I searched and found someone that says that I need to change the connection pooling in the application's connection string.

Below is the relevant Web.Config file:

<?xml version="1.0"?>
<appSettings>
    <add key="Main.ConnectionString" value="Data Source=db-002\db2;Initial   
Catalog=APPDB;TRUSTED_CONNECTION=YES"/>
    <add key="ConnectionHash" value="==QMr1meolHO3YWdTNFOzcTO"/>
    <add key="Database" value="[APPDB]"/>
    <add key="UseApprole" value="True"/>
</appSettings>

I changed it to the following in our test environment:

<?xml version="1.0"?>
<appSettings>
    <add key="Main.ConnectionString" value="Data Source=db-002\db2;Initial 
Catalog=APPDB;TRUSTED_CONNECTION=YES;Pooling=false"/>
    <add key="ConnectionHash" value="==QMr1meolHO3YWdTNFOzcTO"/>
    <add key="Database" value="[APPDB]"/>
    <add key="UseApprole" value="True"/>
</appSettings>

I've reset the application, services and IIS. Checked the application and it works.

For some time I didn't receive these errors anymore, but after 3 hours they reverted.

Can anyone give me an idea what can I do?

share|improve this question
    
Are you doing any impersonation on the SQL Server side? – Jon Seigel Feb 15 '13 at 15:53
2  
Is the application doing impersonation within the .NET code? – mrdenny Feb 15 '13 at 16:15
1  
call unset app role -- sp_unsetapprole before closing the connection from the app. – stacylaray Mar 1 '14 at 21:38

The post that Roni Vered referred to in 2013 did not say simply say: "change the connection pooling in the application's connection string."

The text said to that there was a problem with using "an Application Role ... with connection pooling." The answer was: Turn Off Connection Pooling.

This could be done in the application's connection string by including "; Pooling=False".

This worked back in the mid-2000's. Likely it will still work for you if you are still using SQL Server 2008.

The no longer correct path to the original post was: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=473777&SiteID=1

A suggested workaround from 2007: On each call if the approle is already in use, then skip the setting of the approle again. Sample code:

IF NOT EXISTS (SELECT * FROM sysusers
    WHERE name = USER_NAME()
     AND isapprole = 1)
 EXEC sp_setapprole ...  
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.