Cifrar una columna de datosEncrypt a Column of Data

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

En este artículo se describe cómo cifrar una columna de datos utilizando el cifrado simétrico en SQL Server 2017SQL Server 2017 mediante Transact-SQLTransact-SQL.This article describes how to encrypt a column of data by using symmetric encryption in SQL Server 2017SQL Server 2017 using Transact-SQLTransact-SQL. A veces, esto se conoce como cifrado de nivel de columna, o cifrado de nivel de celda.This is sometimes known as column-level encryption, or cell-level encryption.

SeguridadSecurity

PermisosPermissions

Los siguientes permisos son necesarios para realizar los pasos siguientes:The following permissions are necessary to perform the steps below:

  • Permiso CONTROL en la base de datos.CONTROL permission on the database.

  • Permiso CREATE CERTIFICATE en la base de datos.CREATE CERTIFICATE permission on the database. Solo los inicios de sesión de Windows, los inicios de sesión de SQL Server y los roles de aplicación pueden poseer certificados.Only Windows logins, SQL Server logins, and application roles can own certificates. Los grupos y roles no pueden poseer los certificados.Groups and roles cannot own certificates.

  • Permiso ALTER en la tabla.ALTER permission on the table.

  • Algún permiso en la clave y no debe haberse denegado el permiso VIEW DEFINITION.Some permission on the key and must not have been denied VIEW DEFINITION permission.

Usar Transact-SQLUsing Transact-SQL

Para usar los ejemplos siguientes, debe tener una clave maestra de base de datos.To use the following examples, you must have a database master key. Si la base de datos aún no tiene una clave maestra de base de datos, ejecute la siguiente instrucción y proporcione la contraseña para crear una: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>';  

Realice siempre una copia de seguridad de la clave maestra de base de datos.Always back up your database master key. Para obtener más información sobre las claves maestras de base de datos, vea CREATE MASTER KEY (Transact-SQL).For more information on database master keys, see CREATE MASTER KEY (Transact-SQL).

Para cifrar una columna de datos usando el cifrado simétrico que incluye un autenticadorTo encrypt a column of data using symmetric encryption that includes an authenticator

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.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  
    

Para cifrar una columna de datos usando un cifrado simétrico simpleTo encrypt a column of data using a simple symmetric encryption

  1. En el Explorador de objetos, conéctese a una instancia del Motor de base de datosDatabase Engine.In Object Explorer, connect to an instance of Motor de base de datosDatabase Engine.

  2. En la barra de Estándar, haga clic en Nueva consulta.On the Standard bar, click New Query.

  3. Copie y pegue el siguiente ejemplo en la ventana de consulta y haga clic en Ejecutar.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  
    

    Para obtener más información, vea:For more information, see the following: