void LogUserName(SqlConnection conn, string userName) { string sqlText = "insert user_names values('" + userName + "')"; SqlCommand cmd = new SqlCommand(sqlText, conn); cmd.ExecuteNonQuery(); }
void LogUserName(SqlConnection conn, string userName) { string sqlText = "insert user_names values(@n)"; SqlCommand cmd = new SqlCommand(sqlText, conn); SqlParameter p = cmd.Parameters.Add("@n", SqlDbType.VarChar, userName.Length); p.Value = userName; cmd.ExecuteNonQuery(); }
In the current design, after an application role is enabled on a client connection to SQL Server, you cannot reset the security context of that connection. Therefore, when the user ends the SQL Server session and disconnects from the server, the session is not reusable. However, OLE DB resource pooling returns the closed connection to the pool, and the error occurs when that connection is reused and the client application tries to reset the connection's security context by calling sp_setapprole again.
The only available workaround is to disable OLE DB Resource Pooling, which ADO uses by default. You can do this by adding "OLE DB Services = -2" to the ADO Connection string, as shown here:
'For SQLOLEDB provider 'strConnect = "Provider=SQLOLEDB;server=SQL7Web;OLE DB Services = -2;uid=AppUser;pwd=AppUser; initial catalog=northwind" ' For MSDASQL provider 'strConnect = "DSN=SQLNWind;UID=Test;PWD=Test; OLE DB Services= -2"Pooling can be disabled for the SQL Server .Net Data Provider by adding "Pooling=False" to the connection string.