none
SQL Server Always Encrypted Data Update RRS feed

  • Question

  • Hello,

    I got a problem updating data in an SQL Server 2016. The table data was updatet to be encrypted with the "always encrypted wizard" in the SMSS. I'm using a stored procedure to update the data.

    The table has existing data which was encrypted and now the application should work, but there are errors executing the stored proc from the application.

    I can update the data in the SMSS with the stored procedure, everything is working fine (parameters are encrypted by SSMS)

    I can create a new table and update the encrypted data.

    I can't use the existing stored proc, with the following error:

    "System.Data.SqlClient.SqlException: 'Operand type clash: bigint is incompatible with bigint encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', "

      db.CommandType = CommandType.StoredProcedure;
    
                var parameters = new HashSet<IDataParameter>(db.PropertiesToParameter(method, db.CreateParameter("@instituteID", method.Institute.Id)));
    
                parameters.RemoveWhere(p => Regex.IsMatch(p.ParameterName, @"accountNumber|ownerFirstName|ownerLastName|iban", RegexOptions.IgnoreCase));
    
                var p_accountNumber = db.CreateParameter("@AccountNumber", method.AccountNumber);
                p_accountNumber.DbType = DbType.Int64;
                parameters.Add(p_accountNumber);
    
                var p_fn = (SqlParameter)db.CreateParameter("@ownerFirstName", method.OwnerFirstName);
                p_fn.DbType = DbType.AnsiStringFixedLength;
                p_fn.Size = 255;
                parameters.Add(p_fn);
    
                var p_ln = (SqlParameter)db.CreateParameter("@ownerLastName", method.OwnerFirstName);
                p_ln.DbType = DbType.AnsiStringFixedLength;
                p_ln.Size = 255;
                parameters.Add(p_ln);
    
                var p_iban = (SqlParameter)db.CreateParameter("@iban", method.Iban);
                p_iban.DbType = DbType.AnsiStringFixedLength;
                p_iban.Size = 50;
                parameters.Add(p_iban);
    
    
                
                db.ExecuteNonQuery("[dbo].[PaymentMethodsUpdate]", parameters.ToArray());

    executing the following code works:

        db.CommandType = System.Data.CommandType.Text;
                var data = db.GetDataRow("Select * from dbo.test");
                db.CommandType = System.Data.CommandType.StoredProcedure;
    
              
    
                var p1 = db.CreateParameter("@value", 102);
                p1.DbType = DbType.Int64;
    
                var p2 = (SqlParameter)db.CreateParameter("@name", "from test");
                p2.DbType = DbType.AnsiStringFixedLength;
                p2.Size = 50;
    
    
    
    
                var res = db.ExecuteNonQuery("[dbo].[testInsert]",
                   p2, p1
                    );

    all the encrypted parameters are checked and equal (case etc.)

    thank you for your help

    Monday, July 15, 2019 5:42 AM

All replies

  • Hello,

    I got a problem updating data in an SQL Server 2016. The table data was updatet to be encrypted with the "always encrypted wizard" in the SMSS. I'm using a stored procedure to update the data.

    The table has existing data which was encrypted and now the application should work, but there are errors executing the stored proc from the application.

    I can update the data in the SMSS with the stored procedure, everything is working fine (parameters are encrypted by SSMS)

    I can create a new table and update the encrypted data.

    I can't use the existing stored proc, with the following error:

    "System.Data.SqlClient.SqlException: 'Operand type clash: bigint is incompatible with bigint encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', "

      db.CommandType = CommandType.StoredProcedure;
    
                var parameters = new HashSet<IDataParameter>(db.PropertiesToParameter(method, db.CreateParameter("@instituteID", method.Institute.Id)));
    
                parameters.RemoveWhere(p => Regex.IsMatch(p.ParameterName, @"accountNumber|ownerFirstName|ownerLastName|iban", RegexOptions.IgnoreCase));
    
                var p_accountNumber = db.CreateParameter("@AccountNumber", method.AccountNumber);
                p_accountNumber.DbType = DbType.Int64;
                parameters.Add(p_accountNumber);
    
                var p_fn = (SqlParameter)db.CreateParameter("@ownerFirstName", method.OwnerFirstName);
                p_fn.DbType = DbType.AnsiStringFixedLength;
                p_fn.Size = 255;
                parameters.Add(p_fn);
    
                var p_ln = (SqlParameter)db.CreateParameter("@ownerLastName", method.OwnerFirstName);
                p_ln.DbType = DbType.AnsiStringFixedLength;
                p_ln.Size = 255;
                parameters.Add(p_ln);
    
                var p_iban = (SqlParameter)db.CreateParameter("@iban", method.Iban);
                p_iban.DbType = DbType.AnsiStringFixedLength;
                p_iban.Size = 50;
                parameters.Add(p_iban);
    
    
                
                db.ExecuteNonQuery("[dbo].[PaymentMethodsUpdate]", parameters.ToArray());

    executing the following code works:

        db.CommandType = System.Data.CommandType.Text;
                var data = db.GetDataRow("Select * from dbo.test");
                db.CommandType = System.Data.CommandType.StoredProcedure;
    
              
    
                var p1 = db.CreateParameter("@value", 102);
                p1.DbType = DbType.Int64;
    
                var p2 = (SqlParameter)db.CreateParameter("@name", "from test");
                p2.DbType = DbType.AnsiStringFixedLength;
                p2.Size = 50;
    
    
    
    
                var res = db.ExecuteNonQuery("[dbo].[testInsert]",
                   p2, p1
                    );

    all the encrypted parameters are checked and equal (case etc.)

    thank you for your help

    Friday, July 12, 2019 9:58 AM
  • IMHO, you have SQL Server issues that should be discussed at the SQL Server forums.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver

    Friday, July 12, 2019 8:27 PM
  • If your stored procedure was created before you
     encrypted your column, you will need to refresh metadata for your stored procedure as follows

    Use [Database]
    GO    
    --Do this for all stored procedures
    EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[SPNAME]'

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 15, 2019 6:41 AM
    Answerer
  • Hi Roman Wienicke, 

    Thank you for posting here.

    Based on your question, I try to make a test on my side, but I need more information.

    Please provide some code about ‘db’, ‘method’ and the stored procedure, and this will help us analyze your problem.

    We are waiting for your update.  

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 15, 2019 7:20 AM
  • Hi Xingyu Zhao,

    the db is a wrapper class for database connection, you can find the code here: https://github.com/romanWienicke/dbconnection

    Basically it wraps all db connections and initializes the connection with the BaseManager class.

    I setup the encryption with the CMK certificate stored as computer certificate.

    

    All the data etc. existed before the encryption (made with SMSS). I also called 

    EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[SPNAME]'

    but the issues didn't go away.

    Creating another table inside the database worked and I was able to insert data.

    I can also select data (with SP) from the table without problems, when I call the update procedure there is the following error:

    Operand type clash: bigint is incompatible with bigint encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CMK_LottoManager', column_encryption_key_database_name = 'LottoManager_Update')
    Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.


    The c# test:

    [TestCase]
            public void SavePaymentMethodTest()
            {
                var pm = new PaymentMethodBank {
                    Id= 77378,
                    AccountNumber = 1234567890,
                    IsPrimary=true,
                    IsValid = true,
                    OwnerFirstName= $"UserFirsName",
                    OwnerLastName= "UserLastName",
                    Type=PaymentType.Bank,
                    Institute = new PaymentInstituteBank { Id= 1570 },
                    Bic = "BIC12345",
                    Iban = "NO123456789"
                };
    
                SavePaymentMethod(pm);
            }
    
    public void SavePaymentMethod(PaymentMethodBank method)
            {
                db.CommandType = CommandType.StoredProcedure;
    
                var parameters = new HashSet<IDataParameter>(db.PropertiesToParameter(method, db.CreateParameter("@instituteID", method.Institute.Id)));
    
                parameters.RemoveWhere(p => Regex.IsMatch(p.ParameterName, @"accountNumber|ownerFirstName|ownerLastName|iban", RegexOptions.IgnoreCase));
    
                var p_accountNumber = db.CreateParameter("@accountNumber", method.AccountNumber);
                p_accountNumber.DbType = DbType.Int64;
                parameters.Add(p_accountNumber);
    
                var p_fn = (SqlParameter)db.CreateParameter("@ownerFirstName", method.OwnerFirstName);
                p_fn.DbType = DbType.AnsiStringFixedLength;
                p_fn.Size = 255;
                parameters.Add(p_fn);
    
                var p_ln = (SqlParameter)db.CreateParameter("@ownerLastName", method.OwnerFirstName);
                p_ln.DbType = DbType.AnsiStringFixedLength;
                p_ln.Size = 255;
                parameters.Add(p_ln);
    
                var p_iban = (SqlParameter)db.CreateParameter("@iban", method.Iban);
                p_iban.DbType = DbType.AnsiStringFixedLength;
                p_iban.Size = 50;
                parameters.Add(p_iban);
    
    
                
                db.ExecuteNonQuery("[dbo].[PaymentMethodsUpdate]", parameters.ToArray());
            }

    The Stored Procedure:

    CREATE TABLE [dbo].[PaymentMethods](
    	[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    	[accountNumber] [bigint] ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CMK_LottoManager], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    	[confirmationDate] [smalldatetime] NULL,
    	[isPrimary] [bit] NOT NULL,
    	[isValid] [bit] NOT NULL,
    	[ownerFirstName] [varchar](255) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CMK_LottoManager], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    	[ownerLastName] [varchar](255) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CMK_LottoManager], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    	[type] [int] NOT NULL,
    	[instituteId] [int] NOT NULL,
    	[bic] [varchar](50) NULL,
    	[iban] [varchar](50) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CMK_LottoManager], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL,
    
     CONSTRAINT [PK_PaymentMethods] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Monday, July 15, 2019 7:46 AM
  • Yes the SP was created before, but I already executed 

    EXEC sys.sp_refresh_parameter_encryption @name = '[dbo].[SPNAME]'

    and I also tried to drop / create the SP. 

    Some more interesting facts:

    • I can execute the SP in SMSS with the "encrypt Parameters" Query Option on.
    • I can execute the SQL of the SP from my application (instead of using CommandType.StoredProcedure using CommandType.Text and exetuting the SQL Code directly in c#.
    • I can select all data withou any issues (also with SP)

    thanks for any ideas...

    Tuesday, July 16, 2019 6:55 AM
  • Will an error occur when you execute the stored procedure directly?
    Tuesday, July 16, 2019 7:46 AM
  • No,

    in SMSS I can execute the stored procedure (with the query option "enable Parameterization for always encrypted" on. 

    thank you

    Tuesday, July 16, 2019 7:49 AM
  • It looks that something  wrong with parameter definition. 

    Can you show us your procedure parameter definition, can you exactly match the parameters to the code .NET?

    Is that possible you have INT as a datatype in procedure but in .NET you defined as BIGINT?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, July 16, 2019 7:58 AM
    Answerer
  • Judging from your code, you have bundled SqlClient with some extra on top of it. This extra may be doing something which is not good for its own best. I would recommend that you use Profiler to see what is going on.

    Since you can run the procedure from SSMS with "encrypt parameters" active, the procedure itself seems to be good.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 16, 2019 7:58 AM
  • var p_accountNumber = (SqlParameter)db.CreateParameter("@accountNumber", method.AccountNumber);
                p_accountNumber.DbType = DbType.Int64;
                p_accountNumber.Direction = ParameterDirection.Input;
                parameters.Add(p_accountNumber);
    
                var p_fn = (SqlParameter)db.CreateParameter("@ownerFirstName", method.OwnerFirstName);
                p_fn.DbType = DbType.AnsiStringFixedLength;
                p_fn.Size = 255;
                p_fn.Direction = ParameterDirection.Input;
                parameters.Add(p_fn);
    
                var p_ln = (SqlParameter)db.CreateParameter("@ownerLastName", method.OwnerFirstName);
                p_ln.DbType = DbType.AnsiStringFixedLength;
                p_ln.Size = 255;
                p_ln.Direction = ParameterDirection.Input;
                parameters.Add(p_ln);
    
                var p_iban = (SqlParameter)db.CreateParameter("@iban", method.Iban);
                p_iban.DbType = DbType.AnsiStringFixedLength;
                p_iban.Size = 50;
                p_iban.Direction = ParameterDirection.Input;
                parameters.Add(p_iban);

    Tuesday, July 16, 2019 8:01 AM
  • Moreover ,please check the name of parameters @AccountNumber 

    should be exactly in the stored procedure (case sensitive) 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 8:02 AM
    Answerer
  • Hi, I already did, the names match those in the SP.... also checkt case sensitive errors - everything ist set as it should ...

    Tuesday, July 16, 2019 8:04 AM
  • Also datatypes matched exactly as in .net?

    For example.

    NVARCHAR(20)= p2.Size = 50;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 8:06 AM
    Answerer
  • yes,

    the first param is a bigint, which ist an Int64 as IDataparameter oder bigint as SQLParametr...

    the others match length and type

    Tuesday, July 16, 2019 8:09 AM
  • Can you define just for the testing stored procedure paaram as INT and /NET param as INT?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, July 16, 2019 8:11 AM
    Answerer
  • still the same problem... :-(

    Operand type clash: int is incompatible with int encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CMK_LottoManager', column_encryption_key_database_name = 'LottoManager_Update')
    Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.

    Tuesday, July 16, 2019 8:14 AM
  • When you run a client code turn on SQL Profiler and you will see a call 

    sp_describe_parameter_encryption , can you show us how it is looks like?

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-parameter-encryption-transact-sql?view=sql-server-2017


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Tuesday, July 16, 2019 8:23 AM
    Answerer
  • As I said, using Profiler is a good idea.

    Also, I would recommend that you write a test client that uses SqlClient directly and not using this db object, so you can get that out of the equation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 16, 2019 8:33 AM
  • Here is the profiler output with the error:

    

    Line 1 details:

    exec sp_describe_parameter_encryption N'EXEC [dbo].[PaymentMethodCreate] @instituteID=@instituteID, @gamblerId=@gamblerId, @iban=@iban, @Bic=@Bic, @SendForm=@SendForm, @IsValid=@IsValid, @ownerLastName=@ownerLastName, @ownerFirstName=@ownerFirstName, @ConfirmationDate=@ConfirmationDate, @Type=@Type, @accountNumber=@accountNumber, @IsPrimary=@IsPrimary, @Id=@Id OUTPUT',N'@instituteID bigint,@gamblerId int,@iban char(50),@Bic nvarchar(9),@SendForm bit,@IsValid bit,@ownerLastName char(255),@ownerFirstName char(255),@ConfirmationDate nvarchar(4000),@Type int,@accountNumber int,@IsPrimary bit,@Id bigint output'

    Line 2 details:

    declare @p13 bigint
    set @p13=0
    exec [dbo].[PaymentMethodCreate] @instituteID=1570,@gamblerId=200233,@iban='IBAN123456789                                     ',@Bic=N'Bic123456',@SendForm=0,@IsValid=1,@ownerLastName='Firstname                                                                                                                                                                                                                                                      ',@ownerFirstName='Firstname                                                                                                                                                                                                                                                      ',@ConfirmationDate=NULL,@Type=1,@accountNumber=123456,@IsPrimary=1,@Id=@p13 output
    select @p13

    The Error in the c# code:

    Operand type clash: int is incompatible with bigint encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CMK_LottoManager', column_encryption_key_database_name = 'LottoManager_Update')
    Incorrect parameter encryption metadata was received from the client. The error occurred during the invocation of the batch and therefore the client can refresh the parameter encryption metadata by calling sp_describe_parameter_encryption and retry.

    Tuesday, July 16, 2019 8:41 AM
  • Hm, what is the output from the call to sp_describe_parameter_encryption?

    When run this from SSMS, do you run it on the same machine?

    And, oh, what .NET version have you built your application for?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 16, 2019 8:51 AM
  • When run this from SSMS, do you run it on the same machine?
    Yes, the same machine, but the database is on another one. (SSMS works)

    And, oh, what .NET version have you built your application for?
    the application is on 4.5 but the tests run under 4.7.2 with the same results

    The output from the call to sp_describe_parameter_encryption?

    Tuesday, July 16, 2019 9:01 AM
  • @accountNumber int, should not be it BIGINT?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 9:04 AM
    Answerer
  • the account number is a bigint (long or int64) in the code and in the sp.
    Tuesday, July 16, 2019 9:14 AM
  • I see here 

    OUTPUT',N'@instituteID bigint,@gamblerId int,@iban char(50),@Bic nvarchar(9),@SendForm bit,@IsValid bit,@ownerLastName char(255),@ownerFirstName char(255),@ConfirmationDate nvarchar(4000),@Type int,@accountNumber int,@IsPrimary bit,@Id bigint 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 10:22 AM
    Answerer
  • meanwhile I figured something out something...

    do you know if there are any issues using a IDbConnection, IDataParameter? 

    it seems as the parameters are not encrypted:

    declare @p13 bigint
    set @p13=0
    exec [dbo].[PaymentMethodCreate] @instituteID=1570,@gamblerId=200233,@ownerLastName='Lastmame',@Bic=N'Bic123456',@SendForm=0,@IsValid=1,@ownerFirstName='Firstname',@accountNumber=123456,@ConfirmationDate=NULL,@Type=1,@iban='IBAN123456789',@IsPrimary=1,@Id=@p13 output
    select @p13

    ---

    another code works and in profiler it looks like this:

    declare @p1 int
    set @p1=90600
    exec [dbo].[PaymentMethodCreate] @id=@p1 output,@isPrimary=1,@isValid=1,@type=1,@instituteId=1570,@gamblerId=200233,@iban=0x011949F369DD8DCCC64F30B06DAEAA0D3FF6066E8B680BBED8E90A3B8312AB0BEC72A8CE89C9E05D3E6F9A29336EEDCF46212FAD82E20ADFB592895070AC638DCD5291084887D8DC9036208F9EC0C5FDAB,@accountNumber=0x016135A3AB08E205551338A6AC60B46A1F6EFBA1F1CD056186BADD8DFFC7C3C9BF2006B3F6A0083C41622D2453CF50513C097C703D85B704528FB4E1BCB4C309C8,@ownerFirstName=0x01DFD821799184A5C146D5BA331351B3B9927FDAC92D7FF284E36A51EB4E40106CCB6BD448115956E2C7ACF9F9493249BCD9C0897AE370818AB051F9ECE5F238FC,@ownerLastName=0x01DFD821799184A5C146D5BA331351B3B9927FDAC92D7FF284E36A51EB4E40106CCB6BD448115956E2C7ACF9F9493249BCD9C0897AE370818AB051F9ECE5F238FC
    select @p1

    Tuesday, July 16, 2019 10:46 AM
  • The output from the call to sp_describe_parameter_encryption?

    All columns are coming back as unencrypted for some reason.

    As Uri points out, in the call to sp_describe_parameter_encryption, @accountNumber is listed as int. If you manually change that to bigint, does the output change?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, July 16, 2019 10:56 AM
  • Now, I think we found the problem. What is connection string param?

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/develop-using-always-encrypted-with-net-framework-data-provider?view=sql-server-2017


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 16, 2019 11:05 AM
    Answerer
  • the connectionstring is setup with "...;Column Encryption Setting=enabled;"

    but I think I found the problem:

    I have a method creating SQL Parameters from object properties.

    without this method wich returns an SQLParameter[] everything is working, but when the parameters are created with this mehtod it does not work.

    Do you have any background why this does not work?

     public SqlParameter[] PropertiesToParameter(object target, params SqlParameter[] defaultParams)
            {
                List<SqlParameter> parameters;
    
                if (defaultParams == null)
                    parameters = new List<SqlParameter>();
                else
                    parameters = new List<SqlParameter>(defaultParams);
    
                Type type = target.GetType();
                PropertyInfo[] info = type.GetProperties();
    
                for (int i = 0; i < info.Length; i++)
                {
                    DbPropertyAttribute attr = GetDbDataParameterValue(info[i]);
    
                    if (attr == null) continue;
                    // Wert holen und falls null auf dbnull stellen
                    object value = info[i].GetValue(target, null);
    
                    if (value == null)
                        value = DBNull.Value;
    
                    parameters.Add(
                        CreateParameter(string.Format("@{0}", info[i].Name), value, attr.IsOutputParameter)
                        );
                }
                return parameters.ToArray();
            }

    Tuesday, July 16, 2019 11:20 AM
  • I got it... the problem is / was the parameter naming...

    My initial thinking was that the camel case naming of the parameters is only important for the encrypted papameters BUT -- it's important for all parameters, no matter if the need to be encrypted or not. 

    So when a stored proc is called and any of the parameters is wrong (e.g. myIntParam - myintparam) the error above is thrown.

    thank you for four help...

    Tuesday, July 16, 2019 12:21 PM
  • Hi Roman Wienicke,

    Thanks for your feedback.

    Since this thread is duplicate with the following link, I will merge it.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e5112188-5986-4d49-88b6-471afbcabfe2/sql-server-always-encrypted-data-update?forum=transactsql

    Thank you for your support.

    Best regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 17, 2019 2:22 AM
  • I think it is more related to .Net. Please post your issue in the corresponding forum.
    Wednesday, July 17, 2019 9:17 AM