Introduction
Sometimes, business data needs to be persisted in DB in a format that cannot be read by humans. While hashing can also present a scenario, simple hashing techniques may not always suffice the needs of the business or the domain. Encryption helps secure data by transforming it into a series of characters not easy to decipher unless the encryption algorithm and passwords are known.
Background
All innovation comes from disruption. We came to face a challenge with a client who already has a huge data, that needs to be secured, and informed that the DB will always get a huge bulk of data on an ongoing basis. The bulk size could be anywhere from 10L to 50L records at a time.
With our existing .NET algorithms, if we tried to create a .NET library, fetched, encrypted and saved the data back to DB, it would be time and resource consuming involving unnecessary steps. So we decided to use the in-built AES encryption within SQL Server.
Using the Code
AES is considered to be the "gold standard" when it comes to symmetric key encryption. Symmetric since the keys for both encryption and decryption are the same.
N.B. : - I would like to highlight , please do not use the code in as is where is basis in production. The article is meant for reference. In actual practice, you may need to do the following to meet compliance / data security standards :
i) Do not pass the passwords directly into the stored procedure.
ii) Passwords could be hashed using a hash algorithm
iii) The name of the key and the password should not ideally persist in the same location, as in table or file.
In SQL server, to achieve symmetric key encryption with AES, the steps are given as follows:
- Create a symmetric key with a name that is either client specific or hardcoded as per your choice along with a password of your choice. (
@Msg is used to cross check the status. And is not mandatory. The stored procedure could be tweaked as per your need).
CREATE PROCEDURE [dbo].[CreateNOpenSymmetricKey]
(
@Key VARCHAR(MAX),
@Pwd VARCHAR(MAX),
@OpenConnection BIT = 1,
@Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @KeyCreationSQL VARCHAR(MAX)
DECLARE @OpenKeySQL VARCHAR(MAX)
SET @KeyCreationSQL = 'IF EXISTS(SELECT 1 from sys.symmetric_keys _
where name = ''' + @key + ''')
BEGIN
DROP SYMMETRIC KEY '+ @Key + ';
END
CREATE SYMMETRIC KEY ' + @Key + ' _
WITH ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = ''' + @Pwd + ''';'
PRINT @KeyCreationSQL
BEGIN TRY
EXEC(@KeyCreationSQL)
SET @Msg = 'Key created'
IF @OpenConnection = 1
BEGIN
SET @OpenKeySQL = 'OPEN SYMMETRIC KEY ' + @Key + _
' DECRYPTION BY PASSWORD = ''' + @Pwd + ''';'
PRINT @OpenKeySQL
EXEC(@OpenKeySQL)
SET @Msg = 'Key created and opened'
END
END TRY
BEGIN CATCH
SELECT @Msg = ERROR_MESSAGE()
END CATCH
END
- You will also need to create stored procedures to open & close the keys & also an sp that specifically closes and drops the key.
CREATE PROCEDURE [dbo].[OpenSymmetricKey]
(
@Key VARCHAR(MAX),
@Pwd VARCHAR(MAX),
@OpenConnection BIT = 1,
@Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @KeyCreationSQL VARCHAR(MAX)
DECLARE @OpenKeySQL VARCHAR(MAX)
SET @KeyCreationSQL = 'IF EXISTS(SELECT 1 from sys.symmetric_keys _
where name = ''' + @key + ''')
BEGIN
OPEN SYMMETRIC KEY ' + @Key + ' _
DECRYPTION BY PASSWORD = ''' + @Pwd + ''';
END'
PRINT @KeyCreationSQL
EXEC(@KeyCreationSQL)
SET @Msg = 'Key opened'
END
CREATE PROCEDURE [dbo].[CloseKey]
(
@Key VARCHAR(MAX),
@Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @CloseKeySQL VARCHAR(MAX)
SET @CloseKeySQL = 'CLOSE SYMMETRIC KEY ' + @Key + ';'
EXEC(@CloseKeySQL)
SET @Msg = 'Key closed'
END
CREATE PROCEDURE [dbo].[CloseAndDropKey]
(
@Key VARCHAR(MAX),
@Msg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @CloseKeySQL VARCHAR(MAX)
SET @CloseKeySQL = 'CLOSE SYMMETRIC KEY ' + @Key + _
'; DROP SYMMETRIC KEY '+ @Key + ';'
EXEC(@CloseKeySQL)
SET @Msg = 'Key dropped'
END
- Once the above stored procedures are in place, then comes using them as per your needs. Here, we are having a table called
FileData, that has column called "Value". The content of this column needs to be encrypted. The steps to achieve this are as follows:
- Create a symmetric key with a given name and password of your choice. The length and choice of your password may be governed as per your business needs.
- Open the key.
- Encrypt the column values and if required, store it in the same or other table. For the sake of POC, we will store it in another table called
CipherValueTable (CipherValue Varchar(MAX)) - Close the key.
CREATE PROCEDURE [dbo].[EncryptWithKey]
(
@Key VARCHAR(MAX),
@Pwd VARCHAR(MAX),
@EncryptionStatusMsg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @OutPut VARCHAR(MAX)
EXEC CreateNOpenSymmetricKey @Key, @Pwd, 1, @OutPut OUTPUT
IF @OutPut = 'Key created'
BEGIN
DECLARE @OpenKeySQL VARCHAR(MAX)
SET @OpenKeySQL = 'OPEN SYMMETRIC KEY ' + @Key
PRINT @OpenKeySQL
EXEC(@OpenKeySQL)
SET @EncryptionStatusMsg = 'Key opened'
END
ELSE IF @OutPut <> 'Key created and opened'
BEGIN
SET @EncryptionStatusMsg = 'Some issue has occurred try again later'
END
IF CHARINDEX(@OutPut,'Key',0)>-1
BEGIN
IF EXISTS (SELECT 1 FROM SYSOBJECTS _
WHERE XTYPE='U' AND NAME ='CipherValueTable')
BEGIN
DELETE FROM CipherValueTable
END
DECLARE @startTime DATETIME , @endTime DATETIME
SET @startTime = GETDATE()
Print 'Encryption started here at--- ' + CAST(@startTime AS VARCHAR)
INSERT INTO CipherValueTable (CipherValue)
SELECT EncryptByKey(Key_GUID(@Key), VALUE) FROM FileData
SET @endTime = GETDATE()
Print 'Encryption end here at--- ' + CAST(@endTime AS VARCHAR)
Print 'Time taken for encryption ' + _
CAST(DATEDIFF(second,@startTime,@endTime) AS VARCHAR)
SELECT * FROM CipherValueTable
EXEC CloseKey @Key,@EncryptionStatusMsg
END
END
- In order to decrypt the values in the table
CipherValueTable, a decryption with the same symmetric key using the same password is required. Dropping the key and recreating the key and password will not suffice the needs, since it may maintain a unique value based on time and space constraints (my gut feeling, based on outputs I have received. Will be exploring more on this). The content of the column needs to be decrypted. The steps to achieve this are as follows:
- Open the symmetric key with the given name and password used for the encryption.
- Use the inbuilt function "
DecryptByKey" to decrypt values. For the sake of POC, we will store it in another table called ValueTable (Value Varchar(MAX)) - Close the key.
CREATE PROCEDURE [dbo].[DecryptWithKey]
(
@Key VARCHAR(MAX),
@Pwd VARCHAR(MAX),
@DecryptionStatusMsg VARCHAR(MAX) OUTPUT
)
AS
BEGIN
DECLARE @OutPut VARCHAR(MAX)
EXEC OpenSymmetricKey @Key, @Pwd, 1, @OutPut OUTPUT
PRINT @OutPut
IF CHARINDEX(@OutPut,'Key',0)>-1
BEGIN
IF EXISTS (SELECT 1 FROM SYSOBJECTS _
WHERE XTYPE='U' AND NAME ='CipherValueTable')
BEGIN
IF EXISTS (SELECT 1 FROM SYSOBJECTS _
WHERE XTYPE='U' AND NAME ='ValueTable')
BEGIN
DELETE FROM ValueTable
END
INSERT INTO ValueTable
SELECT CONVERT(NVARCHAR(MaX), DECRYPTBYKEY(CipherValue)) FROM CipherValueTable
SELECT * FROM ValueTable
EXEC CloseAndDropKey @Key,@DecryptionStatusMsg
END
ELSE
BEGIN
SET @DecryptionStatusMsg = 'Cipher table not found'
END
END
END
- Drop the key only if you will not be using it in future.
- To call the stored procedures, use the given syntax. (The original may vary as per your requirement):
DECLARE @EncryptionStatusMsg VARCHAR(MAX)
EXEC EncryptWithKey 'IAmSoThrilledThatICreatedMyFirstSymmetricKey' , _
'Abc@12345678', @EncryptionStatusMsg OUTPUT
PRINT @EncryptionStatusMsg
DECLARE @DecryptionStatusMsg VARCHAR(MAX)
EXEC DecryptWithKey 'IAmSoThrilledThatICreatedMyFirstSymmetricKey' , _
'Abc@12345678', @DecryptionStatusMsg OUTPUT
PRINT @DecryptionStatusMsg
Points of Interest
Here are some of the interesting things that I found:
- Important : What is the valid password is completely defined by the password rules set by the SQL Server instance. So while SQL instance may consider 'abcdedf' as completely valid password, another may not. However using a strong password is always recommended. You can also create a function that calculates the strength of a given passwords and validates it.
- Important : I have used 'CONVERT(NVARCHAR(MaX), DECRYPTBYKEY(CipherValue))'. This converts the decrypted value to NVarchar. Now this casting or conversion is dependent on the source column. So if your source column is NVarchar and you are casting the decrypted value back to NVarchar, it will work fine, but if it is varchar/datetime and you are casting it to nvarchar, it may have issues. So cast as per datatype.
- The above algorithm is fast. I was able to encrypt >5L records in under a minute.
- The decryption took a tad bit longer, due to
drop statement, insert statement and followed by select, and it was around 1 min and 8 seconds for the same set of >5L records. - Unlike .NET libraries, the password length is not prefixed.
- There are many choices for Data Security in SQL Server and this is just the tip of the iceberg.
History
I will keep updating, as and when I improve this.
