MS SQL Server: Single Column Encryption

Discussion in 'Databases' started by X P S, Jan 6, 2012.

0
  1. #1
    Hi,

    Database: Microsoft SQL Server 2000
    Operating System: Windows NT 5.2
    Application: ASP.NET

    Table Name: Login
    Columns: username, password, name

    When I run this query:

    SELECT password FROM Login WHERE name='Bill Gates'
    Code (markup):
    The output is:

    So the password field is encrypted.

    But when I run this query:

    SELECT name FROM Login WHERE password='ankn67FBrOQ='
    Code (markup):
    Then it shows an empty set. Why is that?

    Secondly is there a way that I can find if the password is encrypted via SQL Server Cipher or ASP.NET encryption/hash?

    Regards
    XPS
     
    X P S, Jan 6, 2012 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    What is the field type for password? Most likely ankn67FBrOQ= is an encoded string. You would need to enter the base password to return a row.
     
    jestep, Jan 10, 2012 IP
  3. X P S

    X P S Well-Known Member

    Messages:
    500
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    110
    #3
    It's varchar. And no, if I use the original password still it doesn't return anything.
     
    X P S, Jan 10, 2012 IP
  4. cr111

    cr111 Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    fdfmfdnsmfsnf
     
    cr111, Jan 11, 2012 IP
  5. cashcars

    cashcars Peon

    Messages:
    112
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    0
    #5
    [h=5]TDE[/h] Transparent data encryption is the new database-level encryption feature introduced in SQL Server 2008.
    [h=6]How to Enable TDE[/h] To enable TDE, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.
    To enable TDE
    Perform the following steps in the master database:
    1. If it does not already exist, create a database master key (DMK) for the master database. Ensure that the database master key is encrypted by the service master key (SMK).
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘some password’;

    2. Either create or designate an existing certificate for use as the database encryption key (DEK) protector. For the best security, it is recommended that you create a new certificate whose only function is to protect the DEK. Ensure that this certificate is protected by the DMK.
    CREATE CERTIFICATE tdeCert WITH SUBJECT = ‘TDE Certificate’;

    3. Create a backup of the certificate with the private key and store it in a secure location. (Note that the private key is stored in a separate file—be sure to keep both files). Be sure to maintain backups of the certificate as data loss may occur otherwise.
    BACKUP CERTIFICATE tdeCert TO FILE = ‘path_to_file’
    WITH PRIVATE KEY (
    FILE = ‘path_to_private_key_file’,
    ENCRYPTION BY PASSWORD = ‘cert password’);

    4. Optionally, enable SSL on the server to protect data in transit.
    Perform the following steps in the user database. These require CONTROL permissions on the database.
    5. Create the database encryption key (DEK) encrypted with the certificate designated from step 2 above. This certificate is referenced as a server certificate to distinguish it from other certificates that may be stored in the user database.
    CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE tdeCert

    6. Enable TDE. This command starts a background thread (referred to as the encryption scan), which runs asynchronously.
    ALTER DATABASE myDatabase SET ENCRYPTION ON

    To monitor progress, query the sys.dm_database_encryption_keys view (the VIEW SERVER STATE permission is required) as in the following example:
    SELECT db_name(database_id), encryption_state
    FROM sys.dm_database_encryption_keys
     
    cashcars, Jan 13, 2012 IP