データの列の暗号化

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric SQL Database

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics

この記事では、Transact-SQL を使用する SQL Server で、対称暗号化を使用してデータ列を暗号化する方法について説明します。 これは、列レベルの暗号化、またはセル レベルの暗号化とも呼ばれます。

この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

セキュリティ

アクセス許可

次の手順を実行するには、次の権限が必要です。

• データベースに対する CONTROL 権限。
• データベースに対する CREATE CERTIFICATE 権限。 証明書を所有できるのは、Windows ログイン、SQL Server ログイン、およびアプリケーション ロールだけです。 グループとロールは証明書を所有できません。
• テーブルに対する ALTER 権限。
• キーに対する権限。VIEW DEFINITION 権限が拒否されていないことが必要です。

データベース マスター キーを作成する

次の例を使うには、データベース マスター キーが必要です。 データベースにデータベース マスター キーがない場合は、作成してください。 作成するには、データベースに接続し、次のスクリプトを実行します。 必ず複雑なパスワードを使用してください。

次の例をコピーし、AdventureWorks のサンプル データベースに接続されているクエリ ウィンドウに貼り付けます。 [実行] を選択します。

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

データベース マスター キーは常にバックアップしてください。 データベース マスター キーの詳細については、「CREATE MASTER KEY (Transact-SQL)」をご覧ください。

例: 対称暗号化と認証子を使用した暗号化

1. オブジェクト エクスプローラーで、 データベース エンジンのインスタンスに接続します。

2. 標準バーで、 [新しいクエリ] を選択します。

3. 次の例をコピーし、AdventureWorks のサンプル データベースに接続されているクエリ ウィンドウに貼り付けます。 [実行] を選択します。

   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(160);   
   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('SHA2_256', 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('SHA2_256', CONVERT(varbinary, CreditCardID))))  
       AS 'Decrypted card number' FROM Sales.CreditCard;  
   GO  
   

単純な対称暗号化による暗号化

1. オブジェクト エクスプローラーで、 データベース エンジンのインスタンスに接続します。

2. 標準バーで、 [新しいクエリ] を選択します。

3. 次の例をコピーし、AdventureWorks のサンプル データベースに接続されているクエリ ウィンドウに貼り付けます。 [実行] を選択します。

    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 [AdventureWorks2022];  
   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  
   

次のステップ

• CREATE CERTIFICATE (Transact-SQL)
• CREATE SYMMETRIC KEY (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• OPEN SYMMETRIC KEY (Transact-SQL)