0

I have encrypted existing data using the SQL Server 2016 always encrypt method, one of column we have is NULLABLE but while insert from screen its not accepting either NULL or empty String. how to make it work if encrypted column is not mandatory on screen?

Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13
Windows10
  • 51
  • 9
  • Can you post some code? – Nikhil Vithlani - Microsoft May 16 '17 at 23:30
  • Also what do you mean when you say insert from screen? – Nikhil Vithlani - Microsoft May 16 '17 at 23:30
  • Suppose i have SSN column in my table and its encrypted column. using my .net code(Ado.net) i am passing SSN as parameter to procedure to insert into table. its working fine if i have some value in it but if its NULL or empty then its showing error. – Windows10 May 17 '17 at 01:59
  • string _ssn ="" insert into table1 (ssn) values(_ssn) error - 'Operand type clash: nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'Database') is incompatible with nvarchar(9) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = Database')' – Windows10 May 17 '17 at 02:16
  • I have provided a sample below on how to pass parameters to SP when using always encrypted. If you share your code, schema and stored procedure, I can help point out the problem. – Nikhil Vithlani - Microsoft May 17 '17 at 18:32

1 Answers1

0

The error that you are seeing is incorrect, please ensure that you are passing the parameter correctly. Here is some sample code on how to do so.

Schema:

CREATE TABLE [dbo].[SO](
    [ssn] [nvarchar](9) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
)

GO

CREATE PROCEDURE dbo.insertSP @ssn nvarchar(9)
AS

INSERT INTO [dbo].[SO] ([SSN]) VALUES (@SSN);
GO

C# code:

        SqlConnectionStringBuilder strbldr = new SqlConnectionStringBuilder();
        strbldr.DataSource = ".";
        strbldr.InitialCatalog = @"exptdb";
        strbldr.IntegratedSecurity = true;
        strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

        string ssn = "";

        using (var conn = new SqlConnection(strbldr.ConnectionString))
        using (var command = conn.CreateCommand()) {

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = @"dbo.insertSP";

            SqlParameter paramSSN = command.CreateParameter();
            paramSSN.ParameterName = "@ssn";
            paramSSN.SqlDbType = SqlDbType.NVarChar;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Value = ssn;
            paramSSN.Size = 9;
            command.Parameters.Add(paramSSN);

            conn.Open();
            command.ExecuteNonQuery();
        }

Note, that in the scenario mentioned above, if

string ssn = "";

then the query succeeds, however if 

string ssn = null;

you should see a failure on execution

Additional information: Procedure or function 'insertSP' expects parameter '@ssn', which was not supplied.

This failure will occur even when ssn column is plaintext

You can insert null value in the encrypted column as follows, since null values are not encrypted:

        using (var conn = new SqlConnection(strbldr.ConnectionString))
        using (var command = conn.CreateCommand()) {

            command.CommandText = @"INSERT INTO [dbo].[SO] ([SSN]) VALUES (null)";

            conn.Open();
            command.ExecuteNonQuery();
        }
Nikhil Vithlani - Microsoft
  • 797
  • 5
  • 13