How to encrypt sensitve column while restore?

Question

How to encrypt sensitve column while restore?

SQL_Comp

SSChasing Mays

Points: 646
More actions
March 6, 2012 at 9:19 am

#252039

Dear All,
We 've environment where daily refresh is going on from PROD to lower env., But we need encrypt sensitive column on lower env?
🙂 🙂 🙂
Amey ghag
<a href='http://' class='authorlink' target='_blank'></a>

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Encrypt before you take the backup or encrypt after the restore. Can't be done during a restore, a restore recreates the database exactly as it was at time of backup.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

You could try some slight-of-hand. Restore the DB, then change your DB-context to the new DB and immediately set it to SINGLE_USER mode. This will lock everyone else out except you. Then you can carry on with your business of encrypting the sensitive data before prying eyes can have a look.

USE [master]

GO

RESTORE DATABASE [test]

FROM DISK = N'C:\test.bak'

WITH FILE = 1,

MOVE N'test' TO N'C:\DATA\test.mdf',

MOVE N'test_log' TO N'C:\Data\test.ldf',

NOUNLOAD,

--REPLACE,

STATS = 10 ;

GO

USE [test]

GO

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

-- do encryption routines here

GO

ALTER DATABASE test SET MULTI_USER

GO

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

vikingDBA SSCertifiable Points: 5356 More actions · Answer 3

SINGLE_USER mode allows only a single user at a time, but could be anyone.

RESTRICTED_USER only allows members of the db_owner fixed database role and the dbcreator and sysadmin fixed server roles to connect to the database (not normal users). It allows multiple users, but only those that belong to those roles. I would use this one. If a normal user jumps in and gets a connection, they have it, and you are locked out, even if you are sa.

Source: BOL.

BOL also notes that you should verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF if you use SINGLE_USER mode. From BOL:

"When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view."

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

vikingDBA (3/14/2012)
SINGLE_USER mode allows only a single user at a time, but could be anyone.

True, but you can guarantee you are the anyone 😀

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

opc.three (3/14/2012)
True, but you can guarantee you are the anyone 😀

How can you guarantee that? Once the database has been restored and you've set it single-user, you're then in a race condition to be the first to connect to it--if someone else happens to do so in the few seconds between you setting the single-user flag and you connecting, then you're in trouble. Plus the SQL Server Agent has a tendency to jump in first, too, so you'd have to disable that!

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

paul.knibbs (3/15/2012)
opc.three (3/14/2012)
True, but you can guarantee you are the anyone 😀
How can you guarantee that? Once the database has been restored and you've set it single-user, you're then in a race condition to be the first to connect to it--if someone else happens to do so in the few seconds between you setting the single-user flag and you connecting, then you're in trouble. Plus the SQL Server Agent has a tendency to jump in first, too, so you'd have to disable that!

The "single user" goes to the session that issues it. Just don't close the session until you set back to "multi-user" or your session will be gobbled up by web servers and the like. You also need to turn off asynchronus statistics updates before the issuing the single user command if you have it set to on.

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Good call on the async stats, Jeff.

@paul-2, notice in my code sample the "USE [test]" immediately after the RESTORE and before we set it to SINGLE_USER. In essence this guarantees you become the user of the database. As Jeff pointed out, keep that session open and no one else will be able to connect to the DB until it is changed away from SINGLE_USER mode.

Something else came to mind while writing this, anyone with CONTROL SERVER privileges or in the sysadmin role can change the database mode. So, if you're trying to protect this data post-restore from others with those privileges the approach may not work for you as they could change the mode to MULTI_USER or kill your session and become the single user of the DB.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato