locked
SQL server Encrption RRS feed

  • Question

  • We would like to implement the Encryption for database and corresponding objects .

    Could you please advice me which is best and advantages and disadvantages 

    Monday, December 12, 2016 12:04 PM

All replies

  • Do you want to hide the data and/or the logic written in stored procedures?

    USE AdventureWorks;
    GO

    --If there is no master key, create one now 
    IF NOT EXISTS 
        (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
        CREATE MASTER KEY ENCRYPTION BY 
        PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478dDkjdahflkujaslekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
    GO

    CREATE CERTIFICATE HumanResources037
       WITH SUBJECT = 'Employee Social Security Numbers';
    GO

    CREATE SYMMETRIC KEY SSN_Key_01
        WITH ALGORITHM = Triple_DES
        ENCRYPTION BY CERTIFICATE HumanResources037;
    GO

    USE [AdventureWorks];
    GO

    -- Create a column in which to store the encrypted data
    ALTER TABLE HumanResources.Employee
        ADD EncryptedNationalIDNumber varbinary(128); 
    GO
    select * from HumanResources.Employee
    -- Open the symmetric key with which to encrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;

    -- Encrypt the value in column NationalIDNumber with symmetric 
    -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.
    UPDATE HumanResources.Employee
    SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);
    GO

    -- Verify the encryption.
    -- First, open the symmetric key with which to decrypt the data
    OPEN SYMMETRIC KEY SSN_Key_01
       DECRYPTION BY CERTIFICATE HumanResources037;
    GO

    -- Now list the original ID, the encrypted ID, and the 
    -- decrypted ciphertext. If the decryption worked, the original
    -- and the decrypted ID will match.
    SELECT NationalIDNumber, EncryptedNationalIDNumber 
        AS "Encrypted ID Number",
        CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) 
        AS "Decrypted ID Number"
        FROM HumanResources.Employee;
    GO


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 12, 2016 1:01 PM
  • What or who do you want to protect against? Unless we know more details it is difficult to suggest a technology.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, December 12, 2016 9:41 PM
  • Hi SQLHELP007,

    Which version of SQL Server do you use? In the version before SQL Server 2016, SQL Server provide two provides, Column-level Encryption and Transparent Data Encryption (TDE), from SQL Server 2016, we can use another two provides, Row-level Encryption and Always Encrypted. Every provide has its advantages and disadvantages, we need to select one or more based on the requirement. We cannot say which is the best one without usage scenarios.

    The method given from Uri Dimant is Column-level Encryption, the most common provide. We can use this method to control access to columns that we don't want to show to others like keyword, Credit ID. 

    Row-level Encryption is a provide which enables customers to control access to rows in a database table based on the characteristics of the user executing a query. You can add a FILTER or BLOCK to the table, then the row you encrypted will not be seen when query.

    TDE which performs real-time I/O encryption and decryption of the data and log files is a technology that control access to the data on the disk, for example, if your disk was stolen by someone, then because you have enabled TDE, he will not get the data of the full database, however, this method will reduce the performance of SQL Server.

    Always Encrypted is a provide designed to protect sensitive data, such as credit card numbers or national identification numbers. In many cases, we use it to makes encryption between applications and database.

    If you have any other questions, please let me know.

    Best Regards,
    Teige

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    • Edited by Teige Gao Tuesday, December 13, 2016 9:57 AM
    • Proposed as answer by Teige Gao Friday, December 23, 2016 2:33 AM
    Tuesday, December 13, 2016 2:36 AM