RE: Encrypt data - SQL Server 2000

From: Gates, Gerald A (Jerry) (JerryGates_at_fairisaac.com)
Date: 01/22/04

  • Next message: Paul Navarre: "RE: Microsoft Security (...how to reassure customers of)" 
    Date: Thu, 22 Jan 2004 12:21:18 -0600
To: <focus-ms@securityfocus.com>

    We routinely encrypt data in certain columns of our SQL server
    databases. We use the encryption facilities provided by the .Net
    framework and the Java Crypto API to encrypt and decrypt the data at the
    application level.

    In order to get around the general inconvenience of having binary data
    in our databases, we MIME encode the data after encrypting it (and MIME
    decode it before decrypting). This allows us to store the data as
    Varchar instead of binary. The data is a little bigger (about 33%), but
    we can index and search it.

    Later,
    Jerry Gates

    -----Original Message-----
    From: Mr. Momotaro [mailto:momotaro@catholic.org]
    Sent: Thursday, January 22, 2004 9:48 AM
    To: 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.
    >

    I'll piggy-back here that EFS doesn't cut it. EFS doesn't stop the DBAs
    from seeing the data. EFS has to be configured in such a way SQL Server
    has access to the file. Since SQL Server has access, the DBA will be
    able
    to see into the table. Therefore, EFS will stop someone from stopping
    the
    SQL Server service and copying off the data file if they are familiar
    with
    this approach but aren't familiar with EFS.

    The last time I talked to someone from Microsoft about this (a couple of
    years ago, albeit), I believe Protegrity (http://www.protegrity.com/)
    was
    the recommendation for encrypting at the table or column level.

    Yes, you're going to suffer a performance hit because SQL Server won't
    be
    able to index on the data, but there's always a trade-off.

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

    ---
------------------------------------------------------------------------
---
---------------------------------------------------------------------------
---------------------------------------------------------------------------
  • Next message: Paul Navarre: "RE: Microsoft Security (...how to reassure customers of)"

    Relevant Pages

    • Re: SQL Server 2000 / 2005 Encryption
      ... to encrypt your SSL connections will be different. ... SQL Server can generate its own self-signed certificates though, and that should make the SSL encryption/connectivity easier for you. ... Excel does not know how to decrypt data stored in SQL Server 2005 in encrypted form. ...
      (microsoft.public.sqlserver.security)
    • Re: Storing Connection String
      ... you can encrypt the traffic to the SQL Server. ... Hitchhiker's Guide to Visual Studio and SQL Server ... My idea is to have just one database user whose username and password ... Which means that if an user get hold of connection string ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Securing an MDF file
      ... Windows 2000 support encrypted file system property. ... Below are the steps encrypt the data files: ... Logon with the SQL Server startup account ... database files are encrypted under the identity of the account ...
      (microsoft.public.sqlserver.server)
    • Re: securing mdf files
      ... You can use the Encrypted File System Support on Windows 2000. ... Below are the steps encrypt the data files: ... Logon with the SQL Server startup account ... database files are encrypted under the identity of the account ...
      (microsoft.public.sqlserver.security)
    • Re: Encription for MDF files
      ... EFS by itself is not a complete solution. ... If the box is stolen - including the EFS recovery keys, ... then encrypt it before it is stored and decrypt it when retrieving. ... > 1) Logon with the SQL Server startup account ...
      (microsoft.public.sqlserver.security)