April 19, 2013 at 7:09 am
Hello,
We have a database which needs to be secure in order for PCI compliance. What I am trying to establish is if there is a way of logging USERNAME and EXECUTION TIME each time DECRYPT_BY_CERT is run in SQL Server 2005.
Any advice would be most appreciated.
Andrew
April 23, 2013 at 6:33 am
In SQL 2005 Trace may be your best option. If you're just looking for ad hoc then SQL:StatementStarting might catch all your cases with a filter on Text for that function name.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2013 at 9:52 am
I've only created one database with column-level encryption for a live database so far, but I have done it by putting all code to decrypt and encrypt in stored procedures. This allows you to put any passphrases, etc. in the stored proc, which is then encrypted with the WITH ENCRYPTION clause. Then, in the SPROC, you can log anyone that uses the SPROC, by saving to a audit table who ran it with what parameters (record id values, dates, etc.). Just give the permissions to run the SPROC to the ones that should be running it.
This keeps you from moving any keys across the network, and you can make it to where the SPROC never gives ALL records of a table. That will keep anyone from being able to dump all the data.
If using column-level encryption, safeguard the backups. Anybody can get the backup and restore it to their own SQL Server system and run the SPROCs to get the information, since they would be an SA for their system. I know it is depricated, but you can use the backup with password option, so that nobody can restore the .bak without the password, and the column-level encryption will encrypt those columns of sensitive information so the .bak can't be read with a text editor.
Of course TDE would do the part that encrypts the data at rest (the backup) much better, because it encrypts the live data, the backups, and the log file backups. However, it is different in the sense that once you have gained access to the table info, you can see all the data, since it acts like a normal database as seen from the user standpoint. Column-level encryption adds the additional barrier in that the user has to also have access to the keys to decrypt the data, in addition to access to the table.
Hope this helps.
April 24, 2013 at 9:57 am
Some additional notes to those that may not know.
TDE is available only in SQL 2008 and above, and only in the Enterprise Edition.
Column-level encryption is available in SQL 2005 and above, even in all the Express Editions (free).
In both cases, make sure you backup and store securely the keys/certificates/SMK's. These would be needed to move the database to another server if needed.
April 29, 2013 at 3:19 am
Also, remember that if you create a SQL SP WITH ENCRYPTION, anybody who can get access to the SP can decrypt it. You may as well keep the SP in cleartext.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 29, 2013 at 7:32 am
"You may as well keep the SP in cleartext"
While some people know how to decrypt encrypted stored procedures, I personally subscribe to bad guys the same way I subscribe to a tornado, "Keep as many barriers as you can between you and the storm."
It may not keep the most resourceful or intelligent bad guy from getting it, but it could keep the casual hacker from it. Any day is a good day when your company doesn't end up in the headlines of the news for a data breach.
Just my 2 cents.
April 29, 2013 at 10:15 am
vikingDBA (4/29/2013)
"You may as well keep the SP in cleartext"While some people know how to decrypt encrypted stored procedures, I personally subscribe to bad guys the same way I subscribe to a tornado, "Keep as many barriers as you can between you and the storm."
It may not keep the most resourceful or intelligent bad guy from getting it, but it could keep the casual hacker from it. Any day is a good day when your company doesn't end up in the headlines of the news for a data breach.
Just my 2 cents.
+1. In this case it's not really encryption, more like obfuscation, and security by obfuscation is not really security. As long as you know that and are addressing other attack vectors with equal vigilence you'll be fine. Each attack vector should be given its due so I agree with your thinking 100% and the overall sentiment behind your comment, if I am reading it right. The more barriers you can place in front of an attacker, internal or external, the better off you'll be and the better chance you'll have at preventing a breach.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 29, 2013 at 2:13 pm
I'd like to mention here that typically when encrypting like this you store the data one place, the keys in another.
In general I'd have the N-Tier control the encrypt/decrypt, and the SQL Server store it. This way under normal breaks they can only get half of the pieces they need for the problem.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply