I am building a database that stores employee information, including bank account details. The bank account details are stored so that we can check for current or past staff using our company, which in our case might be a reason to be concerned about fraud. I have been reading up on SQL Server 2008 R2 Encrption and Decryption on MSDN and other places and have come up with the following sample script:
IF NOT EXISTS(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BankDetai!5'
GO
CREATE CERTIFICATE Employees_01 WITH SUBJECT = 'Employee Bank Account Details'
GO
CREATE SYMMETRIC KEY AccountNos_01
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Employees_01
GO
CREATE TABLE #Bank_Accounts
( Id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_BankAccount_Id PRIMARY KEY CLUSTERED (Id ASC)
,AccountNumber VARBINARY(128) NOT NULL
,BankName NVARCHAR(255) NOT NULL
,CountryCode VARCHAR(2) NOT NULL
,CreateDate DATETIME2(0) NOT NULL
,EmployeeId INT NOT NULL
,IsActive BIT NOT NULL
,SortCode VARBINARY(128) NOT NULL
);
OPEN SYMMETRIC KEY AccountNos_01
DECRYPTION BY CERTIFICATE Employees_01;
CREATE TABLE #SampleBankAccounts
( Id INT IDENTITY(1,1)
,AccountNumber VARCHAR(255)
,BankName NVARCHAR(255)
,CountryCode VARCHAR(2)
,EmployeeId INT NOT NULL
,IsActive BIT
,SortCode VARCHAR(255))
INSERT #SampleBankAccounts
SELECT * FROM
( SELECT
'123456789' AccountNo
,'Barclays' BankName
,'US' Country
,1 Employee
,1 IsActive
,'12-34-56' SortCode
UNION
SELECT
'9876543210'
,'Barclays'
,'US'
,1
,0
,'12-34-56'
UNION
SELECT
'111111111111'
,'HSBC'
,'UK'
,2
,1
,'222222'
UNION
SELECT
'IBAN 123 456 9875 3215'
,'Nationwide'
,'ES'
,3
,1
,'00_gn321654'
)AS Samples
ORDER BY Employee
MERGE #Bank_Accounts AS Target
USING #SampleBankAccounts AS Source ON Target.EmployeeId = Source.EmployeeId
AND Source.AccountNumber = Target.AccountNumber
AND Source.BankName = Target.BankName
AND Source.CountryCode = Target.CountryCode
AND Source.SortCode = Target.SortCode
WHEN NOT MATCHED
THEN INSERT (AccountNumber, BankName,CountryCode,CreateDate,EmployeeId,IsActive,SortCode)
VALUES (
ENCRYPTBYKEY(KEY_GUID('AccountNos_01'),Source.AccountNumber)
,Source.BankName
,Source.CountryCode
,GETDATE()
,Source.EmployeeId
,Source.IsActive
,ENCRYPTBYKEY(KEY_GUID('AccountNos_01'),Source.SortCode));
GO
DROP TABLE #SampleBankAccounts
That seems to work fine, but now I need to take a variable e.g. @AccountNo and see if it matches any of the bank accounts we have stored (I'll use a stored procedure in all likelihood). I would much rather avoid decryption in any scripts if possible, so I wondered if it was possible to encrypt the @AccountNo variable and then compare it, so we might see a match:
DECLARE @MyTestBankAccount NVARCHAR(255) = '123456789'
DECLARE @MyEncryptedTestBankAccount VARBINARY(128)
OPEN SYMMETRIC KEY AccountNos_01
DECRYPTION BY CERTIFICATE Employees_01;
SELECT @MyEncryptedTestBankAccount = ENCRYPTBYKEY(KEY_GUID('AccountNos_01'),@MyTestBankAccount)
SELECT
AccountNumber
,EmployeeId
FROM Bank_Accounts
WHERE
@MyEncryptedTestBankAccount = AccountNumber
This does not work; as you can see, I use an account number that should be present in the table, but no results are returned. I have tried to decrypt the account number column and compare it to the variable, but I get a load of Chinese characters coming out (much as in this question), and they do not match the characters created by encrypting and decrypting the variable, so that doesn't work for a match either...
So why does the following query (using data from above) not give me the encrypted and decrypted values for the account details? Can I use this method to search employee bank accounts against a given bank account?
OPEN SYMMETRIC KEY AccountNos_01
DECRYPTION BY CERTIFICATE Employees_01;
SELECT
AccountNumber AS Encrypted_AccountNo
,EmployeeId AS EmployeeId
,CONVERT(NVARCHAR,DECRYPTBYKEY(AccountNumber)) AS Decrypted_AccountNo
FROM Bank_Accounts
