EncryptbyKey SSN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

EncryptbyKey SSN

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2009-05-26 : 11:40:45

I'm trying to write a sp to encrypt my SSN column (varbinary(256) that has a unique contraint so that the SSN field does not allow dups, however, after the field is encrypted then it no longers recognizes duplicates. What am I doing wrong?

sp-
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_AddEmployee2TEST]

(
@LastName varchar(20),
@MiddleName varchar(20),
@FirstName varchar(20),
@DateOfBirth datetime,
@SSN varchar(11),
@Location_LocationID int,
@Employee_EmployeeID int output
)

AS
begin
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

OPEN SYMMETRIC KEY AuditToolKey
DECRYPTION BY CERTIFICATE AuditToolCert

Insert Into [dbo].[i9audit_employees]
(
LastName,
MiddleName,
FirstName,
DateOfBirth,
SSN,
Location_LocationID
)
values
(
@LastName,
@MiddleName,
@FirstName,
@DateOfBirth,
CONVERT(VARBINARY(256),@SSN),
@Location_LocationID
);

select @Employee_EmployeeID = @@identity;

Update i9audit_employees
set SSN = ENCRYPTBYKEY(KEY_GUID('AuditToolKey'),@SSN)

COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
EXEC dbo.sp_ErrorMap$MapError
END CATCH

END

smorty44
Yak Posting Veteran

93 Posts

Posted - 2009-05-26 : 15:50:43

Let me correct my post. I have encryption by key working on my SSN field now using this sp below. What is not working correctly is the unique constraint on the SSN field and duplicates are being allowed. How can I prevent duplicates when the field is encrypted? SSN datatype (varbinary(256)) Or is there a better way to do this?

USE [Audit_tool]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_AddEmployee2]

(
@LastName varchar(20),
@MiddleName varchar(20),
@FirstName varchar(20),
@DateOfBirth datetime,
@SSN varchar(11),
@Location_LocationID int,
@Employee_EmployeeID int output

)

AS
begin
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

OPEN SYMMETRIC KEY AuditToolKey
DECRYPTION BY CERTIFICATE AuditToolCert

Insert Into [dbo].[i9audit_employees]
(
LastName,
MiddleName,
FirstName,
DateOfBirth,
SSN,
Location_LocationID
)
values
(
@LastName,
@MiddleName,
@FirstName,
@DateOfBirth,
ENCRYPTBYKEY(KEY_GUID('AuditToolKey'),@SSN),
@Location_LocationID
);

select @Employee_EmployeeID = @@identity;

COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
EXEC dbo.sp_ErrorMap$MapError
END CATCH

END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-05-26 : 17:57:35

The problem is that encryption will return different cypertext values for the same cleartext input. I know this is true for AES encryption; I'm not sure about the other methods.

Since the same SSN will produce multiple encrypted values, a unique constraint on the encrypted column cannot be used to prevent duplicate SSNs. The only way I know to prevent duplicates would be to unencrypt the other values in the table and compare them to the new one.

Storing a hash on the SSN in the table would defeat your security, since it is easy to brute force hash all possible SSNs and compare them to the SSN hash in the table to get the actual value. Maybe a hash of the SSN, plus some other value would prevent this. It's possible that you would not have a duplicate hash in the range of all valid SSNs done this way and you might be able to get away with a duplicate constraint. However, you would need to test all possible values to make sure. Also, you would have to be sure that the extra text that you use to hash the value is kept secret to prevent someone from using a brute force attack.


-- Create a hash of an SSN with aditional text 
select SSNHash = HashBytes('sha', 'qseiofjqefj'+ '123456789'+'fhjfopjqwejwefjfijfjdf')

Results:


SSNHash
-------------------------------------------------
0xCFE80E97A8B1CA02FDA074F7BEFA19BF4B1C77F9

(1 row(s) affected)

Edit:
I did a quick test of the above with 10,000,000 unique SSNs and found no duplicates. However, that is only 1% of all possible SSNs, so you would have to do more extensive testing to make sure there are no dupes.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 18:21:10

Also, your UPDATE statement dows not include a WHERE clause, which means ALL records get the same SSN encypted value.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-26 : 18:22:57

set @Employee_EmployeeID = scope_identity()

Update i9audit_employees
set SSN = ENCRYPTBYKEY(KEY_GUID('AuditToolKey'), @SSN)
WHERE employeeid = @employee_employeeid

E 12°55'05.63"
N 56°04'39.26"

smorty44
Yak Posting Veteran

93 Posts

Posted - 2009-05-29 : 14:06:02

I think I'd like to try to add a decrypt process within this stored procedure that will decrypt the ssn data that has already been entered and if the ssn = @ssn then an error will occur and the tran will rollback else the tran will be committed and add the new data. May I please get some help in writing this? Here is the sp that inserts the data:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_AddEmployee2]

(
@LastName varchar(20),
@MiddleName varchar(20),
@FirstName varchar(20),
@DateOfBirth datetime,
@SSN varchar(11),
@AlienNumber varchar(20),
@I9DateOfHire datetime,
@DateOfTermination datetime,
@PayrollDateOfHire datetime,
@Location_LocationID int,
@Employee_EmployeeID int output

)

AS
begin
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRANSACTION

OPEN SYMMETRIC KEY AuditToolKey
DECRYPTION BY CERTIFICATE AuditToolCert

Insert Into [dbo].[i9audit_employees]
(
LastName,
MiddleName,
FirstName,
DateOfBirth,
SSN,
AlienNumber,
I9DateOfHire,
DateOfTermination,
PayrollDateOfHire,
Location_LocationID
)
values
(
@LastName,
@MiddleName,
@FirstName,
@DateOfBirth,
ENCRYPTBYKEY(KEY_GUID('AuditToolKey'),@SSN),
@AlienNumber,
@I9DateOfHire,
@DateOfTermination,
@PayrollDateOfHire,
@Location_LocationID
);

select @Employee_EmployeeID = @@identity;

COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
EXEC dbo.sp_ErrorMap$MapError
END CATCH

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources