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

 

 

Dejan Sarka