none
Encryption inserting NULL values. RRS feed

  • Question

  • Greetings. I recently moved a DB that holds DBA service account passwords from a 2012 instance to 2019. The passwords are encrypted using column level encryption. When I run a sproc to decrypt the password and return the unencrypted value all is well. However,  when I insert new data into the table the password into the table it gets stored as a NULL value. 

    Google tells me the two main reasons for this are 1) The symmetric key is not open. We're running a sproc that handles this, and that didn't change between versions. 2) ansi_padding being off. As such I did a "set ansi_padding on alter table foo alter column foo" and that didn't fix it either. 

    I should note that the migration itself wasn't the smoothest, and those woes can be found here. However, I have the luxury of simply "rebuilding" this encryption column if needed. The table is tiny and I'm the only FTE that uses it, all I care about is resolving the issue. 

    Ideas?


    Thanks in advance! ChrisRDBA

    Friday, July 10, 2020 11:12 PM

All replies

  • Hi

    https://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/

    Go through the url and read the last 11 number of point  may help you

    Accessing the Encrypted Data

    All the read access users will see the encrypted values while they do a select on table. A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values. However they do not receive any errors.

    Thanks and Regards

    Saturday, July 11, 2020 7:23 AM
  • Hi ChrisRDBA,

    Have a nice day!
    Is there any update on this case?
    Please feel free to drop us a note if there is any update.


    Best Regards,
    Cris


    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 13, 2020 9:08 AM
  • Hi

    https://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/

    Go through the url and read the last 11 number of point  may help you

    Accessing the Encrypted Data

    All the read access users will see the encrypted values while they do a select on table. A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values. However they do not receive any errors.

    Thanks and Regards

    Thanks, but I'm logged in as myself w SA perms. Also note this only happens w new entries, anything existing is fine. 


    Thanks in advance! ChrisRDBA


    • Edited by ChrisRDBA Monday, July 13, 2020 9:26 PM
    Monday, July 13, 2020 9:15 PM
  • Hi ChrisRDBA,

    Please make sure to Open Symmetric Key before inserting data.

    For the Column Level Encryption, you have to ensure that we always open Symmetric key before starting the Encryption(update, insert)/Decryption(select)., otherwise the null value will appear.

    Best Regards,
    Cris

    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.

    Tuesday, July 14, 2020 1:48 AM
  • Hi ChrisRDBA,

    Please make sure to Open Symmetric Key before inserting data.

    For the Column Level Encryption, you have to ensure that we always open Symmetric key before starting the Encryption(update, insert)/Decryption(select)., otherwise the null value will appear.

    Best Regards,
    Cris

    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.

    I have it opened, as noted in my post above. 


    Thanks in advance! ChrisRDBA

    Tuesday, July 14, 2020 3:45 PM
  • Would it make sense to decrypt the column, the re-encrypt it? 

    Thanks in advance! ChrisRDBA

    Tuesday, July 14, 2020 3:47 PM
  • Would it make sense to decrypt the column, the re-encrypt it? 

    If the problem is that new data does not get encrypted, it's not going help to re-encrypt the old data.

    I guess we need to see your code to be able to understand what is going on.


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

    Tuesday, July 14, 2020 5:06 PM
  • Turns out I'd totally over complicated what was required here, and the issue is now resolved. There's a new issue but I'll start a new thread if needed. All I needed to do after the backup/ restore was the following and all was well:

    open MASTER KEY deCRYPTION BY PASSWORD = 'myPassword'
    alter master key add encryption by service master key
    close master key
    

    Admins if you want to delete this thread it's fine w me, thanks for everyone's input. 


    Thanks in advance! ChrisRDBA

    Tuesday, July 14, 2020 9:20 PM
  • Hi ChrisRDBA,

    I am glad to hear that you have solved your problem. Thank you for your reply very much. 

    Based on your answer, I searched the article OPEN MASTER KEY (Transact-SQL), It mentions:
    When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). 

    In order to close this thread, please kindly mark your reply with the solution as answer. By doing so, it will benefit all community members who are having this similar issue.  

    Thanks for your contribution.

    Best regards,
    Cris


    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 15, 2020 1:09 AM
  • Hi ChrisRDBA,

    Have a nice day!

    In order to close this thread, please kindly mark your reply with the solution as answer. By doing so, it will benefit all community members who are having this similar issue. 

    Thanks for your contribution very much.

    Best Regards,
    Cris


    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.

    Friday, July 17, 2020 1:22 AM