Criptografar uma coluna de dadosEncrypt a Column of Data

Aplica-se a:Applies to: simSQL ServerSQL Server (todas as versões compatíveis) yesSQL ServerSQL Server (all supported versions) SimBanco de Dados SQL do AzureAzure SQL DatabaseYesBanco de Dados SQL do AzureAzure SQL Database SimInstância Gerenciada do Azure SQLAzure SQL Managed InstanceYesInstância Gerenciada do Azure SQLAzure SQL Managed Instance simAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse AnalyticsAplica-se a:Applies to: simSQL ServerSQL Server (todas as versões compatíveis) yesSQL ServerSQL Server (all supported versions) SimBanco de Dados SQL do AzureAzure SQL DatabaseYesBanco de Dados SQL do AzureAzure SQL Database SimInstância Gerenciada do Azure SQLAzure SQL Managed InstanceYesInstância Gerenciada do Azure SQLAzure SQL Managed Instance simAzure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

Este artigo descreve como criptografar uma coluna de dados usando uma criptografia simétrica no SQL ServerSQL Server usando Transact-SQLTransact-SQL.This article describes how to encrypt a column of data by using symmetric encryption in SQL ServerSQL Server using Transact-SQLTransact-SQL. Às vezes, isso é conhecido como criptografia no nível de coluna, ou criptografia no nível da célula.This is sometimes known as column-level encryption, or cell-level encryption. Este recurso está na versão prévia do Azure Synapse AnalyticsThis feature is in preview for Azure Synapse Analytics

Os exemplos neste artigo foram validados em relação ao AdventureWorks2017.The examples in this article have been validated against AdventureWorks2017. Para obter bancos de dados de exemplo, confira Bancos de dados de exemplo AdventureWorks.To get sample databases, see AdventureWorks sample databases.

SegurançaSecurity

PermissõesPermissions

As permissões a seguir são necessárias para executar as etapas abaixo:The following permissions are necessary to perform the steps below:

  • Permissão CONTROL no banco de dados.CONTROL permission on the database.
  • Permissão CREATE CERTIFICATE no banco de dados.CREATE CERTIFICATE permission on the database. Somente logons do Windows, logons do SQL Server e funções de aplicativo podem possuir certificados.Only Windows logins, SQL Server logins, and application roles can own certificates. Grupos e funções não podem possuir certificados.Groups and roles cannot own certificates.
  • Permissão ALTER na tabela.ALTER permission on the table.
  • Alguma permissão na chave, e não deve ter a permissão VIEW DEFINITION negada.Some permission on the key and must not have been denied VIEW DEFINITION permission.

Criar chave mestra de banco de dadosCreate database master key

Para usar os exemplos a seguir, é necessário ter uma chave mestra de banco de dados.To use the following examples, you must have a database master key. Se seu banco de dados ainda não tiver uma chave mestra de banco de dados, crie uma.If your database does not already have a database master key, create one. Para criar uma, conecte-se ao banco de dados e execute o script a seguir.To create one, connect to your database and run the following script. Use uma senha complexa.Be sure to use a complex password.

Copie e cole o exemplo a seguir na janela de consulta que está conectada ao banco de dados de exemplo da AdventureWorks.Copy and paste the following example into the query window that is connected to the AdventureWorks sample database. Clique em Executar.Click Execute.

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

Sempre faça backup da sua chave mestra de banco de dados.Always back up your database master key. Para obter mais informações sobre chaves mestras de banco de dados, consulte CREATE MASTER KEY (Transact-SQL).For more information on database master keys, see CREATE MASTER KEY (Transact-SQL).

Exemplo: Criptografar com criptografia simétrica e autenticadorExample: Encrypt with symmetric encryption and authenticator

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta que está conectada ao banco de dados de exemplo da AdventureWorks.Copy and paste the following example into the query window that is connected to the AdventureWorks sample database. Clique em Executar.Click Execute.

    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

Criptografar com uma criptografia simétrica simplesEncrypt with simple symmetric encryption

  1. No Pesquisador de Objetos, conecte-se a uma instância do Mecanismo de Banco de DadosDatabase Engine.In Object Explorer, connect to an instance of Mecanismo de Banco de DadosDatabase Engine.

  2. Na barra Padrão, clique em Nova Consulta.On the Standard bar, click New Query.

  3. Copie e cole o exemplo a seguir na janela de consulta que está conectada ao banco de dados de exemplo da AdventureWorks.Copy and paste the following example into the query window that is connected to the AdventureWorks sample database. Clique em Executar.Click Execute.

     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

Para saber mais, consulte o seguinte:For more information, see the following: