Sql-server – Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

errorssql-server-2008

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?

Best Answer

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 ...
Related Question