RE: Encrypt data - SQL Server 2000

From: Floyd Russell (floyd_at_neospire.net)
Date: 01/30/04

  • Next message: Steve Evans: "RE: SMTP Service in private DMZ OK?" 
    To: <focus-ms@securityfocus.com>
Date: Fri, 30 Jan 2004 11:08:33 -0600

    This may be a trivial note as this thread has been dead for a few days.
    However,
    in browsing around today I found this:
    http://www.devarticles.com/c/a/SQL-Server/Extended-Stored-Procedures-Intro-A
    nd-10-Cool-Examples/2/

    Particularly, the section labeled: Proc #3: xp_repl_encrypt

    Granted, this is only one way encryption, so it may not be completely
    relevant
    to the original poster. However, it does seem like an interesting
    alternative
    to using a 3rd party encryption package.

    Floyd Russell

    |> -----Original Message-----
    |> From: Fred Langston [mailto:Fred.Langston@guardent.com]
    |> Sent: Friday, January 23, 2004 12:37 AM
    |> To: 'Eric McCarty'; andreas; silkm@hushmail.com; kcasey@nanoweb.com;
    |> focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |>
    |> Yep, you missed the point on both. The thread has moved to general Db
    |> encryption questions, as the example is much more limited in
    |> scope than what
    |> most people are doing with Db encryption; namely, meeting HIPAA, GLBA,
    |> SB1386 and credit card company encryption requirements like Visa
    |> CISP. If
    |> you use EFS to encrypt, a compromised account that is used for EFS
    |> encryption will expose any data anywhere that's encrypted with
    |> EFS. If you
    |> use a non-user account based encryption technology, which is just about
    |> everything else except EFS and CFS, you can have a rogue admin or
    |> compromised admin account and *still not expose encrypted data*.
    |> Yes, you
    |> have big problems, but you will not have to go through the mandatory
    |> reporting or huge fines associated with the regulations. That's
    |> the first
    |> point.
    |>
    |> 2nd point - Most companies need the ability to encrypt portions
    |> of a record
    |> (credit card number, SSN, etc.) but still be able to search or
    |> query other
    |> fields in the database. In addition, the use of hashes of sensitive data
    |> fields stored together with the same encrypted data field allows queries
    |> based on comparing hash values rather than decrypting sensitive
    |> data fields
    |> or the database as a whole, lowering overhead and greatly increasing
    |> security. Your statement "Second, if a malicious attacker uses
    |> a web app to
    |> run queries against the DB, the attacker would get the unencrypted output
    |> anyway as he/she is running in the context of an account that SHOULD have
    |> access" is exactly why you should encrypt only rows/columns - you do not
    |> have the web app-user context problem! We're not allowing web
    |> app users to
    |> query credit card numbers, for example, because there's no
    |> business need; we
    |> are providing the ability query all other non-sensitive data
    |> fields so they
    |> can do there work. Ever so a receipt with XXs for the first 7
    |> digits of a
    |> credit card number? They do that so an account rep can handle
    |> issues with
    |> the order without giving them access to the sensitive data.
    |> It's all part
    |> of 'need-to-know access to data' concepts.
    |>
    |> Your book analogy escapes me.
    |>
    |> Business users never dictate security, but they absolutely
    |> dictate business
    |> requirements. If you can't provide the business function that a
    |> system is
    |> designed to provide, you utterly failed no matter how secure a system is.
    |> No one ever stayed in business with a system so secure that it
    |> does nothing.
    |>
    |> Fred Langston, CISSP
    |> Senior Principal Consultant
    |> W: 206.903.8147 x223 F: 206.903.1862 M: 425.765.3330
    |> Seattle, WA www.Guardent.com
    |> ________________________________________
    |> G U A R D E N T
    |> Enterprise Security and Privacy Programs
    |>
    |>
    |>
    |> -----Original Message-----
    |> From: Eric McCarty [mailto:eric@lawmpd.com]
    |> Sent: Thursday, January 22, 2004 7:59 AM
    |> To: Fred Langston; andreas; silkm@hushmail.com; kcasey@nanoweb.com;
    |> focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |>
    |> While I agree EFS is a poor choice for this purpose. Should you have a
    |> "compromised admin account or rogue admin anywhere on your domain..." I
    |> think
    |>
    |> * Annual employee salaries
    |> * Commissions
    |> * Wages
    |>
    |> Would be the least of your worries.
    |>
    |> I suppose I've missed the intent of encryption within a SQL db. Why would
    |> you want to encrypt data inside a database?. First, if people can run
    |> queries against these tables and get results, you have incorrect database
    |> security. Second, if a malicious attacker uses a web app to run queries
    |> against the DB, the attacker would get the unencrypted output anyway as
    |> he/she is running in the context of an account that SHOULD have access.
    |>
    |> If we use the analogy of a book, if you can't get to the book, you can't
    |> read it. If the need comes about to send the text of the book across the
    |> wire, you should use SSL to encrypt it and decrypt it so people
    |> can't read
    |> it.
    |>
    |> Lastly, since when did business users dictate security? *Flame suit on!*
    |>
    |> Eric McCarty
    |>
    |>
    |>
    |> -----Original Message-----
    |> From: Fred Langston [mailto:Fred.Langston@guardent.com]
    |> Sent: Wednesday, January 21, 2004 9:34 AM
    |> To: 'andreas'; silkm@hushmail.com; kcasey@nanoweb.com;
    |> focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |> EFS is probably the worst approach to this problem and should me
    |> avoided in
    |> all but the smallest installations when applied in this manner. You need
    |> encryption technology designed for this type of application, not a
    |> user/filesystem based encryption system.
    |>
    |> One compromised admin account or rogue admin anywhere on your
    |> domain and EFS
    |> is compromised as well. Performance is dismal as well if used for this
    |> purpose.
    |>
    |> EFS has it's place, but not here.
    |>
    |> Fred Langston, CISSP
    |> Senior Principal Consultant
    |> W: 206.903.8147 x223 F: 206.903.1862 M: 425.765.3330
    |> Seattle, WA www.Guardent.com
    |> ________________________________________
    |> G U A R D E N T
    |> Enterprise Security and Privacy Programs
    |>
    |>
    |>
    |> -----Original Message-----
    |> From: andreas [mailto:andreas@san-andreas.com]
    |> Sent: Tuesday, January 20, 2004 1:39 PM
    |> To: silkm@hushmail.com; kcasey@nanoweb.com; focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |>
    |> True, but my SQL guys are my ASP programmers. While correct
    |> application of
    |> DB encryption would preclude the DBAs from mucking about in the
    |> data, they
    |> very well may be the ones implementing the technology.
    |>
    |> There are plenty of resources, Microsoft being one of them, but as to it
    |> being applicable, understandable or available that is a different story.
    |> Technet is of little help when trying to figure out how to employ EFS for
    |> database protection or even get an idea of the best practices
    |> for having a
    |> single encrypted table.
    |>
    |> Andreas
    |>
    |> -----Original Message-----
    |> From: silkm@hushmail.com [mailto:silkm@hushmail.com]
    |> Sent: Tuesday, January 20, 2004 4:27 PM
    |> To: kcasey@nanoweb.com; focus-ms@securityfocus.com;
    |> andreas@san-andreas.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |> Well the point here is the DBA's don't need to know anything about the
    |> encrypted data ... just have them create tables to hold it.
    |>
    |> You need to educate your progammers on how to encrypt it, and there are
    |> plenty of resources for that no matter what language you choose.
    |>
    |>
    |> -----Original Message-----
    |> From: andreas [mailto:andreas@san-andreas.com]
    |> Sent: Wednesday, 21 January 2004 1:19 AM
    |> To: 'Kevin E. Casey'; focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |>
    |> Any good resources for researching/educating my DBAs? I have
    |> not had luck
    |> finding specific enough information on the subjects. My DB
    |> skills here are
    |> not as strong as I would like them to be, and not as strong as they will
    |> need to be in the future.
    |>
    |> Thanks!
    |>
    |> Andreas Barbiero
    |> CTO ETS/Financialcampus
    |>
    |> -----Original Message-----
    |> From: Kevin E. Casey [mailto:kcasey@nanoweb.com]
    |> Sent: Friday, January 16, 2004 12:01 PM
    |> To: focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |> If you need to encrypt data in 3 columns and 3 columns only,
    |> your best bet
    |> is to do the encryption at the application (in its data tier)
    |> level. Using
    |> .NET (or other tools), gives you a good range/assortment of tools and
    |> sencryption schemes to encrypt that confidential data. This
    |> keeps your DBAs
    |> from snooping around. Keeps backup copies safe from prying eyes
    |> and it also
    |> keeps the performance hit for en/decryption at the client (or web server
    |> level).
    |>
    |>
    |>
    |>
    |> -----Original Message-----
    |> From: Nero, Nick [mailto:Nick.Nero@disney.com]
    |> Sent: Thursday, January 15, 2004 5:09 PM
    |> To: Eduardo.Ortiz@alderwoods.com; focus-ms@securityfocus.com
    |> Subject: RE: Encrypt data - SQL Server 2000
    |>
    |> Encrypting data on a database is tricky. If you must have
    |> table/row level
    |> encryption, then it is really tough to find a decent product and
    |> performance
    |> is abyssmal. I recently authored a document that proposes using
    |> Microsoft's
    |> own EFS to encrypt the whole volume where the Database is. This solution
    |> was easy, performed great (about 5-25% hit on performance
    |> compared to 400%
    |> on DBCrypt) and best of all it is free. I would strongly recommend using
    |> Windows 2003 server for your SQL2k since its version of EFS uses AES at
    |> 256bit. Otherwise you need to hack the reg on Win2k to enable 3DES
    |> encryption. Either is not gonna get cracked by someone anytime soon. The
    |> beauty of this solution is that you encrypt the database with the SQL
    |> Service account so that only that account can read the data.
    |> That way even
    |> an local admin on the box cannot access the data. You could
    |> even boot to a
    |> NTFS boot disk and the data would be encrypted. This depends on
    |> proper key
    |> management (as all crytpo plans do) so you have to ensure you
    |> use a domain
    |> account or roaming profile so the encryption key can not be exploited
    |> locally (see http://www.elcomsoft.com/aefsdr.html for more on this
    |> exploit)
    |> and domain recovery agent policy. Still we feel it delivers extremely
    |> secure databases, acceptable performance and zero cost.
    |>
    |> We tested several products and I believe DBEncrypt (or maybe DbCrypt) was
    |> one of them. They all were several thousand dollars per server (and that
    |> was for a license of over 100 servers), and would require
    |> massive hardware
    |> investments to compensate for the performance penalty. Like I
    |> said, If you
    |> must have row/table level encryption to protect against other
    |> DBA's then you
    |> are stuck. At that point I would say you should either limit who has SA
    |> access, or more strongly background check those that do cause
    |> that level of
    |> encryption will cost you far more. A DB on an encrypted drive
    |> with strong
    |> application level security (ie, custom views), would only be breakable at
    |> the app or by getting SA credentials. There are far easier targets out
    |> there.
    |>
    |> -----Original Message-----
    |> From: Eduardo.Ortiz@alderwoods.com [mailto:Eduardo.Ortiz@alderwoods.com]
    |>
    |> Sent: Thursday, January 15, 2004 1:02 PM
    |> To: focus-ms@securityfocus.com
    |> Subject: Encrypt data - SQL Server 2000
    |>
    |> Hello,
    |>
    |> We are implementing an Enterprise Data Warehouse. We already have data
    |> regarding different business process. Now we need to include
    |> Payroll data in
    |> our SQL Server (2000) database. Business users have specific security
    |> requirements about this sensitive data. They want to secure the following
    |> information:
    |> * Annual employee salaries
    |> * Commissions
    |> * Wages
    |> This information is stored in two tables and are three different
    |> columns. We
    |> have already implemented a tight security schema for the server, database
    |> and user groups (active directory), but business users want more
    |> security.
    |> Now we are planning to encrypt the data (just these three
    |> columns) in the database. I did not find any function in SQL Server to
    |> encrypt data. I found a tool provided by Application Security Inc
    |> (http://www.appsecinc.com) called DbEncrypt. Have you guys heard
    |> or worked
    |> with tool? Do you any suggestion or recommendation to encrypt the data?
    |>
    |> Thanks,
    |> Eduardo Ortiz
    |>
    |>
    |>
    |> ---------------------------------------------------------------------
    |> ---
    |> ---
    |> ---------------------------------------------------------------------
    |> ---
    |> ---
    |>
    |>
    |>
    |> ---------------------------------------------------------------------
    |> ---
    |> ---
    |> ---------------------------------------------------------------------
    |> ---
    |> ---
    |>
    |>
    |> ---------------------------------------------------------------------
    |> ------
    |> ---------------------------------------------------------------------
    |> ------
    |>
    |>
    |>
    |>
    |> ---------------------------------------------------------------------
    |> ------
    |> ---------------------------------------------------------------------
    |> ------
    |>
    |>
    |>
    |>
    |>
    |> Concerned about your privacy? Follow this link to get FREE encrypted
    |> email:
    |> https://www.hushmail.com/?l=2
    |>
    |> Free, ultra-private instant messaging with Hush Messenger
    |> https://www.hushmail.com/services.php?subloc=messenger&l=434
    |>
    |> Promote security and make money with the Hushmail Affiliate Program:
    |> https://www.hushmail.com/about.php?subloc=affiliate&l=427
    |>
    |>
    |>
    |> ------------------------------------------------------------------------
    |> ---
    |> ------------------------------------------------------------------------
    |> ---
    |>
    |> ------------------------------------------------------------------------
    |> ---
    |> ------------------------------------------------------------------------
    |> ---
    |>
    |>
    |> -----------------------------------------------------------------
    |> ----------
    |> -----------------------------------------------------------------
    |> ----------
    |>
    |>

    ---------------------------------------------------------------------------
    ---------------------------------------------------------------------------

  • Next message: Steve Evans: "RE: SMTP Service in private DMZ OK?"

    Relevant Pages

    • Re: Application security question
      ... you want to implement security. ... So you are protecting the database from direct querying and altering ... login credentials for the database from the application. ... Why encrypt the password? ...
      (comp.lang.java.programmer)
    • Re: How To Protect Backup being restoted in any others from other serv
      ... And I completely forgot about the 3:rd party utils which can encrypt along with compression on the fly. ... >> The only "security" feature provided by SQL Server is password. ... >> other servers (in case Database backup stolen from us). ...
      (microsoft.public.sqlserver.server)
    • Re: SSN encryption
      ... >> We want to encrypt social security numbers in a database. ... address and SSN are always excluded. ... exposed if there were a breakdown in the other security precautions. ...
      (sci.crypt)
    • Re: Secure data in SQL Server 2005 Mobile Edition
      ... One other thing to try is not checking the encrypt option - the database still gets encrypted if you provide a password. ... replicate this database on SQL Server Mobile Edition on a mobile device. ... and whatever the perf hit, well that's the price you pay. ...
      (microsoft.public.sqlserver.ce)
    • Re: Which is more secure RC2 or RC4 ?
      ... same database temporarily, until the order is approved manually and the ... obviously there are a LOT of security related issues that arise ... itself in order to decrypt the information, ... meaning if I encrypt the information using AES and a password driven ...
      (sci.crypt)