Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

How to create and use encrypted columns in SQL 2005?

Hi Experts,

I'm in middle of a process of securing data in our database.

Would like to move all employees sensitive data into a separate table and have the columns there set as encrypted columns.

Now I am basically looking for an introduction on how to successfully use that within my Access FE application.

Was trying to follow what it says on following link
https://blogs.msdn.microsoft.com/lcris/2005/06/09/simple-demo-for-how-to-encrypt-and-decrypt-a-table-column-in-sql-server-2005/
However when I ran the following view, the encrypted column was showing as null..
create view v_employees as select id, name, convert(varchar(10), decryptbykey(salary, 1, convert(varchar(30), id))) as salary from t_employees;

Open in new window


Thanks in advance.
0
bfuchs
Asked:
bfuchs
2 Solutions
 
John TsioumprisSoftware & Systems EngineerCommented:
Possibly you missed something from the post. Personally on an couple of occasions i need some kind of encrypting i used simple XOR with a proprietary key to encrypt the data and everything change to a nice series of numbers....if you need it i can post the VBA code..
0
 
PatHartmanCommented:
I'm not sure how encrypting in the view solves the problem.  Shouldn't the data be encrypted in the table?
0
 
pcelbaCommented:
To obtain data from the view you have to open the symmetric key first. Did you do it?
Code sample for the key opening: https://docs.microsoft.com/en-us/sql/t-sql/functions/decryptbykey-transact-sql

You should rather use the DecryptByKeyAutoCert() which does not require the explicit key opening:
https://docs.microsoft.com/en-us/sql/t-sql/functions/decryptbykeyautocert-transact-sql

BTW, I would rather use asymmetric key.
Your web link does not work right now but you may look at this sample code for more ideas:
https://www.codeproject.com/Articles/19134/Encrypted-columns-and-SQL-Server-performance

You may also look here: http://sqlcrypto.codeplex.com/ for more code samples.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
bfuchsAuthor Commented:
Hi Experts,

This is what I was doing as mentioned in the link above
create table EmployeestblHI (id int primary key, EmployeeID int,  SSN varbinary(300));

create symmetric key ssk_employeestbl with algorithm = aes_192 encryption by password = 'MyPWD';

open symmetric key ssk_employeestbl decryption by password = 'MyPWD';

select * from sys.openkeys;

insert into EmployeestblHI(EmployeeID,SSN ) values (101, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6789', 1, '101'));
insert into EmployeestblHI(EmployeeID,SSN ) values (101, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6788', 1, '101'));

create view v_employeestblHI as select id, convert(varchar(10), decryptbykey(ssn, 1, convert(varchar(30), id))) as SSN from Employeestblhi ;

select * from v_employeestblhi


close symmetric key ssk_employeestbl;

Open in new window


Now any reason why I cant see the SSN while running the statement "select * from v_employeestblhi"?

Thanks,
Ben
0
 
pcelbaCommented:
Your code does not work due to some errors...

Try this (you have to delete existing objects first) :
create table EmployeestblHI (id int primary key, EmployeeID int,  SSN varbinary(300));

create symmetric key ssk_employeestbl with algorithm = aes_192 encryption by password = 'MyPWD';

open symmetric key ssk_employeestbl decryption by password = 'MyPWD';

select * from sys.openkeys;

insert into EmployeestblHI(EmployeeID,SSN ) values (1, 101, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6789', 1, '101'));
insert into EmployeestblHI(EmployeeID,SSN ) values (2, 102, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6788', 1, '102'));

create view v_employeestblHI as select id, convert(varchar(11), decryptbykey(ssn, 1, convert(varchar(30), EmployeeID))) as SSN from Employeestblhi ;

select * from v_employeestblhi


close symmetric key ssk_employeestbl;

Open in new window

0
 
bfuchsAuthor Commented:
Hi,

I get the following while running the inserts

There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Thanks,
Ben
0
 
pcelbaCommented:
Sorry missing column name
create table EmployeestblHI (id int primary key, EmployeeID int,  SSN varbinary(300));

create symmetric key ssk_employeestbl with algorithm = aes_192 encryption by password = 'MyPWD';

open symmetric key ssk_employeestbl decryption by password = 'MyPWD';

select * from sys.openkeys;

insert into EmployeestblHI(id, EmployeeID,SSN ) values (1, 101, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6789', 1, '101'));
insert into EmployeestblHI(id, EmployeeID,SSN ) values (2, 102, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6788', 1, '102'));

create view v_employeestblHI as select id, convert(varchar(11), decryptbykey(ssn, 1, convert(varchar(30), EmployeeID))) as SSN from Employeestblhi ;

select * from v_employeestblhi


close symmetric key ssk_employeestbl;

Open in new window

0
 
bfuchsAuthor Commented:
Hi,

Same thing, I get to see null for the SSN.

Thanks,
Ben
0
 
pcelbaCommented:
The code works for me in SQL 2005 Express version. It seems you did some mistake in the implementation. Please check the code again.

I am adding the last code copied from SSMS and the results:
DROP view v_employeestblHI
DROP TABLE EmployeestblHI
DROP symmetric key ssk_employeestbl

create table EmployeestblHI (id int primary key, EmployeeID int,  SSN varbinary(300));

create symmetric key ssk_employeestbl with algorithm = aes_192 encryption by password = 'MyPWD';

open symmetric key ssk_employeestbl decryption by password = 'MyPWD';

select * from sys.openkeys;

insert into EmployeestblHI(id, EmployeeID,SSN ) values (1, 101, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6789', 1, '101'));
insert into EmployeestblHI(id, EmployeeID,SSN ) values (2, 102, encryptbykey(key_guid('ssk_employeestbl'), '123-45-6788', 1, '102'));
GO

create view v_employeestblHI as select id, convert(varchar(11), decryptbykey(ssn, 1, convert(varchar(30), EmployeeID))) as SSN from Employeestblhi ;
GO

SELECT @@version
select * from v_employeestblhi
close symmetric key ssk_employeestbl;

Open in new window

Results of above code
0
 
pcelbaCommented:
Of course, above INSERT commands are not suitable for production environment. Just for testing.

You could also post the result of the

select * from EmployeestblHI

It will show whether the SSN is stored in the table.
0
 
bfuchsAuthor Commented:
Hi pcelba,

Ok this latest seems to work..

Of course, above INSERT commands are not suitable for production environment

Now, you you explain to me what exactly do I have to do in order to use this in production?

FYI- We have an Access ADP 2003 front end application.

Thanks,
Ben
0
 
pcelbaCommented:
Aha. The latest code does not differ from the previous one... GO commands were added to allow its execution in SSMS at once (by one F5 hit) and the first three commands were used to cleanup the old environment from the previous run.

OK, now you agree it works. The problem in production environment is that you don't have the possibility to pass hardcoded IDs into the database by INSERT commands most probably. I don't know what values are you planning to insert but you could implement the new employee insertion as a stored procedure and call this procedure from Access. The procedure will do the rest. Of course, you have to specify how to obtain the ID and EmployeeID values for the INSERT command in the SP.

The view for data retrieval is OK.
0
 
bfuchsAuthor Commented:
Perhaps I did not copy the view definition properly, sorry..

I do have some questions regarding the use of encrypted column.

1- Does it means I cant have a control bound to the SSN field?
2- You're saying I need to call a SP for each insert/update?
3- The key must remain open all the time (open symmetric key..) as by any given time users are performing updates?
4- Taking in consideration that we may upgrade soon our SQL to SQL Express 2008, perhaps its not worth all these efforts as for that version there is a better way to secure our DB (something called TDE, not sure if that's available for express version tough)?
5- You mentioned that DecryptByKeyAutoCert()  is a better way of handling it (same to asymmetric key), now since I'm novice in this area of programming, which of them would be the simplest to implement?

Thanks,
Ben
0
 
pcelbaCommented:
This expert suggested creating a Gigs project.
You can have control bound to SSN but this view column is read/only. Of course, you may create trigger to avoid this problem. Look at the article mentioned in my first post.

You don't need to call SP for each INSERT/UPDATE but SP is easier to implement.

Yes, any update of encrypted data requires the key to be open.

TDE is available only in the Enterprise and Developer editions of SQL Server 2008.  SQL 2016 Express offers Always Encrypted feature.

DecryptByKeyAutoCert() does not need to explicitly open the key. Everything else should work the same way as DecryptByKey().  So its on your decision what method to use. DecryptByKeyAutoCert() is simpler.

Note: I did not suggest to create a Gigs project. EE just interpreted some keys sequence. But you may decide to do so...
0
 
bfuchsAuthor Commented:
Thanks pcelba!

If you know of a site where I can get step by step guidance how to implement this in Access I would appreciate.

Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now