Need A Help in DATA MASKING in SQL SERVER 2008

Question

Post reply

Need A Help in DATA MASKING in SQL SERVER 2008

Learner44

SSCarpal Tunnel

Points: 4466
More actions
July 19, 2013 at 8:44 am

#280692

Hello Friends,
I want to mask certain fields in employee_bank_account_info table, which are very sentive information.
I searched for it , i found verious third-party tools online, which I can not use in my current enviornment.
I need help to find out some way of data masking via sql script or use of any inbuilt function or library in sql server 2008!!!
Please give me any suggestion or example about this.
thanks in advance.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Start here.

http://msdn.microsoft.com/en-us/library/ms179331.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 2

Learner44

SSCarpal Tunnel

Points: 4466

July 19, 2013 at 8:54 am

#1634068

perfact...

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 3

Hi,

I successfully applied/used symmetric key in order to encrypt the one particular column data.

it also show me that real number - > encrypted numbers -> decryted number(real number).

Now, again I am on the same position as I was , few months back.

My quetion is how to test this case.

Like I encrypted this coulmn data, by createing Master Key-> Certificate-> Symmetric Key -> encrypt the data.

when I want I can simply use the "DecryptByKey" and decrypt it..

BUt, How I can test this thing with other user.

As, they are not allowed to see the decryptedata,because they are normal users.

How I can show this , that normal user can not see the decrpted data.

Please Help.

Thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Well you know have kind of shifted gears. At first you wanted to protect the data. That is a good idea with any sensitive information. It sounds like you have encrypted those columns now. That means that data is protected at rest.

Now however you want to disallow selecting certain columns to certain users. This is a different animal. The article here does a good of explaining how you can implement this with column level permissions.

http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/[/url]

Another way it to use views. Here is a decent article that explains a way to do that.

http://www.mssqltips.com/sqlservertip/2125/filtering-columns-in-sql-server-using-views/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 5

Hi,

Thanks for those good articles. It solves my half purpose.

As there number of user for couple of tables , in which case we can not assign different roles to everybody, i guess, and also I can not create that many views from all those tables.

I want to upgrade my logic with your valuable help , what is the usefulness of the encryption which I performed on the particular column.?

I mean to say under my login id in SQL SERVER MANAGMENT STUDIO\server23, I created that master key, certificate and encrypted the data.

Now other users who works with me, also have the access to this same server instance, I just want them to see the table with enrcypted data in one column(Which I have encrypted). how I can achive this.?

Thanks in advance for your help.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

I want to upgrade my logic with your valuable help , what is the usefulness of the encryption which I performed on the particular column.?

The data is now encrypted at rest. This is very important for security. If somebody gains access or is somehow able to select * from your table, the results are gibberish for those columns.

Now other users who works with me, also have the access to this same server instance, I just want them to see the table with enrcypted data in one column(Which I have encrypted). how I can achive this.?

If you want others to not be able to view the decrypted information the easiest way by is to not give them the key. 😉 They can select the column all they want but it is indecipherable without the key. Is that what you are asking?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 7

Yes, This wxactly what I want..

But, Here is the thing, The first document which you have provided me, as you know I suceessfully understood it and appield it too.

No I can see two columns like AccountNumber, and EncryptedAccountNumber,

In short, table has now one more column , called EncryptedAccountNumber, which has encrypted sensitive data,

so what I should do with original column(AccountNumber), Which I don't want other member to see it.

As my role is, I can do any DDL and DML operation in this server instant, which is same as my co-worker

and I also told one of my co-worker to check from his account via accessing the same database and the same table, and I found out he can still see both AccountNumber, and EncryptedAccountNumber columns which doesn't solve the purpose.

I am just missing somthing at some point, otherwise I know this thing should not happen.

Thanks again, in advance for your time and support.

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

Are you saying that the EncryptedAccountNumber an encrypted version of the visible AccountNumber column?

--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)

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 9

yes...You are right..

In my tabel there is one column with sensitive data, called account number.

On which I successfully applied, coulmn encryption, after createing new coulmn called EncryptedAccountNumber.

Now My table show both original "AccounNumber" and EncryptedAccountNumber" column in select query.

I have to show to my boss that no other user can see that original column..while he/she looks into table.

I successfully applied to that column encryption.

We have our server name is like "SQSERVERSTUDIO\sqlserver2008" , and everybody has their own windows authentication,via which they login and access databases.

I am now stuck , how I can restrict other user with that specific table and specific column, which allows them to see only "EncrytedAccountNumber" column, not the actual "AccountNUmber"

and , here the enviornment is all the user can access same tables with its lates update by any other user, before that login.

Please help me.

Thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Why do you need to have both encrypted and clear text columns? This defeats the whole point of column level encryption. You haven't protected the sensitive information at all this way.

The only way I know of to get around this would be to deny select permission on the table to any users that you don't want to view the clear text version. Then create a view that does not contain that column and grant them select permission on the view.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 11

Sean Lange (7/22/2013)

Why do you need to have both encrypted and clear text columns? This defeats the whole point of column level encryption. You haven't protected the sensitive information at all this way.[/

You are right on the target..That's also I am wondering , after applying folowing steps..

I have used following as a reference ..and use my account number column, in bank table in my client database,so only column, table and databases name are changed otherwise evrything is same.

USE AdventureWorks;

GO

--If there is no master key, create one now.

IF NOT EXISTS

(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

CREATE MASTER KEY ENCRYPTION BY

PASSWORD = ''

GO

CREATE CERTIFICATE HumanResources037

WITH SUBJECT = 'Employee Social Security Numbers';

GO

CREATE SYMMETRIC KEY SSN_Key_01

WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE HumanResources037;

GO

USE [AdventureWorks];

GO

-- Create a column in which to store the encrypted data.

ALTER TABLE HumanResources.Employee

ADD EncryptedNationalIDNumber varbinary(128);

GO

-- Open the symmetric key with which to encrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

-- Encrypt the value in column NationalIDNumber with symmetric

-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.

UPDATE HumanResources.Employee

SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);

GO

-- Verify the encryption.

-- First, open the symmetric key with which to decrypt the data.

OPEN SYMMETRIC KEY SSN_Key_01

DECRYPTION BY CERTIFICATE HumanResources037;

GO

-- Now list the original ID, the encrypted ID, and the

-- decrypted ciphertext. If the decryption worked, the original

-- and the decrypted ID will match.

SELECT NationalIDNumber, EncryptedNationalIDNumber

AS 'Encrypted ID Number',

CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))

AS 'Decrypted ID Number'

FROM HumanResources.Employee;

GO

Now , when I use select * from table name

it gives me whole table plus one encrypted column...and I don't know...why it is comming..[/b]

even in the "AdventureWorks" when I run above exact query it gives both original(clear text) and encryted text, here is the out put.(Please find the attachment)

The only way I know of to get around this would be to deny select permission on the table to any users that you don't want to view the clear text version. Then create a view that does not contain that column and grant them select permission on the view.

Yes , we can surely do this but, my senior staff, ask me that once you have done encyption, why we have to create a view, to fillter restricted columns, which shows encryption is not done...properlly..

Now , what should I do...

Please let me know if need any further information, to help me.

thanks a million.

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

I would drop the column with clear text. You have two copies of this information now, 1 is encrypted and the other is not. If the only column you have is encrypted, then all you have to do to prevent people from viewing it is to not let them have the key.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 13

I would agree with Sean. However if you need the plain text column for your application to continue to work (legacy code), I'd set it to blank.

Learner44 SSCarpal Tunnel Points: 4466 More actions · Answer 14

Ok..I got your point Sean and Steve...Thanks for that.

I just checked the encryption from one of my co-worker, under her login name and password , but on the same server.

she has the same view as I have, I mean clear text column(Account Number) and Encrypted coulmn(EncryptedAccountNumber)...

I found my certificate and symmetric key under the "Security-> Certificate" folder and "Security-> Symmetric Key" folder respectively.

So , as you are saying that I should not provide the key to other user, whom I don't want to see that clear text column..How I can do that.?

The reson why I am asking this is that, it is there on the server and who ever logs on into that server have the certificate and symmetric key under the folder which I have metioned above.

Is it possible that I save them somewhere else , out of the server?

and YES , YOU BOTH ARE RIGHT, THAT I CAN DELETE THE COLUMN WITH CLEAR TEXT AS I HAVE NOW ENCRYPTED TEXT, BUT IT IS GOOD PRACTICE TO DO THAT..BECAUSE I HAVE 40+ TABLES....PLEASE LET ME KNOW SO THAT I CAN START FOLLOWING THIS OPTION..

THANKS TO BOTH OFF YOU FOR YOUR TIME AND SUPPORT.

REALLY..