Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PCI Compliance Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 7:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:59 AM
Points: 1,356, Visits: 1,180
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
Post #1444374
Posted Tuesday, April 23, 2013 6:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1445386
Posted Wednesday, April 24, 2013 9:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:59 AM
Points: 191, Visits: 904
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.
Post #1446084
Posted Wednesday, April 24, 2013 9:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:59 AM
Points: 191, Visits: 904
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.

Post #1446087
Posted Monday, April 29, 2013 3:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,900, Visits: 3,301
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 18 October 2014: now over 31,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1447413
Posted Monday, April 29, 2013 7:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 12, 2014 11:59 AM
Points: 191, Visits: 904
"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.
Post #1447511
Posted Monday, April 29, 2013 10:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1447636
Posted Monday, April 29, 2013 2:13 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, December 15, 2014 2:26 PM
Points: 5,466, Visits: 7,647
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.



- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1447760
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse