tags:

views:

192

answers:

2

Hi All

I have a program which would use the Application Role to write data to a SQL Server 2005.

using (SqlConnection sqlCon = new SqlConnection(connectionString))
{
    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.Connection = sqlCon;
    sqlCommand.CommandType = CommandType.Text;
    sqlCommand.CommandText = 
               "EXEC sp_setapprole 'application Role name','password';";
    sqlCommand.CommandText += sqlComm;
    sqlCommand.CommandTimeout = 300;

    sqlCon.Open();
    int res = sqlCommand.ExecuteNonQuery();
}

This code is in a loop. for the first time, it's OK. In second iterator, it throws exception.

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. Event ID 18059 Source MSSQLSERVER

Is there anyone meet this problem before?

Best Regards,

A: 

There's a couple of possible solutions here on the MSDN.

Preet Sangha
+1  A: 

turn off connection pooling, explicitly. by default is on and connection pooling does not work with unreversible impersonation like approles.

Remus Rusanu