You can use SQL Server's built-in encryption features to store sensitive configuration values securely. Here are common approaches:
1. Using Symmetric Keys
Step 1: Create a Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';
Step 2: Create a Symmetric Key
CREATE SYMMETRIC KEY ConfigSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'AnotherStrongPassword';
Step 3: Encrypt a Config Value
OPEN SYMMETRIC KEY ConfigSymKey
DECRYPTION BY PASSWORD = 'AnotherStrongPassword';
DECLARE @ConfigValue NVARCHAR(100) = 'SensitiveValue';
DECLARE @EncryptedValue VARBINARY(256);
SET @EncryptedValue = EncryptByKey(Key_GUID('ConfigSymKey'), @ConfigValue);
-- Store @EncryptedValue in your config table
Step 4: Decrypt a Config Value
OPEN SYMMETRIC KEY ConfigSymKey
DECRYPTION BY PASSWORD = 'AnotherStrongPassword';
SELECT CAST(DecryptByKey(EncryptedValueColumn) AS NVARCHAR(100)) AS DecryptedValue
FROM ConfigTable;
2. Using Always Encrypted (Column-Level Encryption)
- Define columns as encrypted using Always Encrypted.
- Use client-side drivers to insert and read encrypted values.
Notes
- Protect your keys and passwords.
- Use secure connections and limit access to encryption keys.
- Regularly rotate keys if possible.
Refer to Microsoft Docs: SQL Server Encryption for more details.
sample code
ALTER procedure [config].[SetConfig] (@config_name varchar(100), @config_value varchar(2000), @is_encrypted bit = 0)
As Begin
declare @encrypted_value varbinary(max)
if exists(select * from DataPortal.dbo.[DataPortalConfig] where trim(config_name) = trim(@config_name))
print @config_name +': This config already exists'
else
if @is_encrypted = 1
begin
OPEN SYMMETRIC KEY SymKeyDP DECRYPTION BY CERTIFICATE CertificateDP;
set @encrypted_value = EncryptByKey (Key_GUID('SymKeyDP'), @config_value);
CLOSE SYMMETRIC KEY SymKeyDP;
INSERT INTO DataPortal.dbo.[DataPortalConfig](config_name, [encrypted_value], is_encrypted)
VALUES(@config_name, @encrypted_value, @is_encrypted);
end
else
INSERT INTO DataPortal.dbo.[DataPortalConfig](config_name, config_value, is_encrypted)
VALUES(@config_name, @config_value, @is_encrypted);
end
ALTER PROCEDURE [dbo].[GetConfig2](@configName VARCHAR(100))
AS
BEGIN
DECLARE @ReturnValue VARCHAR(1000)
DECLARE @IsEncrypted BIT
SELECT @IsEncrypted = c.is_encrypted
FROM DataPortalConfig c
WHERE c.config_name = @configName
IF @IsEncrypted = 1
BEGIN
OPEN SYMMETRIC KEY SymKeyDP DECRYPTION BY CERTIFICATE CertificateDP;
SELECT @ReturnValue = CONVERT(VARCHAR, DECRYPTBYKEYAUTOCERT(CERT_ID('CertificateDP'), NULL, c.encrypted_value)) --CONVERT(VARCHAR, DecryptByKey(c.encrypted_value))
FROM DataPortalConfig c
WHERE c.config_name = @configName
/*SELECT @ReturnValue = CONVERT(VARCHAR, DecryptByKey(c.encrypted_value))
FROM DataPortalConfig c
WHERE c.config_name = @configName*/
CLOSE SYMMETRIC KEY SymKeyDP;
END
ELSE
SELECT @ReturnValue = c.config_value
FROM DataPortalConfig c
WHERE c.config_name = @configName
SELECT @ReturnValue
END