I restored a masterkey, and then created a certificate and symmetric key SSMS with: use AdventureWorks2022 DROP MASTER KEY RESTORE MASTER KEY FROM FILE = 'c:\stuff\master_key_backup.mas' DECRYPTION BY PASSWORD = 'ASDF$E£%^IJYK*&234' ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD' OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'; -- create new certificate and symkey to encode a new column CREATE CERTIFICATE Certificate_test2 WITH SUBJECT = 'Protect my second data'; CREATE SYMMETRIC KEY SymKey_test2 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test2; I then encrypted a column from a table using: open symmetric key SymKey_test2 decryption by certificate Certificate_test2 update AdventureWorks2022.[Person].[EmailAddress] set email_encrypt = ENCRYPTBYKEY(key_guid('symkey_test2'), emailaddress) from AdventureWorks2022.[Person].[EmailAddress] close symmetric key symkey_test2 and tested that it worked with: open symmetric key symkey_test2 decryption by certificate certificate_test2 select convert(nvarchar, decryptbykey(email_encrypt)) as 'decrypted email',* from AdventureWorks2022.[Person].[EmailAddress] close symmetric key symkey_test2 I put the code that worked in a new script, but came with the error: Please create a master key in the database or open the master key in the session before performing this operation. I can get it to work if I include the line: OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';, but I do not want to expose my password in a new script that will be called in a stored procedure and excel data calls. Is there a way to open a master key without exposing the password? The final goal is to have this set up in a stored procedure that is called by excel data connection, but of course, I do not want the final users to have access to the master password.

In SQL server, I created a symmetric key and encoded a column as per [An overview of the column level SQL Server encryption](https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/) use AdventureWorks2022 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1'; CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data'; CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test; -- encode using symmetric keys ALTER TABLE AdventureWorks2022.HumanResources.Employee ADD BankACCNumber_encrypt varbinary(MAX) OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY CERTIFICATE Certificate_test; UPDATE AdventureWorks2022.HumanResources.Employee SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), NationalIDNumber) FROM AdventureWorks2022.HumanResources.Employee; CLOSE SYMMETRIC KEY SymKey_test; select BankACCNumber_encrypt,* from AdventureWorks2022.HumanResources.Employee I decode succeessfully with: -- decode OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY CERTIFICATE Certificate_test; SELECT nationalIDNumber,BankACCNumber_encrypt AS 'Encrypted data', CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number' FROM AdventureWorks2022.HumanResources.Employee I backed up the symmetric key, pretended it was lost/dropped, and restored the key using: OPEN SYMMETRIC KEY SymKey_test DECRYPTION BY CERTIFICATE Certificate_test BACKUP SYMMETRIC KEY SymKey_test TO FILE = 'C:\stuff\Sym_key_backup.cer' ENCRYPTION BY PASSWORD = 'A4FR^hhjg££fhj' CLOSE SYMMETRIC KEY SymKey_test; DROP SYMMETRIC KEY SymKey_test; RESTORE SYMMETRIC KEY key_name FROM FILE = 'C:\stuff\Sym_key_backup.cer' DECRYPTION BY PASSWORD = 'A4FR^hhjg££fhj' ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4' I then try to decode, using: OPEN SYMMETRIC KEY key_name DECRYPTION BY CERTIFICATE Certificate_test; SELECT nationalIDNumber,BankACCNumber_encrypt AS 'Encrypted data', CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number' FROM AdventureWorks2022.HumanResources.Employee but it does not decrypt correctly, and I get error: > The key is not encrypted using the specified decryptor. How can I use the restored symmetric key to decode my encoded column?

This post is similar to another question listed here. https://dba.stackexchange.com/questions/319755/encrypting-data-by-open-symmetric-key-by-asymmetric-key-created-with-azure-provi We are also facing the similar problem on our PROD server randomly, did anyone find a fix for this one. Below is the SQL statement we are executing with timestamp, randomly the time taken between the statement print 'before open ....' and print 'after open ....' is more than a minute and at a time is less than a second. print 'before open ' + convert(varchar(50),getdate(),114) OPEN SYMMETRIC KEY SYMMETRIC_KEY_NAME BY ASYMMETRIC KEY ASYMMETRIC_KEY_MECHANISM; print 'after open ' + convert(varchar(50),getdate(),114) -- SQL Select query CLOSE SYMMETRIC KEY SYMMETRIC_KEY_NAME print 'after close ' + convert(varchar(50),getdate()) Few findings from our end. There is no error reported, just a continous info logged in eventviewer > The description for Event ID 0 from source SQL Server Connector for > Microsoft Azure Key Vault cannot be found. Either the component that > raises this event is not installed on your local computer or the > installation is corrupted. You can install or repair the component on > the local computer. > > If the event originated on another computer, the display information > had to be saved with the event. > > The following information was included with the event: > > Vault Name: vaultname.vault.azure.net Operation: Constructor Key Name: > Message: [error:0, info:0, state:0] The Operation was successful. > > the message resource is present but the message is not found in the > string/message table There are outbound network calls in firewall logs which connects to Microsoft Azure, and there are continous denials (resets) from Microsoft. The same setup works on our lower Test environment, just PROD is affected. Already raised this issue in another forum. https://learn.microsoft.com/en-us/answers/questions/1275244/sql-decryption-randomly-slow?page=1&orderby=Helpful&comment=answer-1245040#newest-answer-comment

Hope you're having a good day. This is in continuation to [my previously posted question in Salesforce StackExchange](https://salesforce.stackexchange.com/questions/388321/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via) . I was suggested to seek help at **DBA SE** in order to find a way to extract Symmetric key from MS SQL in order to use it for decrypting the data in Salesforce Apex using its Crypto class. Or otherwise, an alternative. --- Recently, I have been working on a project and haven't been able to find a solution to this problem. What I want to achieve is: To decrypt the data in Apex using [the crypto class](https://developer.salesforce.com/docs/atlas.en-us.apexref.meta/apexref/apex_classes_restful_crypto.htm#apex_System_Crypto_encrypt) . The data is encrypted using the AES_256 algorithm in MS SQL Server. Ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-transact-sql?view=sql-server-ver16 MS SQL server has its own functionality to encrypt/decrypt data using symmetric/asymmetric keys. The problem is, unlike Apex or any other language, we cannot view the generated AES key. The server stores the key such that it is self-encrypted and can only be accessed within the database by its name. This is how the data is encrypted in MS SQL: Can be tested at [MS SQL Online IDE](https://sqliteonline.com/)

--Creating a table in db to store data. It has a column to store encrypted password as well.
--
CREATE TABLE encryption_test ( name VARCHAR(20), email VARCHAR(40), password VARCHAR(45), password_encrypted VARBINARY(MAX))


-- Create a DB Master key as pre requisit for symmetric key creation.
--
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncPass@123#';


-- Create a self-signed cert
--
CREATE CERTIFICATE EncryptCert1 WITH SUBJECT = 'EncryptCert1';


-- Create a symmteric key with AES_256 with self-signed cert
--
CREATE SYMMETRIC KEY EncryptKey1 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE EncryptCert1;


-- Create a UDF to easily encrypt/decrypt any data

--Encryption function
--
CREATE FUNCTION Encrypt
(  
    @ValueToEncrypt varchar(max)  
)  
RETURNS varbinary(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varbinary(max)  
    SET @Result = EncryptByKey(Key_GUID('EncryptKey1'), @ValueToEncrypt)  
    -- Return the result of the function  
    RETURN @Result  
END


-- Decryption function
--
CREATE FUNCTION Decrypt
(  
    @ValueToDecrypt varbinary(max)  
)  
RETURNS varchar(max)  
AS  
BEGIN  
    -- Declare the return variable here  
    DECLARE @Result varchar(max)  
    SET @Result = DecryptByKey(@ValueToDecrypt)  
    -- Return the result of the function  
    RETURN @Result  
END


-- Create a server stored procedure to easily access encryption/decryption key.
--
CREATE PROCEDURE sp_OpenEncryptionKeys  
AS  
BEGIN  
    SET NOCOUNT ON;  

    BEGIN TRY  
        OPEN SYMMETRIC KEY EncryptKey1  
        DECRYPTION BY CERTIFICATE EncryptCert1 
    END TRY  
    BEGIN CATCH  
        --catch
    END CATCH  
END

-- Insert some data in table
--
EXEC sp_OpenEncryptionKeys  --run procedure to access key.
INSERT INTO encryption_test VALUES ( 'MyName', 'MyName@gmail.com', 'MyPass123', dbo.Encrypt('MyPass123') );  --dbo.Encrypt encryps the data in BLOB

-- Finally, access the encrypted data.
--
EXEC sp_OpenEncryptionKeys
SELECT name, email, password, password_encrypted, pass_base64
FROM encryption_test
cross apply (select password_encrypted '*' for xml path('')) T (pass_base64);

--OUTPUT DATA
----------------------------------------------------------------
--| name: MyName 
--| email: MyName@gmail.com
--| password: MyPass123
--| password_encrypted: -0,114,0,42,94,23,84,68,157,243,45,3,148,238,239,41,2,0,0,0,254,67,113,151,205,120,22,129,189,211,250,94,72,151,11,120,215,250,241,70,193,107,75,191,219,153,101,6,228,84,203,130,84,151,13,71,146,95,234,10,233,6,77,132,176,46,52,240
--| pass_base64: AHIAKl4XVESd8y0DlO7vKQIAAAD+Q3GXzXgWgb3T+l5Ilwt41/rxRsFrS7/bmWUG5FTLglSXDUeSX+oK6QZNhLAuNPA=
----------------------------------------------------------------

Now the data is encrypted in MS SQL server and exported in bas64 format in a CSV. Following suggestion from [identigral](https://salesforce.stackexchange.com/users/4142/identigral) in my [previous question](https://salesforce.stackexchange.com/questions/388321/decrypt-symmetrically-asymmetrically-encrypted-data-in-ms-sql-t-sql-server-via) , I manipulate the output data by cutting out all the junk to arrive at IV + encrypted data.

GUID: 0072002a5e1754449df32d0394eeef29

VER: 02000000

IV: fe437197cd781681bdd3fa5e48970b78
IV_b64: /kNxl814FoG90/peSJcLeA==

Header: d7faf146c16b4bbf

Data: db996506e454cb8254970d47925fea0ae9064d84b02e34f0
Data_b64: 25llBuRUy4JUlw1Hkl/qCukGTYSwLjTw

However, still missing out the Symmetric key to be used for decryption inside Salesforce Apex.

Blob key = Blob.valueOf('???????????????????');  //no such way to export key from T-SQL
        Blob encrypted = EncodingUtil.base64Decode('25llBuRUy4JUlw1Hkl/qCukGTYSwLjTw');

        Blob decrypted = Crypto.decryptWithManagedIV('AES256', key, encrypted);
        
        String decryptedString = decrypted.toString(); 
        System.debug('decrypted String: '+decryptedString);

I gave it a try by creating a symmetric key encrypted by password to use the password as the **key**. But, it then returns **error: *last block incomplete in decryption*.**

-- Create a symmetric key with AES_256 with a password.
--
CREATE SYMMETRIC KEY EncryptKey1   
WITH ALGORITHM = AES_256  
ENCRYPTION BY PASSWORD = '12345qwertyu@!#$%asdEWQAS#$r4cfr';  -- 32 bytes, to be used as the key.

After encoding and manipulating the same password, this is the result:

PASSWORDED

GUID: 005d15df67da1d4199bb777f779acb26

VER: 02000000

IV: b6175270ad8b7a259531206018591c4f
IV_b64: thdScK2LeiWVMSBgGFkcTw==

Header: a0a1bb88411dea02


Data: 19b397e8335e6f5934b31814ce62645395c2e76408667160
Data_b64: GbOX6DNeb1k0sxgUzmJkU5XC52QIZnFg

Using the password as **KEY** in Apex Class. Returns above-mentioned error.

Blob key = Blob.valueOf('12345qwertyu@!#$%asdEWQAS#$r4cfr');  //using password as the key.
        Blob iv = EncodingUtil.base64Decode('thdScK2LeiWVMSBgGFkcTw==');
        Blob encrypted = EncodingUtil.base64Decode('GbOX6DNeb1k0sxgUzmJkU5XC52QIZnFg');

        Blob decrypted = Crypto.decrypt('AES256', key, iv, encrypted);
        
        String decryptedString = decrypted.toString(); 
        System.debug('decrypted String: '+decryptedString);

Therefore, need a solution in some way to either export the key string from MS SQL. Or otherwise an alternate to decrypt the T-SQL encrypted data in Apex Class.

I found that for each encryption of a symmetric key an entry in sys.key_encryptions is present and at least for ENCRYPTION BY CERTIFICATE-type entries I can use the certificate of the specified thumbprint to decrypt the crypt_property: SELECT SK.name, SK.key_length, SK.algorithm_desc, [DECRYPTBYCERT(C.certificate_id, KE.crypt_property)] = DECRYPTBYCERT(C.certificate_id, KE.crypt_property), SK.key_guid FROM sys.symmetric_keys SK INNER JOIN sys.key_encryptions KE ON KE.key_id = SK.symmetric_key_id INNER JOIN sys.certificates C ON C.thumbprint = KE.thumbprint WHERE KE.crypt_type = 'EPUC' Now I wonder if I can recreate the symmetric key from this information inside another database. For CREATE CERTIFICATE you can use FROM BINARY = 0x... WITH PRIVATE KEY ( BINARY = 0x...), but it seems the closest I get is CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = AES_256, KEY_SOURCE = 'password to derive key', IDENTITY_VALUE = 'password to derive guid' ENCRYPTION BY CERTIFICATE TestCert; Can anybody help me write a working statement to recreate the symmetric key? I guess I will either need a way to generate a password that derives the same key (and ideally the same guid) again or find another option to specify the 'plaintext' symmetric key (EKM?). The original KEY_SOURCE- and IDENTITY_VALUE-properties are unknown to me. ### Appendix: Demonstration, that key is valid Prerequisites on SQL Server (I used SQL Server 2016): CREATE CERTIFICATE TestCert WITH SUBJECT = 'TestCert'; CREATE SYMMETRIC KEY TestKey WITH ALGORITHM = AES_256, KEY_SOURCE = 'password to derive key', IDENTITY_VALUE = 'password to derive guid' ENCRYPTION BY CERTIFICATE TestCert; Use query from above to retrieve AES-Key: || name || key_length || algorithm_desc || DECRYPTBYCERT(C.certificate_id, KE.crypt_property) || key_guid || | TestKey | 256 | AES_256 | 0xAF8C9D22CE9B6BBE5E4207608A2D4D9494B8532AD2F358C382F02BB3303A36E6 | 01DE2200-EF6C-3C1A-0F3A-889881EF77E7 | Encrypt something DECLARE @KeyGuid uniqueidentifier = '01DE2200-EF6C-3C1A-0F3A-889881EF77E7'; OPEN SYMMETRIC KEY TestKey DECRYPTION BY CERTIFICATE [TestCert]; SELECT ENCRYPTBYKEY(@KeyGuid, N'blubb'); -- yields 0x0022DE016CEF1A3C0F3A889881EF77E70100000091B6D30FAB2FFBC7FFEAE32D3A52A6F8AEA2750CEBEBE6BCE33782AEC52A4E31B04FFBCCD06470242E585AA4261BC3B1 CLOSE SYMMETRIC KEY TestKey; Then this [LINQPad](http://www.linqpad.net/)-query (C# Program) can be used to decrypt the encrypted values again (Strictly proof-of-concept quality!): void Main() { AesManaged myAes = new AesManaged(); // Override the cipher mode and padding. Key and IV are passed in below myAes.Mode = CipherMode.CBC; myAes.Padding = PaddingMode.None; var Key = StringToByteArray("0xAF8C9D22CE9B6BBE5E4207608A2D4D9494B8532AD2F358C382F02BB3303A36E6"); // Byte array representing the key var encrypted = StringToByteArray("0x0022DE016CEF1A3C0F3A889881EF77E70100000091B6D30FAB2FFBC7FFEAE32D3A52A6F8AEA2750CEBEBE6BCE33782AEC52A4E31B04FFBCCD06470242E585AA4261BC3B1"); // Following fields are according to https://blogs.msdn.microsoft.com/sqlsecurity/2009/03/30/sql-server-encryptbykey-cryptographic-message-description/ var KeyGUID = new Guid(encrypted.Slice(0, 16)).Dump("Guid"); var EncryptionHeader = encrypted.Slice(16, 4); //var Headerversion = EncryptionHeader.Slice(0, 1); // Could check for 0x01 //var ReservedBytes = EncryptionHeader.Slice(1, 3); // currently all 0x00 var EncryptedMessage = encrypted.Slice(20); var InitializationVector = EncryptedMessage.Slice(0, myAes.BlockSize / 8); var EncryptedData = EncryptedMessage.Slice(myAes.BlockSize / 8); // We have everything to perform the decryption var decryptor = myAes.CreateDecryptor(Key, InitializationVector); var InnerMessage = decryptor.TransformFinalBlock(EncryptedData, 0, EncryptedData.Length); // Encrypted result contaisn more fields var InnerMessageHeader = InnerMessage.Slice(0, 8); //var MagicNumber = InnerMessageHeader.Slice(0, 4); // Could check for 3131961357 var IntegrityBytesLength = BitConverter.ToUInt16(InnerMessageHeader.Slice(4, 2), 0).Dump("IntegrityBytesLength"); var PlaintextLength = BitConverter.ToUInt16(InnerMessageHeader.Slice(6, 2), 0).Dump("PlaintextLength"); //var IntegrityBytes = InnerMessage.Slice(8, IntegrityBytesLength).Dump("IntegrityBytes"); // Not used without "authenticator" in ENCRYPTBYKEY // Our Plaintext (finally!) - Encoding needs to be changed if input to ENCRYPTBYKEY is not nvarchar var Plaintext = new string(Encoding.Unicode.GetChars(InnerMessage.Slice(8 + IntegrityBytesLength, PlaintextLength))).Dump("Plaintext"); } // Define other methods and classes here public static byte[] StringToByteArray(string hex) { return Enumerable.Range(2, hex.Length - 2) .Where(x => x % 2 == 0) .Select(x => Convert.ToByte(hex.Substring(x, 2), 16)) .ToArray(); } static class Extensions { public static byte[] Slice(this byte[] input, long startIndex, long? length = null) { var result = new byte[length ?? (input.LongLength - startIndex)]; Array.Copy(input, startIndex, result, 0, result.LongLength); return result; } } Output: Guid 01de2200-ef6c-3c1a-0f3a-889881ef77e7 IntegrityBytesLength 0 PlaintextLength 10 Plaintext blubb

In sqlservercentral blog I've encountered this phrase > Symmetric keys are deterministic > when created, meaning that the same parameters run in different > databases will result in the same key. That means that the same key in > a different database (or instance) can decrypt data that was encrypted > in your production instance. Keep control of the parameters used to > create symmetric keys and secure them. That means watch out for > storage of these items in source control, in installation files, > upgrade scripts, etc. I can't understand what it tries to say. Symmetric keys are protected by DMKs, so how can they be the same on different instances? Thanks.

We have a situation where a symmetric key has been created (a long time ago) using TRIPLE_DES. This is used to encrypt a password column, and is used by around ten stored procedures (that utilizes these with OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01;) I want to replace the certificate and key (to offer a modicum of future-proofing) with AES_192. However, I'd prefer to avoid editing the stored procedures if possible. I wrote the following routine that: 1. Unencrypts the password column into a plain text field 2. Drops the existing key and certificate 3. Creates a new certificate and key with the same names as above 4. Re-encrypts the plain text column using the new cert/key My question is, to the trained-eye, **is there anything here that sets off alarm bells, particularly because the old and new names are the same, and that these are used within stored procedures?** I've tested the code and it worked, but my lack of experience with encryption and a dose of information-overload today makes me very nervous, hence the plea for expertise... -- Decrypt data ALTER TABLE [dbo].[tbl_Users] ADD PasswordClear nvarchar(250) NULL; GO OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01; UPDATE [tbl_Users] SET PasswordClear = CONVERT(nvarchar(250), DECRYPTBYKEY(PasswordEnc)) CLOSE SYMMETRIC KEY SSN_Key_01; GO -- Drop old encryption ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordEnc; GO DROP SYMMETRIC KEY SSN_Key_01; GO DROP CERTIFICATE [MyCertificate01]; GO -- Create new certificate and key CREATE CERTIFICATE MyCertificate01 WITH SUBJECT = 'My Certificate 01'; GO CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = AES_192 ENCRYPTION BY CERTIFICATE MyCertificate01; GO ALTER TABLE [dbo].[tbl_Users] ADD PasswordEnc varbinary(256) NULL; GO OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyCertificate01; UPDATE [tbl_Users] SET PasswordEnc = ENCRYPTBYKEY(Key_GUID('SSN_Key_01'), PasswordClear); CLOSE SYMMETRIC KEY SSN_Key_01; GO ALTER TABLE [dbo].[tbl_Users] DROP COLUMN PasswordClear; GO ALTER TABLE [dbo].[tbl_Users] ALTER COLUMN PasswordEnc varbinary(256) NOT NULL; GO

I will start by describing the issue / cause before asking my question: If you are using a symmetric key in SQL to encrypt data. This can be set up as follows: CREATE MASTER KEY ENCRYPTION BY PASSWORD='password'; CREATE CERTIFICATE My_Certificate AUTHORIZATION [dbo] WITH SUBJECT = 'My Certificate' CREATE SYMMETRIC KEY My_Key AUTHORIZATION [dbo] ENCRYPTION BY PASSWORD='CertificatePassword' WITH IDENTITY_VALUE = 'My Key', ALGORITHM = AES_256, KEY_SOURCE = '12345-AABB-1234-BBAA' ENCRYPTION BY CERTIFICATE My_Certificate;My_Certificate This can then be used with the de/encryptbykey function(s) to encrypt data or vise-versa. The issue is that data encrypted on SQL Server 2016 (or a previous version) is not accessible if moved to SQL Server 2017 (after re-applying the certificate that is). This looks to be an issue with SQL Server 2017, See: https://support.microsoft.com/en-us/help/4053407/sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions And the suggested fix is to ensure you have CU2 installed and to globally enable trace-flag #4631 My question is: Is it possible (and if so how) to specify which hashing algorithm should be used. For instance, can I tell SQL to use sha2_256 for example when encrypting my certificate?

I have implemented data encryption using SQL Server symmetric key encryption technique using a certificate, master key with password. When we backup database from PROD server and restore to a different server, we open the master key using OPEN MASTER KEY DECRYPTION BY PASSWORD = ''... statement. Password for master key is being managed by a SQL admin. After opening the master key, we are able to decrypt the column data using DecryptByKey function. However, once we have done some analysis, we want to stop decrypting the data. No developer should be able to decrypt the data after a while. How to stop decrypting the data or close the master key so that no developer can see data in plain-text?

As title asks, what are implications for changing encryption from TRIPLE_DES to AES_128? I have an older database I've been tasked with hardening, and I need to updated the algorithm used for encryption on it. Running this query: > SELECT name, key_length, algorithm_desc, create_date, modify_date FROM > sys.symmetric_keys; returns a result with theh name: ##MS_ServiceMasterKey##, using the TRIPLE_DES algorithm. I didn't find any asymmetric keys used any of the database on this server. Thanks for any help!

In this article is explained how to decrypt a symmetric key. For example: SELECT SK.name, SK.symmetric_key_id, SK.key_length, SK.algorithm_desc, KE.crypt_type_desc, COALESCE(C.name,AK.name,PSK.name) AS protector_name, KE.crypt_property AS encrypted_key, COALESCE(DECRYPTBYCERT(C.certificate_id,KE.crypt_property), DECRYPTBYASYMKEY(AK.asymmetric_key_id,KE.crypt_property)) AS decrypted_key FROM sys.key_encryptions AS KE JOIN sys.symmetric_keys AS SK ON KE.key_id = SK.symmetric_key_id LEFT JOIN sys.certificates AS C ON KE.thumbprint = C.thumbprint LEFT JOIN sys.asymmetric_keys AS AK ON KE.thumbprint = AK.thumbprint LEFT JOIN sys.symmetric_keys AS PSK ON KE.thumbprint = CAST(PSK.key_guid AS VARBINARY(50)); It can be tested using the following query: -- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'smGK_MasterKeyPassword@'; -- CREATE CERTIFICATE [CERT_V001] WITH SUBJECT = 'User for protecting SM symetric keys.' -- CREATE SYMMETRIC KEY [SK_SecurityUsers_V001] WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE [CERT_V001] GO DECLARE @Email NVARCHAR(128) = 'sometest@google.com'; DECLARE @EmailEncrypted VARBINARY(256); OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION BY CERTIFICATE [CERT_V001]; SELECT @EmailEncrypted = ENCRYPTBYKEY(KEY_GUID('SK_SecurityUsers_V001'),@Email); SELECT @EmailEncrypted; CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001]; OPEN SYMMETRIC KEY [SK_SecurityUsers_V001] DECRYPTION BY CERTIFICATE [CERT_V001]; SELECT CONVERT(NVARCHAR(128), DECRYPTBYKEY(@EmailEncrypted)); CLOSE SYMMETRIC KEY [SK_SecurityUsers_V001]; --DROP SYMMETRIC KEY [SK_SecurityUsers_V001]; --DROP CERTIFICATE [CERT_V001]; --DROP MASTER KEY; I am wondering, does this mean the data is not protected at all? In the article is said, that: > However, for password-protected and symmetric-key-protected keys this > sadly does not work. I guess this means I need to use one of these types of encryption in order to be sure the data is protected?

Knowing the security objects hierarchy, we can see that in order to create a Symmetric Key and encrypt data, we need to create: 1. Database Master Key which is protected by password 2. Certificate which is protected by the Database Master key 3. The Symmetric Key itself, which is protect by the Certificate In Always Encrypted, we can use EKM module to simplify this hierarchy. For example, we can store the certificates in the Windows Certification Store and these certificates are protecting our encryption master keys. I am wondering, if I want to use not Always Encrypted built-in encryption functionalities, can I use EKM module to create and manage my symmetric keys (like it is shown on the diagram). In the CREATE SYMMETRIC KEY documentation we have provider option, but not enough information about possible providers and examples. I am interesting in storing the Certificate which is protecting the Symmetric keys or the Symmetric keys in external storage, because in such way the data is separated from the keys and in the database we are storing only references to the keys (like in always encrypted). Windows Certification Store will be best option for me, but Azure Key Vault or something else will work as well, I guess.

When a symmetric key is created we have the option to encrypted it by using at least one of the following: certificate, password, symmetric key, asymmetric key or EKM. When a password is used, it it said that: > When a symmetric key is encrypted with a password instead of a > certificate (or another key), the TRIPLE DES encryption algorithm is > used to encrypt the password. Because of this, keys that are created > with a strong encryption algorithm, such as AES, are themselves > secured by a weaker algorithm. So, using password is not best way to protect it. I was not able to find any pros and cons for using the other techniques. Can anyone tell is there are difference from security or performance matter or it is just a matter of choice?

I am wondering if symmetric keys created in particular database are being stored in the primary file group? Or they are stored in some special file group which the user is not able to interact with? For example, if I am performing a partial backup to a file group (primary or secondary) can I be sure that the backup does not contain the symmetric keys?