Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PCI Compliance


PCI Compliance

Author
Message
DBA From The Cold
DBA From The Cold
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 1728
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
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
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.
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
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.
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3150 Visits: 3819
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "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
vikingDBA
vikingDBA
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 929
"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.
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5701 Visits: 7660
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search