Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    21

    Red face Unanswered: SQL 2008 R2 Express

    Hi all,
    Is there any way to encrypt a column in SQL EXPRESS 2008 R2. Table will be queried from a .Net application.
    I want to encrypt only specific columns: That is, as surname column and work location code column. I have searched the internet for over an hour now and any solutions seem to refer to SQL Entreprise.
    Yours
    Morke

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    Store the encrypted values in the table. Let the encrypt and ecrypt logic be in .Net application.

    thoughts??
    Cheers....

    baburajv

  3. #3
    Join Date
    Jul 2009
    Posts
    21
    Hi,
    Thats was one of my (MANY) thoughts. I have been using a routine to encrypt bank account and SSI numbers going back to Clipper/DBF days. I have already tried something similar with surnames and the first two address lines, however, it has a serious impact on speed in a 'Find' module which incrementally refines name and other contact details in a grid as a user fills out details.
    Thanks,
    Morke

  4. #4
    Join Date
    Sep 2011
    Posts
    71
    HI ,Try this procedures
    There are two different kinds of encryptions available in SQL Server:

    Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution.
    Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption. If a column is used as a primary key or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to perform operations involving those columns.

    Let’s go over a simple instance that demonstrates the encryption and the decryption process executed with Symmetric Key and Triple DES encryption algorithm.

    /* Create Database */
    USE master
    GO
    CREATE DATABASE EncryptTest
    ON PRIMARY ( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')
    LOG ON ( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')
    GO

    First, let’s create a sample table and then populate it with sample data. We will now encrypt one of the two columns of the table.

    /* Create table and insert data in the table */
    USE EncryptTest
    GO
    CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
    GO
    INSERT INTO TestTable (FirstCol, SecondCol)
    SELECT 1,'First'
    UNION ALL
    SELECT 2,'Second'
    UNION ALL
    SELECT 3,'Third'
    UNION ALL
    SELECT 4,'Fourth'
    UNION ALL
    SELECT 5,'Fifth'
    GO
    /* Check the content of the TestTable */
    USE EncryptTest
    GO
    SELECT *
    FROM TestTable
    GO

    The preceding code will return the result depicted in the subsequent figure.

    Result of the SQL query

    Every database can have one master key. Database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm together with user-provided password to encrypt the keys.

    /* Create Database Master Key */
    USE EncryptTest
    GO
    CREATE MASTER KEY ENCRYPTION
    BY PASSWORD = 'SQLAuthority'
    GO

    Certificates are used to safeguard encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the capability to generate self-signed X.509 certificates.

    /* Create Encryption Certificate */
    USE EncryptTest
    GO
    CREATE CERTIFICATE EncryptTestCert
    WITH SUBJECT = 'SQLAuthority'
    GO
    For more details you can check below link
    SQL SERVER – Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial with Script Journey to SQLAuthority

  5. #5
    Join Date
    Jul 2009
    Posts
    21
    Hi,
    Thanks very much for the excellent reply. What is your view on SQL2008 Enterprise R2 with TDE.
    Yours,
    Mick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •