Application roles seem like the ultimate level of security in SQL Server. Unfortunately, in SQL Server 2000 you cannot use them together with connection pooling. This means you cannot have a scalable solution that would use application roles. The problem is with security context of the connection – it is not reset properly when it is closed (see Damir Arh’s blog). SQL Server 2005 has a new system procedure sp_unsetapprole, which deactivates an application role and reverts to the previous security context. You can check this easily with the code from this blog.
Start in SSMS; execute the following T_SQL code to prepare an application role in the Tempdb database:
USE tempdb;
GO
EXEC sp_addapprole ‘AdoDotNet’, ‘Ado’;
GO
Create a new VB console application. This is the main code:
Imports System.Data
Imports System.Data.SqlClient
Module AppRolesConnPooling
Sub Main()
AppRolesConnPooling_NoReset()
Console.ReadLine()
AppRolesConnPooling_Reset()
Console.ReadLine()
End Sub
In first subroutine, I am showing the SQL 2000 compatible approach – set up an application role without unsetting it and I use connection pooling:
Private Sub AppRolesConnPooling_NoReset()
Dim strConn As String
Dim cmdDB1, cmdDB2 As SqlClient.SqlCommand
Console.WriteLine(“SQL 2000 compatible code – no app role unset”)
‘Pooled connection string
‘Pool of just on connection to make sure to reuse it
strConn = “Server=localhost;” _
+ “Integrated Security=SSPI;” _
+ “pooling=yes;” _
+ “min pool size=1;” _
+ “max pool size=30;” _
+ “connection reset=true;” _
+ “Database=tempdb;”
Dim conn1 As New SqlConnection(strConn)
Dim conn2 As New SqlConnection(strConn)
‘Lets open them
conn1.Open()
cmdDB1 = conn1.CreateCommand()
cmdDB1.Parameters.Clear()
‘ Activate the Application role
cmdDB1.CommandText = “sp_setapprole”
cmdDB1.CommandType = CommandType.StoredProcedure
Dim parameter As SqlParameter = cmdDB1.Parameters.Add(“retvalue”, SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = cmdDB1.Parameters.Add(“@rolename”, SqlDbType.NVarChar, 128)
parameter.Value = “AdoDotNet”
parameter = cmdDB1.Parameters.Add(“@password”, SqlDbType.NVarChar, 128)
parameter.Value = “Ado”
‘Executes the command
cmdDB1.ExecuteNonQuery()
‘Close connection
conn1.Close()
‘Use the connection from the pool – error!
conn2.Open()
cmdDB2 = conn2.CreateCommand()
‘ Activate the Application role
cmdDB2.CommandText = “EXEC sp_setapprole ‘AdoDotNet’, ‘Ado'”
cmdDB2.CommandType = CommandType.Text
‘Executes the command
Try
cmdDB2.ExecuteNonQuery()
Console.WriteLine(“No error from the second command”)
Catch XcpSQL As SqlClient.SqlException
Console.WriteLine(“Error from the second command”)
Dim se As SqlClient.SqlError
For Each se In XcpSQL.Errors
Console.WriteLine(“Sql Error “ & se.Number.ToString & ” : “ & _
se.Message & ” ; Severity: “ & se.Class.ToString)
Next
Catch XcpInvOp As System.InvalidOperationException
Console.WriteLine(XcpInvOp.Message)
Catch Xcp As System.Exception
Console.WriteLine(Xcp.Message)
Finally
conn2.Close()
End Try
conn1.Dispose()
conn2.Dispose()
cmdDB1.Dispose()
cmdDB2.Dispose()
End Sub
When you run this procedure on SQL 2000, you get an “General network error. Check your network documentation.” error message. In SQL 2005, you get two interesting errors:
Sql Error 0 : A severe error occurred on the current command. The results, if any, should be discarded. ; Severity: 11
Sql Error 0 : A severe error occurred on the current command. The results, if any, should be discarded. ; Severity: 20
As you can see, both errors have error number zero and the same message, although they have different severity level. You get different nonsense from the one you get from SQL 2000; still, you get nonsense. Well, at least you do not have to spend hours in checking your network documentation.J I would definitely like to see these errors improved in next service pack!
The second procedure properly unsets the application role, and connection pooling works. You should get only the “No error from the second command” message notifying success of the operation. Note that the sp_setapprole system procedure accepts two new parameters in version 2005: @fCreateCookie input and @cookie output parameter. You have to pass the @cookie to the sp_unsetapprole procedure in order to unset the correct application role.
‘A proper way – set app roles with unset
Private Sub AppRolesConnPooling_Reset()
Dim strConn As String
Dim cmdDB1, cmdDB2 As SqlClient.SqlCommand
Console.WriteLine(“SQL 2005 compatible code – app role unset”)
‘Pooled connection string
‘Pool of just on connection to make sure to reuse it
strConn = “Server=localhost;” _
+ “Integrated Security=SSPI;” _
+ “pooling=yes;” _
+ “min pool size=1;” _
+ “max pool size=30;” _
+ “connection reset=true;” _
+ “Database=tempdb;”
Dim conn1 As New SqlConnection(strConn)
Dim conn2 As New SqlConnection(strConn)
‘Lets open them
conn1.Open()
cmdDB1 = conn1.CreateCommand()
‘ Activate the Application role
cmdDB1.CommandText = “sp_setapprole”
cmdDB1.CommandType = CommandType.StoredProcedure
cmdDB1.Parameters.Clear()
Dim parameter As SqlParameter = cmdDB1.Parameters.Add(“retvalue”, SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = cmdDB1.Parameters.Add(“@rolename”, SqlDbType.NVarChar, 128)
parameter.Value = “AdoDotNet”
parameter = cmdDB1.Parameters.Add(“@password”, SqlDbType.NVarChar, 128)
parameter.Value = “Ado”
parameter = cmdDB1.Parameters.Add(“@fCreateCookie”, SqlDbType.Bit)
parameter.Value = True
parameter = cmdDB1.Parameters.Add(“@cookie”, SqlDbType.VarBinary, 50)
parameter.Direction = ParameterDirection.Output
Dim myCookie As SqlTypes.SqlBinary
‘Executes the command – set the app role
cmdDB1.ExecuteNonQuery()
‘store the cookie
myCookie = cmdDB1.Parameters(“@cookie”).SqlValue
‘unset the app role
cmdDB1.Parameters.Clear()
cmdDB1.CommandText = “sp_unsetapprole”
cmdDB1.CommandType = CommandType.StoredProcedure
parameter = cmdDB1.Parameters.Add(“retvalue”, SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = cmdDB1.Parameters.Add(“@cookie”, SqlDbType.VarBinary, 50)
parameter.SqlValue = myCookie
‘Executes the command – unset the app role
cmdDB1.ExecuteNonQuery()
‘Close connection
conn1.Close()
‘Use the connection from the pool – no error!
conn2.Open()
cmdDB2 = conn2.CreateCommand()
‘ Activate the Application role
cmdDB2.CommandText = “sp_setapprole”
cmdDB2.CommandType = CommandType.StoredProcedure
cmdDB2.Parameters.Clear()
parameter = cmdDB2.Parameters.Add(“retvalue”, SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = cmdDB2.Parameters.Add(“@rolename”, SqlDbType.NVarChar, 128)
parameter.Value = “AdoDotNet”
parameter = cmdDB2.Parameters.Add(“@password”, SqlDbType.NVarChar, 128)
parameter.Value = “Ado”
parameter = cmdDB2.Parameters.Add(“@fCreateCookie”, SqlDbType.Bit)
parameter.Value = True
parameter = cmdDB2.Parameters.Add(“@cookie”, SqlDbType.VarBinary, 50)
parameter.Direction = ParameterDirection.Output
‘Executes the command
Try
cmdDB2.ExecuteNonQuery()
myCookie = cmdDB2.Parameters(“@cookie”).SqlValue
Console.WriteLine(“No error from the second command”)
Catch XcpSQL As SqlClient.SqlException
Console.WriteLine(“Error from the second command”)
Dim se As SqlClient.SqlError
For Each se In XcpSQL.Errors
Console.WriteLine(“Sql Error “ & se.Number.ToString & ” : “ & _
se.Message & ” ; Severity: “ & se.Class.ToString)
Next
Catch XcpInvOp As System.InvalidOperationException
Console.WriteLine(XcpInvOp.Message)
Catch Xcp As System.Exception
Console.WriteLine(Xcp.Message)
Finally
‘unset the app role
cmdDB2.Parameters.Clear()
cmdDB2.CommandText = “sp_unsetapprole”
cmdDB2.CommandType = CommandType.StoredProcedure
parameter = cmdDB2.Parameters.Add(“retvalue”, SqlDbType.Int)
parameter.Direction = ParameterDirection.ReturnValue
parameter = cmdDB2.Parameters.Add(“@cookie”, SqlDbType.VarBinary, 50)
parameter.SqlValue = myCookie
‘Executes the command – unset the app role
cmdDB2.ExecuteNonQuery()
‘close the 2nd connection
conn2.Close()
End Try
conn1.Dispose()
conn2.Dispose()
cmdDB1.Dispose()
cmdDB2.Dispose()
End Sub
End Module
Finally, whan you are done, you can drop the application role from the Tempdb:
EXEC sp_dropapprole ‘AdoDotNet’;
GO
