データの列の暗号化Encrypt a Column of Data

適用対象: ○SQL Server ○Azure SQL Database XAzure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

この記事では、Transact-SQLTransact-SQL を使用して SQL Server 2017SQL Server 2017 で対称暗号化を使用してデータ列を暗号化する方法について説明します。This article describes how to encrypt a column of data by using symmetric encryption in SQL Server 2017SQL Server 2017 using Transact-SQLTransact-SQL. これは、列レベルの暗号化、またはセル レベルの暗号化とも呼ばれます。This is sometimes known as column-level encryption, or cell-level encryption.

SecuritySecurity

アクセス許可Permissions

次の手順を実行するには、次の権限が必要です。The following permissions are necessary to perform the steps below:

  • データベースに対する CONTROL 権限。CONTROL permission on the database.

  • データベースに対する CREATE CERTIFICATE 権限。CREATE CERTIFICATE permission on the database. 証明書を所有できるのは、Windows ログイン、ログイン、およびアプリケーション ロールだけです。Only Windows logins, SQL Server logins, and application roles can own certificates. グループとロールは証明書を所有できません。Groups and roles cannot own certificates.

  • テーブルに対する ALTER 権限。ALTER permission on the table.

  • キーに対する権限。VIEW DEFINITION 権限が拒否されていないことが必要です。Some permission on the key and must not have been denied VIEW DEFINITION permission.

Transact-SQL の使用Using Transact-SQL

次の例を使うには、データベース マスター キーが必要です。To use the following examples, you must have a database master key. データベースにデータベース マスター キーがまだない場合、次のステートメントを実行してパスワードを提供することにより作成します。If your database does not already have a database master key, create one by executing the following statement providing your password:

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<some strong password>';  

データベース マスター キーは常にバックアップしてください。Always back up your database master key. データベース マスター キーの詳細については、「CREATE MASTER KEY (Transact-SQL)」をご覧ください。For more information on database master keys, see CREATE MASTER KEY (Transact-SQL).

認証子を含む対称暗号化を使用して列を暗号化するにはTo encrypt a column of data using symmetric encryption that includes an authenticator

  1. オブジェクト エクスプローラーで、 データベース エンジンDatabase Engineのインスタンスに接続します。In Object Explorer, connect to an instance of データベース エンジンDatabase Engine.

  2. [標準] ツール バーの [新しいクエリ] をクリックします。On the Standard bar, click New Query.

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] をクリックします。Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    
    CREATE CERTIFICATE Sales09  
       WITH SUBJECT = 'Customer Credit Card Numbers';  
    GO  
    
    CREATE SYMMETRIC KEY CreditCards_Key11  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE Sales09;  
    GO  
    
    -- Create a column in which to store the encrypted data.  
    ALTER TABLE Sales.CreditCard   
        ADD CardNumber_Encrypted varbinary(128);   
    GO  
    
    -- Open the symmetric key with which to encrypt the data.  
    OPEN SYMMETRIC KEY CreditCards_Key11  
       DECRYPTION BY CERTIFICATE Sales09;  
    
    -- Encrypt the value in column CardNumber using the  
    -- symmetric key CreditCards_Key11.  
    -- Save the result in column CardNumber_Encrypted.    
    UPDATE Sales.CreditCard  
    SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
        , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary  
        , CreditCardID)));  
    GO  
    
    -- Verify the encryption.  
    -- First, open the symmetric key with which to decrypt the data.  
    
    OPEN SYMMETRIC KEY CreditCards_Key11  
       DECRYPTION BY CERTIFICATE Sales09;  
    GO  
    
    -- Now list the original card number, the encrypted card number,  
    -- and the decrypted ciphertext. If the decryption worked,  
    -- the original number will match the decrypted number.  
    
    SELECT CardNumber, CardNumber_Encrypted   
        AS 'Encrypted card number', CONVERT(nvarchar,  
        DecryptByKey(CardNumber_Encrypted, 1 ,   
        HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))  
        AS 'Decrypted card number' FROM Sales.CreditCard;  
    GO  
    

単純な対称暗号化を使用してデータ列を暗号化するにはTo encrypt a column of data using a simple symmetric encryption

  1. オブジェクト エクスプローラーで、 データベース エンジンDatabase Engineのインスタンスに接続します。In Object Explorer, connect to an instance of データベース エンジンDatabase Engine.

  2. [標準] ツール バーの [新しいクエリ] をクリックします。On the Standard bar, click New Query.

  3. 次の例をコピーしてクエリ ウィンドウに貼り付け、 [実行] をクリックします。Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    GO  
    
    CREATE CERTIFICATE HumanResources037  
       WITH SUBJECT = 'Employee Social Security Numbers';  
    GO  
    
    CREATE SYMMETRIC KEY SSN_Key_01  
        WITH ALGORITHM = AES_256  
        ENCRYPTION BY CERTIFICATE HumanResources037;  
    GO  
    
    USE [AdventureWorks2012];  
    GO  
    
    -- Create a column in which to store the encrypted data.  
    ALTER TABLE HumanResources.Employee  
        ADD EncryptedNationalIDNumber varbinary(128);   
    GO  
    
    -- 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  
    

詳細については、以下を参照してください。For more information, see the following: