Encrypt a column in sql server?

Question

Encrypt a column in sql server?

vsamantha35

SSChampion

Points: 11529
More actions
November 4, 2017 at 2:46 pm

#326041

Hi All,
Need help in encrypting a column and share the encryption key with the person whom I want to share so that he can decrypt the data.
Also, I want to encrypt only first 8 characters of a 12 letter string values.
How can I acheive that in SQL Server 2014 SP1 Enterprise edition? What are the steps involved in encrypting and decrypting a column of a table.
--demo script
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test ;
create table test
(c1 int identity(1,1),
c2 varchar(12)
)
insert into test(c2)
select '182933892910'
union all
select '134533893456'
union all
select '239338929130'
union all
select '182933896666'
union all
select '182933898375'
select c1,c2 from test;
c1    c2
1    182933892910
2    134533893456
3    239338929130
4    182933896666
5    182933898375
--Expected output
1   x4^#5*42910
2   *%153(*3456
3    &#*$**^9130
4   *%2&(1*6666
5   x!#2!$*^8375
Thanks,
Sam

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

It sounds like what your trying to describe is a feature called Dynamic Data Masking (DDM), which you can read about here: https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking. DDM, however, was introduced with SQL Server 2016; it is not available in SQL Server 2014.

You can still encrypt data in SQL Server 2014, but I can't think of way that you obfuscate the first 8 characters of a string/integer, and allow the visibility of the remainder (while giving others access to see the whole thing). When you encrypt data on SQL server, it's an a varbinary, which means it looks nothing like a string or integer. For example that varbinary value of 182933892910 (without encryption) is 0x0C0000012EAFB5972A000000. This, however, is different again if the original is a varchar or nvarchar (0x313832393333383932393130 and 0x310038003200390033003300380039003200390031003000 respectively).

As this may not be what you're looking for, I'm not going to post an example or solution right now for you; only if you're interested. You can read up about data encryption here: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-encryption. As you can see there, are many different ways to go about it. If you decide to pursue this path, and run into trouble, post back. I'm sure I, or and a number of other users will be more than happy to help. 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

vsamantha35 - Saturday, November 4, 2017 2:46 PM
Hi All,
Need help in encrypting a column and share the encryption key with the person whom I want to share so that he can decrypt the data.
Also, I want to encrypt only first 8 characters of a 12 letter string values.
How can I acheive that in SQL Server 2014 SP1 Enterprise edition? What are the steps involved in encrypting and decrypting a column of a table.
--demo script
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test ;
create table test
(c1 int identity(1,1),
c2 varchar(12)
)
insert into test(c2)
select '182933892910'
union all
select '134533893456'
union all
select '239338929130'
union all
select '182933896666'
union all
select '182933898375'
select c1,c2 from test;
c1    c2
1    182933892910
2    134533893456
3    239338929130
4    182933896666
5    182933898375
--Expected output
1    xxxxxxxx2910
2    xxxxxxxx3456
3    xxxxxxxx9130
4    xxxxxxxx6666
5    xxxxxxxx8375
Thanks,
Sam

Save yourself a headache. Use two columns when you store the data... one for the full encryption and one to generate the "all X's + 4" that you wish do display. Understand that the "X's" should literally be a string of "X's". Don't even think of using dynamic data-masking for this because a half-wit that knows how to use a SELECT and the right kind of CONVERT can break DDM.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11529 More actions · Answer 3

Sorry my bad. I want to encrypt and not masking the data. Editing the original post with correct expected output.

Thom A SSC Guru Points: 99126 More actions · Answer 4

Jeff Moden - Saturday, November 4, 2017 8:42 PM
Don't even think of using dynamic data-masking for this because a half-wit that knows how to use a SELECT and the right kind of CONVERT can break DDM.

Thanks Jeff. I'll admit, I haven't looked at DDM yet; I don't have SQL 2016 at the office so not had the need.

vsamantha35 - Sunday, November 5, 2017 12:53 AM
Sorry my bad. I want to encrypt and not masking the data. Editing the original post with correct expected output.

Your original post has just, however, replaced characters with random non numeric characters. Again, this isn't how encryption works.

I think it's time for a small example. This is NOT a solution, just an example of how encryption works, and why, what you're after, isn't going to be achievable:

USE Sandbox;
GO
CREATE CERTIFICATE YourCert 
  ENCRYPTION BY PASSWORD = 'A Strong Password goes here, 1234567'
 WITH SUBJECT = 'An example Cert';
GO
CREATE SYMMETRIC KEY YourKey
 WITH ALGORITHM = AES_256 
 ENCRYPTION BY CERTIFICATE YourCert; 
GO
CREATE TABLE test (ID int IDENTITY(1,1), String varbinary(128));
GO
OPEN SYMMETRIC KEY YourKey 
  DECRYPTION BY CERTIFICATE YourCert
WITH PASSWORD = 'A Strong Password goes here, 1234567'; 
INSERT INTO test (String)
select EncryptByKey(Key_GUID('YourKey'),'182933892910')
union all
select EncryptByKey(Key_GUID('YourKey'),'134533893456')
union all
select EncryptByKey(Key_GUID('YourKey'),'239338929130')
union all
select EncryptByKey(Key_GUID('YourKey'),'182933896666')
union all
select EncryptByKey(Key_GUID('YourKey'),'182933898375');
CLOSE SYMMETRIC KEY YourKey ;
GO
--Notice, here, that all your values are encrypted. You can't now add the 4 characters to it.
SELECT *
FROM test;
GO
TRUNCATE TABLE test;
GO
--Thus, like Jeff suggested, you'd be better off with an extra column
ALTER TABLE test ADD String_partial varchar(20);
GO
--Then we can insert again (you wou;ld b using some other method, such as an SP (or if you have to, a trigger).
OPEN SYMMETRIC KEY YourKey 
   DECRYPTION BY CERTIFICATE YourCert
WITH PASSWORD = 'A Strong Password goes here, 1234567'; 
INSERT INTO test (String, String_partial)
select EncryptByKey(Key_GUID('YourKey'),'182933892910'),'xxxxxxxx' + RIGHT('182933892910',4)
union all
select EncryptByKey(Key_GUID('YourKey'),'134533893456'),'xxxxxxxx' + RIGHT('134533893456',4)
union all
select EncryptByKey(Key_GUID('YourKey'),'239338929130'),'xxxxxxxx' + RIGHT('239338929130',4)
union all
select EncryptByKey(Key_GUID('YourKey'),'182933896666'),'xxxxxxxx' + RIGHT('182933896666',4)
union all
select EncryptByKey(Key_GUID('YourKey'),'182933898375'),'xxxxxxxx' + RIGHT('182933898375',4);
CLOSE SYMMETRIC KEY YourKey ;
GO
--Now return your data again
SELECT *
FROM test;
GO
--Clean up
DROP TABLE test;
GO
DROP SYMMETRIC KEY YourKey; 
GO
DROP CERTIFICATE YourCert; 

Notice, in the data, that again the values of your encrypted data are varbinary. For example, the value of your first string is: 0x001A1A52F9D49E40AA91D97433C65CB402000000E961D3C24B494021EBEC8B6560B6E224493DBC0E9EA19DC16516EDBCCD9848A8D7362EE3636ED4791DD77569AB9F1345. That's your string, encrypted as a varbinary. The second column gives you the half picture, by having the last 4 characters, as that is stored as a literal value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

vsamantha35 SSChampion Points: 11529 More actions · Answer 5

Hi Thom,
Thank you for the example. Now, assume that the data is encrypted, suppose If someone has to decrypt the data, what is that he needs to have?? Is it symmentric key password right? just confirming? without that no one will be able to decrypt data ?

Thom A SSC Guru Points: 99126 More actions · Answer 6

vsamantha35 - Sunday, November 5, 2017 4:20 AM
Hi Thom,
Thank you for the example. Now, assume that the data is encrypted, suppose If someone has to decrypt the data, what is that he needs to have?? Is it symmentric key password right? just confirming? without that no one will be able to decrypt data ?

That's just one example, as it was rather easy to show, but in that instance, yes, they would need the password to the key 9thus access to the key), which is stored in that database as well. There are several methods, but we'd need to know which you'd prefer. The link I gave before has much more detail.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

vsamantha35 SSChampion Points: 11529 More actions · Answer 7

So, in general how are we going to share the key? Is that by taking a backup and providing them the password to other person to decrypt??

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

vsamantha35 - Sunday, November 5, 2017 12:53 AM
Sorry my bad. I want to encrypt and not masking the data. Editing the original post with correct expected output.

Again and regardless, the key is to use two columns. One where all 12 characters are totally encrypted and 1 where the last 4 characters are in plain text and preceded by whatever it is you wish to display.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

vsamantha35 - Sunday, November 5, 2017 5:47 AM
So, in general how are we going to share the key? Is that by taking a backup and providing them the password to other person to decrypt??

At this point, I'm going to recommend that you good folks hire an expert to do what you need so that you don't have to read about yourself in the morning news someday. Although encrypting and decrypting a column is an easy thing to do, it's too easy and not enough. If someone breaks into your machine as a privileged user, you're dead. The entire system must be correctly hardened, logins must follow a strict password policy and be seriously enforced, no application must operate with elevated privs, and the whole ball of wax should be penetration-tested by a company certified to do so. There's a whole lot more than just encrypting a column here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

Thom A - Sunday, November 5, 2017 3:00 AM
Jeff Moden - Saturday, November 4, 2017 8:42 PM
Don't even think of using dynamic data-masking for this because a half-wit that knows how to use a SELECT and the right kind of CONVERT can break DDM.
Thanks Jeff. I'll admit, I haven't looked at DDM yet; I don't have SQL 2016 at the office so not had the need.

You're not alone there, Thom. A whole lot of people have adopted the use of DDM instead of using proper encryption and it scares the hell out of me. 😉 My comment wasn't directed at you specifically (I hadn't actually read your post prior to posting). I just wanted to make sure that the OP knew of it's dangers and to stress the use of two columns instead of trying to do tricks that may cost them dearly in the near future.

Having read a little more about how they think they want to share the data indicates to me that they don't understand the full gravity of what they're trying to do and what they're trying to protect and strongly urge them to hire a pro and engage a certified pen-testing company.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vsamantha35 SSChampion Points: 11529 More actions · Answer 11

Hi Jeff.

Thanks for valuable suggestions. This is just a POC kind of thing try on my local machine. This is not a prod implementation. Kind of learning sake. However, really appreciate Thom and you for genuine suggestions. I am not going to play around with prod servers like that. not at all. I know my limitations very clear. I am just a SQL developer but interested in admin activities. I dont have permissions to do such things as well.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

vsamantha35 - Sunday, November 5, 2017 10:54 AM
Hi Jeff.
Thanks for valuable suggestions. This is just a POC kind of thing try on my local machine. This is not a prod implementation. Kind of learning sake. However, really appreciate Thom and you for genuine suggestions. I am not going to play around with prod servers like that. not at all. I know my limitations very clear. I am just a SQL developer but interested in admin activities. I dont have permissions to do such things as well.

Thanks for the feedback. I'll also say that I'd never tell someone that they're "just" a Developer. Good Developers are really hard to come by and you definitely sound like you have your head in the right place.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99126 More actions · Answer 13

Jeff Moden - Sunday, November 5, 2017 9:48 AM
Thom A - Sunday, November 5, 2017 3:00 AM
Jeff Moden - Saturday, November 4, 2017 8:42 PM
Don't even think of using dynamic data-masking for this because a half-wit that knows how to use a SELECT and the right kind of CONVERT can break DDM.
Thanks Jeff. I'll admit, I haven't looked at DDM yet; I don't have SQL 2016 at the office so not had the need.
You're not alone there, Thom. A whole lot of people have adopted the use of DDM instead of using proper encryption and it scares the hell out of me. 😉 My comment wasn't directed at you specifically (I hadn't actually read your post prior to posting). I just wanted to make sure that the OP knew of it's dangers and to stress the use of two columns instead of trying to do tricks that may cost them dearly in the near future.
Having read a little more about how they think they want to share the data indicates to me that they don't understand the full gravity of what they're trying to do and what they're trying to protect and strongly urge them to hire a pro and engage a certified pen-testing company.

No worries, I didn't think it was specifically aimed at me anyway. 🙂 You're knowledge of SQL Server far surpasses my own, so I always appreciate your insight.

vsamantha35 - Sunday, November 5, 2017 10:54 AM
Hi Jeff.
Thanks for valuable suggestions. This is just a POC kind of thing try on my local machine. This is not a prod implementation. Kind of learning sake. However, really appreciate Thom and you for genuine suggestions. I am not going to play around with prod servers like that. not at all. I know my limitations very clear. I am just a SQL developer but interested in admin activities. I dont have permissions to do such things as well.

Playing around is fine, in your own environment, just like Jeff said, if you're planning to do this in the work place, there's a lot of layers that just the database encryption, if that's the route you're planning. Like Jeff mentioned, you have to take into account users, their permissions, passwords, etc. The fact that a sysadmin can (often easily) get around it is another.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

David Burrows SSC Guru Points: 65138 More actions · Answer 14

Jeff Moden - Sunday, November 5, 2017 11:46 AM
I'll also say that I'd never tell someone that they're "just" a Developer.

Oh Jeff you're breaking my heart :crying:
I am "just" a developer, I just happen to pretend I can do the other things 😉

Far away is close at hand in the images of elsewhere.
Anon.