• I'm posting this in response to an email request for some sample ASP code to encrypt/decrypt using the DBA Toolkit.  Unfortunately it's been quite a while since I used good old ASP (sans .NET), so I'll post a short ASP.NET/VB.NET snippet that shouldn't be too difficult to translate backwards.  The key items to consider here are that:

    1) the UDF's accept and return VARBINARY values, which in .NET are BYTE() arrays.  Hence all the System.Text.Encoding... calls. 

    2) The example assumes a few things - that your server is (local), the login is Windows Integrated security, and the default Local Key and Master Key created by the INSTALL.SQL script are still around.

    Hope this helps!

    Dim Plaintext As String = "This is some Plain Text"

    Dim Bits As Integer = 256

    Dim Key As String = "Local Key 1"

     

    Dim sqlcon As System.Data.SqlClient.SqlConnection

    Dim sqlcmd As System.Data.SqlClient.SqlCommand

    Dim sqlcmd2 As System.Data.SqlClient.SqlCommand

     

    Try

        Console.Writeline (Plaintext)

     

        sqlcon = New System.Data.SqlClient.SqlConnection("server=(local);initial catalog=master;integrated security=sspi;")

        sqlcon.Open()

     

        sqlcmd = New System.Data.SqlClient.SqlCommand("SELECT dbo.fn_encrypt_aes(CAST(@plaintext AS VARBINARY), @key, NULL, @bits)", sqlcon)

        sqlcmd.Parameters.Add("@plaintext", SqlDbType.VarBinary, 1000).Value = System.Text.Encoding.ASCII.GetBytes(Plaintext)

        sqlcmd.Parameters.Add("@key", SqlDbType.VarChar, 64).Value = Key

        sqlcmd.Parameters.Add("@bits", SqlDbType.Int).Value = Bits

        Dim Encryptedtext As Byte() = sqlcmd.ExecuteScalar()

     

        Console.Writeline (System.Text.Encoding.ASCII.GetString(Encryptedtext))

     

        sqlcmd2 = New System.Data.SqlClient.SqlCommand("SELECT dbo.fn_decrypt_aes(@enctext, @key, NULL, @bits)", sqlcon)

        sqlcmd2.Parameters.Add("@enctext", SqlDbType.VarBinary, 1000).Value = Encryptedtext

        sqlcmd2.Parameters.Add("@key", SqlDbType.VarChar, 64).Value = Key

        sqlcmd2.Parameters.Add("@bits", SqlDbType.Int).Value = Bits

                             = "g" convert the relevantou e the article or the Toolkit.chm file for directions)If there were some way wDim Decryptedtext As String = System.Text.Encoding.ASCII.GetString(sqlcmd2.ExecuteScalar())

     

        Console.Writeline (Decryptedtext)

     

    Catch ex As Exception

        MessageBox.Show(String.Format("Error Occurred: {0}", ex.Message))

    Finally

        If Not (sqlcmd2 Is Nothing) Then

            sqlcmd2.Dispose()

        End If

        If Not (sqlcmd Is Nothing) Then

            sqlcmd.Dispose()

        End If

        If Not (sqlcon Is Nothing) Then

            sqlcon.Dispose()

        End If

    End Try