Hi Brain,

Thanks for above article but my question here

how we can enrypt many tables at once, do we need to write query again and again i.e. same script for all tables or is there any single script for this

Thanks

Abhi

Putting in SQL Server's built-in encryption after the fact is a time-consuming process. There's no quick way to do this. You'd have to build the scripts to create the varbinary columns, then encrypt the data into them, and then remove the old columns. As a result, there's nothing within SQL Server that would let you do this quickly and easily, especially for many tables.

You're probably going to need to look at a 3rd party product if that's the case. There have been some on the market that do just that: encapsulate whole tables and keep the data away from all eyes that don't come through the product, even the eyes of the DBAs.

If you try encryption with a password, you must keep it secure.  More importantly, when people that know the password are no longer in positions where they should know the password, or when those who aren't in positions where they should know the password find it out (usually during some emergency... or an "emergency"), the password should be changed.

Therefore, one needs to consider how the passwords will be changed.

Further, guessing the password _does_ work once you guess successfully.  Therefore, you cannot use passwords like those listed if you actually want to keep your data secure against those using serious dictionary and rules-based dictionary password crackers.  A three word combination plus a couple numbers in front and three number/symbols at the end (_especially_ 1 or ! or 123, which are very commonly used) is going to be vulnerable to a serious brute-force effort... which can be done, by anyony with access to database backups (DBA), offline and on multiple machines at once.  Backup the database, take the backup offsite, crack to your heart's content.

Note that the very requirement to keep the data hidden from DBA's indicates that DBA's are not and cannot be trusted with this data per the rules of the organization, therefore, the security should be sufficient to protect against an active cracking attempt by said untrusted DBA's.

Good passwords are completely random.  Since completely random passwords are hard for most people to type, they should be stored in a secure, encrypted manner (Keepass 2.x, Office 2007+ Prepare, Encrypt, FreeOTFE drive, Truecrypt drive, etc.), and then copy and pasted when required.  Once you're at the stage of copy and pasting the password, length and complexity cease to matter; therefore, a maximum length, maximum complexity password is no more difficult to use in practice than a minimally secure password, and if you use maximums, you don't have to quibble and argue and debate over what the acceptable minimum is... nor do you have to change when the acceptable minimum changes due to law, regulation, or corporate decision.  For SQL Server, 128 characters long (maximum length) and including extended ASCII is valid.  For some programming languages, extended ASCII and certain symbols are problematic, but at length 128, even pure random digits (no symbols, no letters) gives 1E128 possible passwords, which is a slightly greater value than 2^384 (3E115, which is equivalent to a length 65 upper, lower, number random passwords where the characters in the password are not known), i.e. it's easier to brute force a 384 bit binary key.

I.e. a great password is:

qH!0M2NnUD0ZRr05Fvsnw+V?}!HGrXey(Jdh\5(P1F:H@rOq*r8#k9omD)y[jsz\_tYp#6?pd.\}fXp~AQ=pd5@sk1Y^H2tpV~fAf-4a5!X]X{.iLrvIaMan~9[HaHJ9

or

F0lV4aKc4qkBlwxg5SotzZqeAebwB6DnsAJp0ivJzYsx0fWQFLlYi7XhbUwmtVKNB3mRPiu5Ohcge7oDyu28ZgDzD4WnRQlYM2NcXLBh8bp25haLJcryKrldEWp8WKSj

and a good password is:

AauOdH2NeznzRapF3WRIJ6P37jj4sELpQobCgBTuWLKGjzv7aa5RbJcRgw0b0cxHXMACgtELXObDfTX7vLPg0uMr2Vwag0FQcl6i1CzmpMr1nBvVZmBCgDnz2H3577Fj

or

00612873175251783317215595986222170103437102201738622871651139904548606931195872422709496264462579332922109497631666156269839343

"Note that the very requirement to keep the data hidden from DBA's indicates that DBA's are not and cannot be trusted with this data per the rules of the organization"

Let me correct something right away. The fact that you encrypt a data so a DBA can't see it doesn't say anythign about a DBA being trusted or not. Case in point: classified data in the military. I carried a secret clearance when I was in. However, I would only be allowed to view documents and material that were applicable to the job I did. This is known as "need to know." It's not that anyone is inherently untrustworthy. If that was the case, I wouldn't have had a secret clearance. And that would mean many, many folks that carry secret and top secret clearances would be untrustworthy, too.

The reason for need to know is several fold:

• If you don't have access to the data, YOU have repudiation.
• The less folks who have access to the data, the less folks you have to check out in case there is a disclosure.
• The less folks who have access to the data, the less likely it will be disclosed, especially in an ACCIDENTAL manner.
• Sometimes being able to see multiple sets of data at a lower level allows you to put the pieces together for data a higher level. This is why the military makes a big deal about Operational Security or OPSEC. So you protect all the lower levels as much as possible.

Question, can you implement this on a database being used by a third party application?  Will the application still work with no changes?  Does SQL Server decrypt everything?  OR will the application need amending to accomodate the encryption?

This is a great article, thank you very much for posting it.  It relates to a question that came up somewhat recently on Ask.SQLServerCentral.

One thing you mention, but that bears repeating is to make sure that keys and passwords are appropriately backed up.  Losting those is effectively tantamount to losing the encrypted column.

This can't be used with a third party app because opening the key in order to encrypt/decrypt the data must be called by the same SPID. As you might guess, this means that third-party app would need to know to do so.

Aye, Timothy, that's very true. When you are dealing with encryption, you must have an absolutely sound plan for securing and storing the keys and/or passwords used. This is true whether we're talking SQL Server, EFS, Bitlocker, or some other encryption process.

Is the data sent unencyprted on the network? Meaning is it unencrypted at the SQL Server end or at the application client end? Otherwise WireShark comes to mind...

Yes, the data would be sent across the wire unencrypted. However, remember that with respect to Wireshark, it could be looked for, especially with tools like Altiris and System Center - Configuration Manager. Seeing it installed would be a red flag. That doesn't stop the networking folks from using a span port.

With that said, it is possible, using IPSEC policies, to ensure ALL database traffic is encrypted between client and server. This is transparent to SQL Server. And then, if SQL Server is so configured, it could ensure that everything transmitted is encrypted as well. This would get around a packet sniffer, whether installed on the host or via a span port.

Brian, Norman Heyen,

I would like to mention we have quite unique tool for database encryption in SQL Server which solves the problem with Wireshark and SQL Profiler. It's name DbDefence (can be found easilty if you are interested)  It can:

-Encrypt all incoming SQL statements while transmitted over network protocols.

-Encrypt database files complely.

-Do not let DBA see database catalog

-Hide all related SQL statements from Profiler

-Automatically encrypt backups

-Encrypt database and wrap fontend application to use encrypted database without chaning ANYTHING in front-end.

-free version for small databases.

Please do not consider my post as advertising. I think that's very related to the problem and could be a solution for many.