Help! SQL Server 2005 Encryption Problem

[mcauliff]

I attempting to use the SQL Server 2005 encryption function. The code is in a ASP page with no display to the screen. The definition of the cc_number_encrypt column is varbinary(256). The update works in SQL Server Management Studio Query, but not in the program. It leaves the columna as NULL

Here is the code

SQLQuery = "Open SYMMETRIC KEY MosasKey decryption by certificate MosasCertificate"
Connection.Execute(SQLQuery)
If err.number <> 0 then
Response.Write("<br>error occured on Open symmetric key")
Response.Write("<br>error is " & err.number)
Response.End
Else
Response.Write("MosasKey is Opened<br>")
Response.Flush
End If


strCCNumber = Trim(Request("cc_number"))

Response.Write("<BR>Value of strCCNumber is " & strCCNumber)
Response.Flush


SQLQuery = "Update order_header set cc_number_encrypt = EncryptByKey(Key_GUID('MosasKey'), '" & strCCNumber & "') where order_id = " & ordernum
Response.Write("<br>Value of SQL is " & SQLQuery & "<BR>")
Response.Flush
Connection.Execute SQLQuery

If err.number <> 0 Then
Response.Write("<br>Error in Encrypt Update")
Response.Write("<br> err.number is " & err.number)
Response.End
End If


Here are the displays

Prior to Encrypt logicMosasKey is Opened

Value of strCCNumber is 123456789012
Value of SQL is Update order_header set cc_number_encrypt = EncryptByKey(Key_GUID('MosasKey'), '123456789012') where order_id = 151759


What is missing? when I view the table the cc_number_encrypt is NULL.

 

[mrdenny]

I would assume that its because you are doing the open and the encryption in separate calls. Check out the FAQs here and in the programming forum. I've got an example in there. A stored procedure will probably fix the problem.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2008 Implementation and Maintenance / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Admin (SQL 2005/2008) / Database Dev (SQL 2005)

My Blog

 

[mcauliff]

mrdenny

Thank you for the response.

I didn't want to do a Stored Procedure. I would had to call from with in the ASP program.

What I did, based on your response, is to combine the Open Symmetric and the Update into the SQL statement.

This work

Thanks again

 

[jbenson001]

You should ONLY use stored procedure from your front end code. Google "SQL Injection" can you will see why. Also, stored procedures make your code much more maintainable.