January 31, 2022 at 12:37 am
Hi Everyone,
I am trying to encrypt a data column on SQL 2019 and decrypt on SQL 2016. I am not sure if it is possible to do this between SQL 2019 and SQL 2016. The data is replicated over to the SQL 2016 server. I am running the below on the two different servers to create and test the keys.
Thank you in advance.
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'passowrd';
GO
CREATE CERTIFICATE Cert_Column_Encrypt WITH SUBJECT = 'data col';
GO
CREATE SYMMETRIC KEY Sym_Column_Encrypt
WITH
KEY_SOURCE = 'cert2019',
ALGORITHM = AES_256,
IDENTITY_VALUE = 'key2019'
ENCRYPTION BY CERTIFICATE Cert_Column_Encrypt;
Running the following on the encrypting SQL 2019 works, but not on the SQL 2016.
OPEN SYMMETRIC KEY Sym_Column_Encrypt
DECRYPTION BY CERTIFICATE Cert_Column_Encrypt;
SELECT CONVERT(NVARCHAR(256),DECRYPTBYKEY( colname)), colname
FROM dbo.table_encrypt;
SELECT colname, CONVERT(NVARCHAR(512),DECRYPTBYKEYAUTOCERT(CERT_ID('Cert_Column_Encrypt') , null, colname))
AS 'EncryptedCol'
FROM dbo.table_encrypt;
January 31, 2022 at 4:21 am
By default, what you're doing is not possible, because the way SQL Server performs encryption work changed in SQL Server 2017. You can work around this with a trace flag. From the SQL Server docs: "Create Identical Symmetric Keys on Two Servers: (https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/create-identical-symmetric-keys-on-two-servers?view=sql-server-ver15):
"SQL Server 2016 uses the SHA1 hashing algorithm for its encryption work. Starting in SQL Server 2017, SHA2 is used instead. This means extra steps might be necessary to have your SQL Server 2017 installation decrypt items that were encrypted by SQL Server 2016. Here are the extra steps: [...]" (see article)
SQL Server 2019 has the same behavior as SQL Server 2017 - using SHA2 in places where SQL 2016 uses SHA1. Enabling the trace flag mentioned in the article on SQL 2019 will enable you to create encrypted values and objects on the SQL 2019 instance and have the decrypted on the SQL 2016 instance.
You will need to re-create any keys that you created on the SQL 2019 instance before you enable the trace flag. You can ignore the stuff about patching to a CU, that's only for SQL2017. SQL 2019 shipped this way.
Eddie Wuerch
MCM: SQL
January 31, 2022 at 4:32 pm
Thank you for your reply. I will try that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply