Solved

How to create and use encrypted columns in SQL 2005?

Posted on 2017-05-14
15
78 Views
Last Modified: 2017-05-17
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
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 17

Expert Comment

by:John Tsioumpris
ID: 42135681
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 42135805
I'm not sure how encrypting in the view solves the problem.  Shouldn't the data be encrypted in the table?
0
 
LVL 42

Expert Comment

by:pcelba
ID: 42136020
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 4

Author Comment

by:bfuchs
ID: 42136038
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
 
LVL 42

Expert Comment

by:pcelba
ID: 42136187
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
 
LVL 4

Author Comment

by:bfuchs
ID: 42136217
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
 
LVL 42

Expert Comment

by:pcelba
ID: 42137986
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
 
LVL 4

Author Comment

by:bfuchs
ID: 42138050
Hi,

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

Thanks,
Ben
0
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 42138970
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
 
LVL 42

Expert Comment

by:pcelba
ID: 42138975
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
 
LVL 4

Author Comment

by:bfuchs
ID: 42139489
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
 
LVL 42

Expert Comment

by:pcelba
ID: 42139623
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
 
LVL 4

Author Comment

by:bfuchs
ID: 42139650
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
 
LVL 42

Assisted Solution

by:pcelba
pcelba earned 500 total points
ID: 42139668
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
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 42139753
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

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question