CODEWITHSUNDEEP
sqlsql-serverencryptionaesx509certificate
Yesudass Moses
Yesudass Moses

Reputation: 1859

MS SQL column encryption without certificate

I am trying to encrypt one of my sensitive columns in a SQL Server table. I tried AES256 encryption with this script, and it works perfect. But I don't want to create Certificates, or Symmetric Keys on my SQL Server as it may be a security problem later. How can I encrypt the data with a single password or key in my query ? Another problem with this method is, I can use only a single master password for my encryption. 

    CREATE MASTER KEY ENCRYPTION
    BY PASSWORD = '$Passw0rd'
    GO

    CREATE CERTIFICATE AESEncryptTestCert
    WITH SUBJECT = 'AESEncrypt'
    GO

    CREATE SYMMETRIC KEY AESEncrypt
    WITH ALGORITHM = AES_256 ENCRYPTION -- TRIPLE_DES ENCRYPTION
    BY CERTIFICATE AESEncryptTestCert;


   OPEN SYMMETRIC KEY AESEncrypt DECRYPTION
   BY CERTIFICATE AESEncryptTestCert 
   SELECT ENCRYPTBYKEY(KEY_GUID('AESEncrypt'),'The text to be encrypted');

Upvotes: 2

Views: 2765

Answers (1)

Jesús López
Jesús López

Reputation: 9221

You can use EncryptByPassPhrase and DecryptByPassPhrase, for example:

DECLARE @CypherText varbinary(8000)
DECLARE @ClearText varchar(128) = 'Text to encrypt'

SET @CypherText = EncryptByPassPhrase ('MyP@ssword123', @ClearText)

DECLARE @DecryptedText varchar(128)
SET @DecryptedText = DecryptByPassPhrase('MyP@ssword123', @CypherText)

SELECT @CypherText AS CypherText, @DecryptedText As DecryptedText

EDIT: The above code produces the following output

+----------------------------------------------------------------------------+-----------------+
|                                 CypherText                                  |  DecryptedText  |
+----------------------------------------------------------------------------+-----------------+
| 0x01000000F1D813F399246484FDA8D7C7D22BFBCF748D3F6033D4E9980FCDC58A387A1A93 | Text to encrypt |
+----------------------------------------------------------------------------+-----------------+

Upvotes: 9

Related Questions