0

Using SQL Server 2016, Visual Studio 2017, and .Net 4.7.1 framework. I have a table (tJobs) with a column called TRAN_NO. It is an `nvarchar(11). I prepared the table with "Always Encrypted". Enabled the"Enable Parameterization" checkbox in SQL Server. Changed the SSMS connection and added "Column Encryption Setting=enabled".

After AE wizard finishes, I see the column is now encrypted in SSMS. I export the public and private key for AE and copy them to my dev PC. I open certificate manager and import those keys to the dev PC.

I run the following in SSMS and it successfully updates the TRAN_NO column:

Declare @Tran_No nvarchar(11) = 'A124000054A'
Update CCJOB
Set TRAN_NO = @TRAN_NO
Where Job_No = '235877'

SQL profiler sends encrypted data for the TRAN_NO field. This because I have added the encryption connection string variable and parameterization in the options. So in SSMS it works as intended (see below).

sp_executesql N'DECLARE @Tran_No AS NVARCHAR (11) = @p5c6c097b89b449d184469863f8e8685c;

UPDATE CCJOB SET TRAN_NO = @Tran_No WHERE Job_No = ''235877'';

',N'@p5c6c097b89b449d184469863f8e8685c nvarchar(11)',@p5c6c097b89b449d184469863f8e8685c=0x0157D68B171F2B325C21CF0914EEC4D4E7A91EBC90B61531E19413E3F5F514EDE58F827C5E983F56A1612E15BB817A5B65A8284D3FDE121F5B0C3F5BDC25CC68A3EB8AF52E3A0D212E0C41A0DE60B7BE77

So far so good.

I open .Net 2017 application that uses this table and set the connection string: 

Data Source=IC-SQL5;Integrated Security=SSPI;Initial Catalog=IC_CC2; Column Encryption Setting=enabled;

When I run the application it successfully pulls the TRAN_NO field from the DB, unencrypts it, and displays it in my textbox. Awesome. I love it.

The last thing I need is to write back changes to the DB when textbox changes. Must do through code. (side note: it doesn't work using the datasource binding control either and throws errors when trying to generate the update command)

I run the following update command in code:

Dim sStr As String = "Data Source=IC-SQL5;Integrated Security=SSPI;Initial Catalog=IC_CC2; Column Encryption Setting=enabled"

Dim oCN As New SqlClient.SqlConnection(sStr)

Dim oCom As New SqlClient.SqlCommand("spUpdate_CCJOB2", oCN)
oCom.CommandType = CommandType.StoredProcedure

oCN.Open()

oCom.Parameters.AddWithValue("@JOBNO", "235877")

Dim param3 = oCom.CreateParameter()
param3.ParameterName = "@TRAN_NO"
param3.DbType = SqlDbType.NVarChar = 11
param3.Direction = ParameterDirection.Input
param3.Value = "987654321"
oCom.Parameters.Add(param3)

oCom.ExecuteNonQuery()

This is the stored procedure on the server

ALTER PROCEDURE [dbo].[spUpdate_CCJob2]
    @JobNo VARCHAR(6), 
    @TRAN_NO NVARCHAR(11)
AS
    UPDATE CCJOB
    SET TRAN_NO = @TRAN_NO
    WHERE Job_No = @JobNo;

I get the following error:

Operand type clash: varchar is incompatible with nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'IC_CC2')

I tried this code using ADO.NET provider and SqlClient provider and get the same error message. Does not seem to be a provider issue.

I tried changing TRAN_NO to NChar, changing the size of the column, and every other combination I can think of.

When I send the stored proc through my .Net app using a parameterized query it sends the Tran_No in plaintext, even though I have the connection string set up properly. The documentation clearly states the provider should encrypt the Tran_No field when sending to MSSQL? SQL profiler clearly shows TRAN_NO is not encrypted (see below).

exec sp_describe_parameter_encryption N'EXEC [spUpdate_CCJOB2] @JOBNO=@JOBNO, @TRAN_NO=@TRAN_NO',N'@JOBNO varchar(6),@TRAN_NO varchar(11)' exec spUpdate_CCJOB2 @JOBNO='235877',@TRAN_NO='A124000054A'

Why isn't the provider encrypting the TRAN_NO field before sending to SQL Server?

sThomas
  • 1
  • 1
  • 1
    Take a look at this page: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-ver15 It'll answer some of your issues I think. TL;DR - don't use .AddWithValue. – TechGnome Nov 25 '19 at 19:53
  • Thanks for the info. I can't find anything in this that helps my situation. I did remove the AddWithValue and created a parameter. Did not change the error message. – sThomas Nov 25 '19 at 23:25
  • Did you look at the section titled `Errors due to passing plaintext instead of encrypted values` ... because the error described therein seems to be exactly the problem you're facing. – TechGnome Nov 26 '19 at 12:59
  • Yes, however, the example given is how to not send a query. I am using parameters to send the query. What I have read is that the provider takes the plaintext values and encrypts the data before passing to the stored procedure. I edited my post to show that SSMS does encrypt the field but .Net code does not encrypt the field. I am sure I am missing something but can't see it. Thanks – sThomas Nov 26 '19 at 17:39

1 Answers1

0

Thank you TechGnome. After banging my head for several days I used the example in the article you referenced. I needed to do an update so I changed the example from an Insert to an Update and it works sending a query string. I cant get it to work as a stored procedure?

I would prefer to send a stored proc rather than a query string. with that said, this solution does work:

param3.DbType = SQLDbType.NVarChar = 11

Changed it to

param3.DbType = DbType.String

param3.Size = 11

    Dim sStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("IC_CC.My.MySettings.constrIC_CC").ToString
    Dim oCN As New SqlClient.SqlConnection(sStr)
    oCN.Open()

    Using cmd As SqlClient.SqlCommand = oCN.CreateCommand()
        cmd.CommandText = "Update CCJOB set TRAN_NO = @TRAN_NO WHERE Job_No = @JOBNO"
        Dim param1 = cmd.CreateParameter()
        param1.ParameterName = "@JOBNO"
        param1.DbType = DbType.String
        param1.Direction = ParameterDirection.Input
        param1.Value = "235877"
        param1.Size = 6
        cmd.Parameters.Add(param1)

        Dim param3 = cmd.CreateParameter()
        param3.ParameterName = "@TRAN_NO"
        param3.DbType = DbType.String
        param3.Direction = ParameterDirection.Input
        param3.Value = "A111111111A"
        param3.Size = 11
        cmd.Parameters.Add(param3)
        cmd.ExecuteNonQuery()
    End Using
sThomas
  • 1
  • 1