Categorias
Azure SQL Database How To Troubleshooting

Azure SQL Database – Transparent Data Encryption with bring your own key

[bing_translator]

Transparent Data Encryption (TDE) vem por padrão habilitado quando você cria um novo database no Azure SQL Database.  

TDE não é mais uma novidade porem eu tenho visto que é pouco explorada (minha percepção). Se predente usar TDE on-premise vale a pena a leitura no post anterior. 

Habilitar ou desabilitar TDE no Azure SQL Database é simplesmente um click para ON ou OFF e a Microsoft faz tudo para você! Simples né, porem algumas perguntas vinham em relação a esse gerenciamento, por exemplo, como é realizado o gerenciamento de chaves ou porque apenas certificado poderiam ser usados.   

Com isso em mente a Microsoft disponibilizou uma nova opção: Bring Your Own Key. Com certeza isso faz muita diferença uma vez que posso ter o controle maior sobre as minhas chaves e posso aplicar a política definida pela minha empresa. Sendo assim agora eu posso estar cumprindo as conformidades de órgãos regulamentadores com mais transparência.  

https://azure.microsoft.com/en-us/blog/transparent-data-encryption-with-customer-managed-keys-in-azure-sql-database-generally-available/

Bring Your Own Key 

A minha primeira tentativa foi utilizar o Azure Portal para criar todos os passos necessários desde a criação de um novo servidor, key vault e key. 

Key Vault 

Na blade para criar o key vault começaram meus problemas.  

Ao tentar adicionar um acesso para o meu “logical server” eu não pude encontra-lo. Mas a pergunta era, porque não?! 

Voltei a ler a documentação e tentei entender melhor como TDE suporta BYOK. 

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-byok-azure-sql?view=azuresqldb-current 

O servidor logico precisa gerar e assinar uma identidade no Azure Active Directory, mas o Azure Portal não me dá essa opção quando estou criando o servidor.  

Não tem problema, vamos para o PowerShell. 

Set-AzureRmSqlServer `
-ResourceGroupName TDE `
-ServerName tdebalabuch `
-AssignIdentity

Eu não precisei apagar e criar novamente o servidor. 

Depois de executar o comando voltei ao Azure Portal e agora o meu servidor estava disponível para adicionar acesso.

Adicionei as permissões necessárias: GET, WRAP e UNWRAP.

Key 

Agora que a key vault está criado falta apenas a key.

3 importantes atributos que a Key deve ter: 

  • No expiration date 
  • Not disabled 
  • Permissão de get, wrap key, unwrap key  

Habilitar TDE com BYOK 

Agora seria a hora de apertar o botão salvar e tudo funcionar perfeito. Seria, mas não foi! 

Recebi o erro:  

Failed to save Transparent Data Encryption settings for server: {serverName}. Error message: The provided Key Vault uri ‘https://keyvaulttde.vault.azure.net/keys/KeyForTDE/633a6b1fad6941cb96449599df0382c0’ is not valid. Please ensure the vault has the right Recovery Level other than ‘Purgeable’. 

Ok. Fiz algo de erro, vou refazer tudo e vai dar certo! #sqn 

Boa notícia, refiz algumas vezes e pude perceber o que mesmo sem atribuir uma identidade para o servidor quando clico em salvar a etapa de atribuir uma identidade e conceder as permissões o Azure Portal está fazendo 🙂 

Mas ainda falta algo que ele não faz. Então nesse momento apenas é possível utilizar PowerShell e CLI.  

Quebrando os passos 

Aqui eu comecei a quebrar os passos da criação de todos os recursos envolvidos. 

Lendo a documentação novamente me deparo com essa informação 

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-byok-azure-sql?view=azuresqldb-current 

Depois de ler mais documentação percebi que essa funcionalidade era o que estava me causando problema.  

https://docs.microsoft.com/en-us/azure/key-vault/key-vault-ovw-soft-delete 

https://docs.microsoft.com/en-us/azure/key-vault/key-vault-soft-delete-powershell 

Eu tive que executar um comando em PowerShell:

($resource = Get-AzureRmResource -ResourceId (Get-AzureRmKeyVault -VaultName "KeyVaultTDE").ResourceId).Properties | Add-Member -MemberType "NoteProperty" -Name "enableSoftDelete" -Value "true" 

Set-AzureRmResource -resourceid $resource.ResourceId -Properties $resource.Properties  

Get-AzureRmKeyVault -VaultName "KeyVaultTDE"

Voltei ao Azure Portal e pude finalizar o processo. 

Investigando um pouco mais, quando entrei na Key Vault me apareceu a seguinte mensagem! 

OK! Meu entendimento é que Soft Delete ainda está em “Preview” e minha pergunta é: Porque a Microsoft liberou um recurso (TDE com BYOK) que depende de outro recurso que ainda está em “Preview”? 

Enquanto o Azure Portal não tem todas as opções disponíveis o melhor jeito é utilizar o PowerShell.  

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-byok-azure-sql 

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-byok-azure-sql-configure

https://azure.microsoft.com/en-us/blog/transparent-data-encryption-with-customer-managed-keys-in-azure-sql-database-generally-available/ 

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption-azure-sql 

https://azure.microsoft.com/en-us/blog/preview-sql-transparent-data-encryption-tde-with-bring-your-own-key-support/ 

 

Categorias
SQL Server Troubleshooting Virtual PASS BR

Transparent Data Encryption and its tricks

[bing_translator]

Transparent Data Encryption – TDE não é mais uma novidade, mas sim uma realidade que devemos usar. Um ponto importante que vale ressaltar é que esse recurso está presente apenas na edição ENTERPRISE do SQL Server.
Existem alguns passos a passos na internet de como implementar, mas eu vou deixar um script que pode ser seguido.

Master Key e Certificados

Uma forma simples de implementar o TDE é utilizar um certificado gerado pelo próprio SQL Server que é protegido pela Master Service Key.
Se você está implementando ou pretende implementar lembre-se que o mais importante de tudo é fazer backup do certificado e das keys!

PARE TUDO e faça BACKUP 

O principal aspecto para utilizar o TDE está relacionado à protecao de chaves incluindo backups separados das chaves de criptografia e a capacitade de restaurar e recuperar esse banco de dados em outro local. Assim como qualquer outro plano de DR, a restauração de bancos de dados de TDE é algo que devemos praticar antes que ocorra o desastre. Você não quer descobrir depois de um evento  que você não estava fazendo o backup da chave TDE corretamente.

Mais uma vez eu utilizei o dbatools para me ajudar a automatizar minha tarefa e quero agradecer ao Claudio Silva  (blog/twitter) por me ajudar. Algumas linhas de powershell são suficientes para isso 🙂

Agora que o background está pronto, o TDE pode ser habilitado.

A diferença quando se está utilizando AlwaysON Availability Group está na preparação dos ambientes antes de habilitar o TDE. Ou seja, você deve criar chaves e certificados em todas réplicas que pertencem ao AG.

/*
  Following steps must be executed on PRIMARY REPLICA
*/
-- Check if MASTER KEY is created
USE master; 
GO

SELECT * FROM sys.symmetric_keys; 
SELECT * FROM sys.certificates;

/*
  Step 1: On Primary Replica - Creation of the Database MASTER KEY (DMK)
  We can skype this step because we already have it
  Make sure that MASTER KEY Backup is working correctly.
        Review backup routine! This is most important step to do 
*/
-- Create a Master Key IF NOT EXISTS
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use_a_strong_password';
GO

-- Backup the Master Key
BACKUP MASTER KEY
TO FILE = '\\PrimaryReplica\TDE\PrimaryReplica_MasterKeyBackup.key'
ENCRYPTION BY PASSWORD = 'use_a_strong_password';
GO
/*
  Step 2: On Primary Replica - Creation of the CERTIFICATE
  Certificate is named as TDECert and it will use MASTER KEY to en
*/

-- Create Certificate Protected by Master Key
CREATE CERTIFICATE TDECert
WITH
  SUBJECT = 'Transparent Data Encryption Cerficate';
GO

/*
 Backup the Certificate
 Review backup routine. We should back up it to two/three different location
 Pick up a strong password 
*/
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,ENCRYPTION BY PASSWORD = 'use_a_strong_password' );

/*
  Step 3: On Primary Replica - Creation of Database Encryption Key (DEK)
*/

USE YourDatabase;
GO
/*
 Create a Database Encryption Key
 Choose between AES_128 and AES_256
 Your are not going to encrytp it yet! 
*/

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 -- AES_256 
ENCRYPTION BY SERVER CERTIFICATE Cert_For_TDE;
/*
  Following stes must be executed on SECONDARY REPLICA
*/

-- Check if MASTER KEY is created
USE master;
GO
SELECT * FROM sys.symmetric_keys;
SELECT * FROM sys.certificates;

-- Create a Master Key IF NOT EXISTS
USE master;
GO
/*	
  Step 4: On Secondary Replica - Creation of the DMK
  Make sure that MASTER KEY Backup is working correctly.
  Review backup routine.  
*/

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'use_a_strong_password';
-- Backup the Master Key on secondary in order we have this when it turns into Primary and we need not worry when recovery event occurs.
-- Review backup routine! This is most important step to do

BACKUP MASTER KEY
TO FILE = '\\SecondaryReplica\TDE\SecondaryReplica_MasterKeyBackup.key' -- You can use same folder to store keys (I just change it to make things easy)
ENCRYPTION BY PASSWORD = 'use_a_strong_password';
/*
  Step 5: On Secondary Replica - Creation of the Certificate from the Primary Certificate Backup
*/

-- Create Certificate Protected by Master Key 
CREATE CERTIFICATE Cert_For_TDE
FROM FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_Backup.cer'
WITH PRIVATE KEY ( FILE = '\\PrimaryReplica\TDE\PrimaryReplica_Cert_For_TDE_PrivKey.pvk'
                  ,DECRYPTION BY PASSWORD = 'same_strong_password_for_backup' );

-- Backup the Certificate
-- Review backup routine! This is most important step to do
BACKUP CERTIFICATE Cert_For_TDE TO FILE = '\\SecondaryReplica\TDE\SecondaryReplica_Cert_For_TDE_Backup.cer' 
WITH PRIVATE KEY ( FILE = '\\SecondaryReplica\TDE\SecondaryReplica_Cert_For_TDE_PrivKey.pvk' 
                  ,ENCRYPTION BY PASSWORD = 'use_a_strong_password' );
/*
  Step 6: On Primary Replica – Enabling TDE Encryption
  Following stes must be executed on PRIMARY REPLICA
*/

-- Now you are going to encrypt it
ALTER DATABASE YourDatabase SET ENCRYPTION ON;
GO
/*
  Step 7: On the Primary Replica and Secondary Replicas – Monitoring Encryption
  This code must be run in SQLCMD mode
*/

:CONNECT PrimaryReplica
SELECT
  @@SERVERNAME
 ,db.name
 ,db.is_encrypted
 ,dm.encryption_state
 ,dm.percent_complete
 ,dm.key_algorithm
 ,dm.key_length
FROM master.sys.databases                                db
  LEFT OUTER JOIN master.sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
GO

:CONNECT SecondaryReplica
SELECT
  @@SERVERNAME
 ,db.name
 ,db.is_encrypted
 ,dm.encryption_state
 ,dm.percent_complete
 ,dm.key_algorithm
 ,dm.key_length
FROM master.sys.databases                                db
  LEFT OUTER JOIN master.sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
GO

Uma vez que você efetive o recurso ele será executado silenciosamente em segundo plano, criptografando o banco de dados enquanto as atividades continuam. Enquanto a criptografia inicial está em execução, podemos verificar a coluna percent_complete na sys.dm_database_encryption_keys para ver progresso (veja o step 7).

http://www.sqlservercentral.com/articles/always+on/135432/

Esse processo seria simples, se não fosse pelo grande volume de transações que eu tenho. Um sistema de telemetria que a todo instante está recebendo e enviando informações de veículos no mundo.

O CPU foi a quase 100% e gerou um performance ruim para o sistema. O que quero dizer é que processos que executavam em 500 milissegundos passaram a executar em 1200/1500 milissegundos.

Se você procurar na documentação só exista a opção de ON e OFF para ENCRYPTION. Uma vez que eu iniciei o processo para criptografar o database eu tenho que esperar terminar. E agora? Quanto tempo isso vai demorar para finalizar no meu database?

Existe uma trace flag documentada (Thanks God Microsft) que pausa o processo e simplesmente não faz rollback! Fantástico pois não perco o que foi criptografado e meu database fica com o status de “encryption in progress”. Ou seja, as páginas que foram criptografadas continuam assim e o as páginas que não foram criptografadas serão lidas normalmente.

https://blogs.msdn.microsoft.com/markweberblog/2017/04/04/transparent-data-encryption-tde-traceflag-5004-and-interrupting-encryption-scanning/

Como qualquer outra TF basta você ativar ou desativar. Para parar o processo ative a TF:

DBCC TRACEON(5004) 
GO

Para reiniciar o processo de onde parou basta desativar a TF e executar o comando novamente para criptografar o database.

DBCC TRACEOFF(5004) 
GO 

ALTER DATABASE YourDatabase SET ENCRYPTION ON;
GO

Isso salvou minha vida, pois eu pude habilitar o processo em horários de menor utilização e mesmo assim se impactasse o sistema eu seria capaz de parar.

Backup Compression

Um fator que notamos depois de habilitar o TDE foi a compressão de backups não estava funcionando corretamente, ou seja, simplesmente ignorava a opção e gerava um backup gigantesco.

Pesquisando um pouco achei uma excelente explicação para esse comportamento.

https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/

Depois de entender melhor esse comportamento, foi a hora de verificar o que estava ocorrendo. Para isso utilizei um extended event (backup_restore_progress_trace) que facilita e muito a interpretar o que está acontencendo no momento do backup.

O Edvaldo Castro (blog/twitter) explica muito bem esse novo XE.

https://edvaldocastro.com/benchmarking-teste-do-novo-extended-event/

Você deve querer ver uma mensagem de MaxTransferSize maior de 64 KB:

https://blogs.msdn.microsoft.com/sql_server_team/new-extended-event-to-track-backup-and-restore-progress/#.VgtLzHpVhBd

Nesse momento fizemos duas ações:

1 – Atualizamos para o service pack e cumulative update mais recente visto que estávamos com a versão inferior descrita no link (https://blogs.msdn.microsoft.com/sql_server_team/backup-compression-for-tde-enabled-databases-important-fixes-in-sql-2016-sp1-cu4-and-sql-2016-rtm-cu7/)

2 – Mudamos nossa rotina de backup para se adequar as necessidades.

E o tempdb?

A partir do primeiro database utilizando TDE o tempdb passa ser criptografado automaticamente. Um curioso caso sobre o tempdb foi desvendado pelo Bob Ward, recomendo a leitura!

https://blogs.msdn.microsoft.com/bobsql/2017/01/26/sql-server-mysteries-the-case-of-tde-and-permanent-tempdb-encryption/

Eu tenho FileStream, vai criptografar?

A documentação é clara, dados em FileStream não são criptografados. Nossa solução foi utilizar BitLocker

https://docs.microsoft.com/en-us/windows/security/information-protection/bitlocker/bitlocker-how-to-deploy-on-windows-server

Como ficou a CPU?

Nossos testes mostraram um leve aumento de CPU mas nada que comprometeu  a performance. Monitoramos alguns processos críticos e vimos que o aumento foi em média de 3-5% de CPU e tempo total de duração aumentou 100-150 milissegundos.

Transaction-Log? Hummmm

Eu não vi nenhum tipo de problema com isso. Espero que continue assim 🙂

Alguns recursos que não utilizamos e ainda não testei como Buffer Pool Extention – a documentação diz que não é criptogrado e assim como FileStream uma opção seria usar BitLocker. Quero verificar o quanto isso pode afetar a área de memória ou não.

Certificado expirado

O processo de substituir com segurança o certificado é chamado rotating the encryption key. É importante fazer isso, e o SQL Server torna isso um processo simples e rápido. A substituição dos seus certificados expirados é um processo rápido e simples, e é uma parte importante da manutenção da segurança da sua criptografia

Você também pode usar ou restaurar um certificado expirado no servidor.

https://blogs.msdn.microsoft.com/sqlsecurity/2010/06/14/database-encryption-key-dek-management/

Rotação de chaves

Rotation the encryption key é o processo de passar do certificado antigo para o novo. Nesse caso, tudo o que acontece é que a chave de criptografia do banco de dados é descriptografado com o certificado antigo e criptografado novamente com o novo certificado. Esse novo valor criptografado é armazenado no banco de dados sobrescrevendo o antigo. Por ser um processo rápido, a rotação frequente não é um problema.

Novo certificado

O primeiro passo é criar um novo certificado que será usado para criptografar o banco de dados. Você pode seguir os mesmos passos acima.

Rotação do certificado

Para substituir o certificado usado para TDE, adicione o novo certificado como acima, então execute o comando

USE YourDatabase
GO

ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDECert_Rotate_Key;
GO

Faça backup do novo certificado e do database.

Remover TDE

Para remover o TDE é necessário executar o comando abaixo.

ALTER DATABASE yourdatabase SET ENCRYPTION OFF;
GO

Aguarde até que o processo de descriptografia esteja concluído. Use o sys.dm_database_encryption_keys DMV para determinar seu status

USE YourDatabase
GO

DROP DATABASE ENCRYPTION KEY
GO

Faça backup do novo certificado e do database.

Para finalizar, aprenda que o TDE não é apenas um recurso que liga e desliga para entrar em conformidade com órgãos regulamentadores. Você deve se preparar e entender melhor esse recurso antes de utiliza-lo em seu ambiente de producao. Este foi um dos recursos que eu mais gostei de implementar pois me aprofundei em um assunto que eu mesmo tinha deixado de lado.

Fica uma pergunta! Quando falamos de data-in-rest o TDE é suficiente para deixar meus dados seguros ou precisamos fazer um backup com criptografia? Em um próximo post falarei como utilizar backup encryption

https://blogs.msdn.microsoft.com/sqlsecurity/2010/06/14/database-encryption-key-dek-management/

https://blogs.msdn.microsoft.com/sqlsecurity/2016/10/05/feature-spotlight-transparent-data-encryption-tde/

Categorias
How To SQL Server

Habilitando TDE

Hoje vou demostrar como utlizar o TDE, no qual descrevi no post anterior.

Vamos apenas recordar algumas coisas. No SQL Server 2008 temos um novo recurso para encriptar dados, é o Transparent Data Encryption (TDE).

O TDEencriptografa todo o seu banco de dados, isto é todos os seus arquivos de banco de dados, de forma simples e elegante. Ele fornece um forte mecanisco para proteger informações sensíveis e cumpri muitas exigencias de orgãos regulamentares. O melhor é que você não irá precisar tomar nenhuma ação no seu aplicativo.

Gostou? Então vamos habilitar esse recurso.

Primeiro vamos criar um banco de dados:

TDE01

Agora vamos criar nossa DATABASE MASTER KEY, primeiramente eu faço uma validação para ver se já existe essa DMK na base master.

TDE02

Com a DMK criada, vamos criar a o certificado de acesso, novamente eu faço uma validação que verifica se o certificado já existe.

TDE03

Agora vamos habilitar o nosso banco de dados ‘CRIPTOGRAFIA’ para receber esse criptografia

TDE04

Eu escolhi o algoritmo AES_256, mas o SQL Server disponibiliza outros.

Lembre sempre de fazer backup da sua MASTER KEY e do seu Certificado.

Voce pode fazer uma breve consulta para verificar quais os seus bancos que estão encriptados.

clip_image002

 

Simples, facil e    agora seguro!

Categorias
SQL Server

Brincando com Segurança na Informação – TDE

TDE

Segurança na informação é uma questão amplamente discutida. Você pode tomar várias precauções para proteger suas informações, tais como firewalls, certificados e ect. No entanto, quando uma midia (HD, DVD ou Fita de backup) é roubada, a pessoa mal intencionada, pode facilmente recuperar essa informações. Como um administrador de banco de dados(DBA), a segurança dos dados é uma das áreas mais importantes a considerar quando se trata de proteger os seus bancos de dados. Uma das soluções é criptografar os dados confidenciais no banco de dados e proteger as chaves que são usadas para criptografar os dados com um certificado. Isso impede que qualquer pessoa sem as chaves usadas para criptografar, de usar esses dados. Este tipo de proteção deve ser planejado com muita cautela e antecedencia.

SQL Server 2008 introduz um novo recurso que protege o banco de dados chamado Transparent Data Encryption – TDE, que oferece proteção para o banco de dados inteiro. TDE é criptografia de nível pleno de banco de dados que não está limitado às colunas e linhas, mas protege os arquivos de dados e arquivos de log assim como os arquivos de backup e a melhor parte é que NÃO exige quaisquer alterações às aplicações existentes. Dessa forma o pessoal de desenvolvimento não precisa nem ficar sabendo!

Depois que o TDE estiver habilitado em um banco de dados, para executar o restore de um backup para outra instância do SQL Server ou o attach para outra instância do SQL Server não será permitido até que o certificado que foi usado para proteger a chave de criptografia de dados seja restaurado na nova instância SQL Server.

O recurso de criptografia da TDE é aplicada no nível da página. As páginas de um banco de dados criptografado são criptografadas antes de serem escritas no disco e descodificada quando é lido na memória. TDE não aumenta o tamanho do banco de dados criptografado. Transparent Data Encryption utiliza uma Database Encryption Key (DEK) para criptografar o banco de dados que é armazenado no registro de inicialização do banco de dados. A DEK é uma chave simétrica protegida usando um certificado armazenado no banco de dados master do servidor ou uma chave assimétrica protegida por um módulo de Extensible Key Management (EKM).

Embora o certificado pode ser garantido por uma senha, TDE requer que o certificado é garantido pela Database Master Key que é protegida pela Master Key Service que é protegido pelo Data Protection API. A TDE fornece a capacidade para cumprir muitas leis, regulamentos e diretrizes estabelecidas em diversos setores.

Obs: É importante lembrar que o canal de comunicação entre o SQL Server e o aplicativo não serão criptografados via TDE.

 

Muito Importante!

Quando TDE for habilitado, você deve imediatamente fazer o backup da MASTER KEY e do seu CERTIFICADO.

A ilustração (retirada do site da MSDN) a seguir mostra a arquitetura de criptografia TDE:

clip_image001

Os arquivos de backup da base de dados em que o TDE foi habilitado também são criptografados usando a chave de criptografia do banco de dados. Quando você restaurar esses backups, o certificado que protege a chave de criptografia de banco de dados deve estar disponível, isto significa que, além de fazer o backup do banco de dados, você tem que ter certeza que você mantenha cópias de segurança dos certificados do servidor para evitar perda de dados. A perda de dados irá ocorrer se o certificado não está mais disponível. No TDE, todos os files e filegroups do banco de dados são criptografados. Se algum algum filegroups estiver marcado como READ ONLY, a operação de criptografia do banco de dados irá falhar.

Se o banco de dados que você pretende utilizar o TDE estiver habilitado para mirroring ou log shipping não se preocupe os dados serão criptografados quando forem enviados.

Um recurso a mais para você deixar seus dados seguros!

Até a próxima!

Categorias
SQL Server

Entendendo a Criptografia

No mundo corporativo de hoje, a segurança com os dados e informações é primordial.
O SQL Server se preocupa com isso e implementa alguns artificios em relação a criptografia e segurança
de dados.

SQL Server criptografa dados com uma criptografia hierárquica e infra-estrutura de gerenciamento de chaves. Cada camada criptografa a camada abaixo dela, usando uma combinação de certificados, chaves assimétricas, e as chaves simétricas. As chaves assimétricas e chaves simétricas podem ser armazenados fora do SQL Server em um módulo chamado Extensible Key Management (EKM).

A imagem baixo, retirada do site da TechNet, mostra a hierarquia de criptografia.

 

 

O acesso ao início da hierarquia é geralmente protegido por uma senha.

A figura mostra que tudo começa com a criptografia do Windows através da API Data Protection que proteje a Service Master Key (SMK) onde começa nossa aventura no mundo do SQL Server.

Service Master Key

Service Master Key forma a base para a hierarquia da criptografia no SQL Server e é necessária para que você possa criar categorias de certificados ou chaves assimétricas. A Master Key é gerada automaticamente na primeira vez que a instância é iniciada. Por padrão, Service Master Key é criptografada usando a API de proteção de dados do Windows e usa a chave da máquina local. Service Master Key só pode ser aberta pela conta de serviço do Windows em que ela foi criada, ou por uma entidade com acesso tanto ao nome da conta de serviço e sua senha. A Service Master Key é usada para criptografar qualquer database master key que é criado dentro da instância.

Regenerar ou recuperar a Service Master Key envolve descriptografar e re-criptografar a hierarquia de criptografia completa.

Database Master Key

Cada banco de dados tem uma Master Key diferentes, garantindo que um usuário com acesso a descodificar os dados em um database também não pode decifrar dados em outro database sem ser concedido permissão para ele executar essa tarefa.

A Database Master Key é usado para proteger todos os certificado, chave simétrica, ou chave assimétrica que são armazenadas detro de um database e é criptografada com o Triple DES e a senha fornecida pelo usuário. Uma cópia da Database Master Key também é criptografada usando a Service Master Key de tal forma que a descriptografia automática pode ser realizado dentro da instância.

Vamos demostrar a criação de uma Database Master Key, criei um database chamado CRIPTOGRAFIA”

USE [master]

GO

CREATE DATABASE [CRIPTOGRAFIA]

GO

A Database Master Key deve ser gerada explicitamente usando o seguinte comando:

USE [CRIPTOGRAFIA]

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘passwordDatabase’

GO

Para verifcar informações sobre chaves de criptografia, você pode consultar uma DMV (Dynamic Management View)

USE [master]

GO

SELECT * FROM sys.symmetric_keys

GO

 

Quando você faz uma solicitação para descriptografar dados, a Master Key é usada para descriptografar a Database Master Key, que é usada para descriptografar um certificado, chave simétrica, ou chave assimétrica, e por sua vez é usada para descriptografar os dados.

A razão dessa hierarquia é importante e que você deve ter cuidado quando for preciso mover backups contendo os dados criptografados entre instâncias do SQL Server. Para restaurar e ser capaz de decifrar os dados com sucesso, você também deve fazer backup da Database Master Key e depois gerar a Database Master Key em outra instância.

Certificados

Os certificados são chaves baseado no padrão X.509 que são utilizados para autenticar as credenciais da entidade. Você pode criar certificados públicos ou privados. Um certificado público é essencialmente um arquivo que é fornecido por uma autoridade de certificado que valida a entidade, através do certificado. Os certificados privados são gerados e utilizados para proteger os dados dentro de uma organização.

Para criar um certificado assinado no SQL Server, você deve usar o seguinte comando:

USE [CRIPTOGRAFIA]

GO

CREATE CERTIFICATE CertificadoBlog

ENCRYPTION BY PASSWORD = ‘passwordCertificado’

WITH SUBJECT = ‘Certificado Blog’,

EXPIRY_DATE = ’12/31/2010′;

GO

Assinaturas

Assinaturas permitem elevar a permissão de um usuário, mas para proporcionar isso o usuário deve estar executando um pedaço especificações de código ou seja, você so pode adicionar uma assinatura digital para um módulo de stored procedure, function, trigger e assemblies.

O processo para assinar digitalmente um código para gerenciar as permissões é o seguinte:

  • Criar uma Database Master Key.

    (repetindo o código acima)

USE [CRIPTOGRAFIA]

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘passwordDatabase’

GO

  • Criar um certificado no database

    (repetindo o código acima)

USE [CRIPTOGRAFIA]

GO

CREATE CERTIFICATE CertificadoBlog

ENCRYPTION BY PASSWORD = ‘passwordCertificado’

WITH SUBJECT = ‘Certificado Blog’,

EXPIRY_DATE = ’12/31/2010′;

GO

Criar um usuário mapeado para o certificado.

USE [CRIPTOGRAFIA]

GO

CREATE USER UserBlog FOR CERTIFICATE CertificadoBlog;

GO

  • Atribuir permissões em um objeto ou objetos para o usuário.

   Vou criar uma serie de objetos no database para demonstrar a assinatura.

USE [CRIPTOGRAFIA]

GO

CREATE TABLE dbo.Secretaria(

idSec int IDENTITY(1,1),

nomeSec nvarchar(300) NOT NULL,

responsavelSec nvarchar(300) NOT NULL,

CONSTRAINT Secretaria_PK PRIMARY KEY CLUSTERED (idSec) )

GO

USE [CRIPTOGRAFIA]

GO

CREATE PROCEDURE dbo.USP_LISTA_SECRETARIA AS

SELECT * FROM dbo.Secretaria

Depois de criar os objetos vou dar permissão ao usuario UserBlog que criamos para o certificado.

GRANT EXECUTE ON dbo.USP_LISTA_SECRETARIA TO UserBlog

  • Assinar digitalmente um módulo para o certificado.

      USE [CRIPTOGRAFIA]

GO

ADD SIGNATURE TO dbo.USP_LISTA_SECRETARIA BY CERTIFICATE CertificadoBlog WITH PASSWORD = ‘passwordCertificado’

Symmetric Keys

As chaves simétricas utilizam uma única chave de criptografia e descriptografia. Como apenas uma única chave é necessária para criptografar e descriptografar dados, a criptografia de chave simétrica não é tão forte como os certificado ou chave assimétrica baseada em criptografia. No entanto, as chaves simétricas proporcionar o melhor desempenho possível para o uso rotineiro de dados criptografados.

Legal, e agora? O que isso muda?

Vamos lá… vou mostrar o que isso muda e como faz diferença.

Vou aproveitar que já criamos uma Master Key e um Certificado e vou apenas criar uma Symmetric Keys

USE [CRIPTOGRAFIA]

GO

CREATE SYMMETRIC KEY EncriptaTabela

WITH ALGORITHM = TRIPLE_DES

ENCRYPTION BY CERTIFICATE CertificadoBlog

Vamos inserir alguns dados na tabela “Secretaria” que tambem já criamos utilizando a chave simetrica.

OPEN SYMMETRIC KEY EncriptaTabela

DECRYPTION BY CERTIFICATE CertificadoBlog WITH PASSWORD = ‘passwordCertificado’

insert into CRIPTOGRAFIA.dbo.secretaria

( nomeSec

,responsavelSec

)

values

( ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Urbanismo’)

,ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Fernado B.’)

)

insert into CRIPTOGRAFIA.dbo.secretaria

( nomeSec

,responsavelSec

)

values

( ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Meio Ambiente’)

,ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Ricardo G.’)

)

insert into CRIPTOGRAFIA.dbo.secretaria

( nomeSec

,responsavelSec

)

values

( ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Educacao’)

,ENCRYPTBYKEY(KEY_GUID(‘EncriptaTabela’),‘Aline A.’)

)


Dessa forma meus dados estão todos criptografados. Como mostra a figura abaixo.

 

Ok.. você pode perguntar.. ele não usou a função para descriptografar. Tudo bem, vamos lá.

Hum.. e agora? Como eu recupero meus dados? Será que eles estão guardados?

Calma, seus dados estão lá. Para recuperar nos precisamos usar a mesma função para inserir dados.

 

Um detalhe importante. Não esqueça de fechar a chave, isso garante a segurança na hora de leitura dos dados!

Backup Master key e Certificates

Porque devemos fazer backup da Master Key e de Certificados?

A resposta é simples! São eles que permitem a criptografia dos seus dados e apenas eles podem descriptografar os dados.

Não adianta nada voce criptografar os dados se qualquer um pudesse abri-los em qualquer lugar.

A sintaxe genérica para fazer backup de um DMK é:

USE [CRIPTOGRAFIA]

GO

BACKUP SERVICE MASTER KEY TO FILE = ‘D:Bancos de DadosMasterKey’ ENCRYPTION BY PASSWORD = ‘password’;

Database Master Key (DMKs) são criadas antes de um certificado, uma chave simétrica, ou chave assimétrica. A DMK é a raiz da hierarquia de criptografia em um banco de dados. Para garantir que você pode acessar certificados, chaves assimétricas, e as chaves simétricas dentro de um banco de dados, você precisa ter uma cópia de segurança da DMK.

Antes que você pode fazer backup de um DMK, ela deve ser aberta. Por padrão, um DMK é criptografada com a chave mestra de serviço. Você deve abrir a DMK usando o seguinte comando:

USE [CRIPTOGRAFIA]

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘passwordDatabase’;

Os certificados são usados para criptografar dados, bem como assinaturas digitais. Apesar de você poder criar um novo certifcado para substituir a assinatura digital, no caso da perda de um
certificado, você deve ter o certificado original para acessar todos os dados que foram criptografados com
ele. Você pode fazer backup somente da chave pública, usando o seguinte comando:

BACKUP CERTIFICATE CertificadoBlog TO FILE = ‘D:Bancos de DadosCertificado1’

CLOSE MASTER KEY

No entanto, se você restaurar um backup de um certificado, contendo apenas a chave pública, o SQL Server gera uma nova chave privada. Infelizmente, a chave privada é um componente importante de um certificado que é usado para criptografar e descriptografar dados no SQL Server. Portanto, é preciso assegurar que o backup contenha tanto chaves públicas e privadas para um certificado. Assim como Master Key, você deve fazer backup de um certificado imediatamente após a criação, utilizando o seguinte comando:

USE [CRIPTOGRAFIA]

GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘passwordDatabase’;

BACKUP CERTIFICATE CertificadoBlog TO FILE = ‘D:Bancos de DadosCertificado2’

WITH PRIVATE KEY ( DECRYPTION BY PASSWORD = ‘passwordCertificado’ ,

        FILE = ‘D:Bancos de DadosPrivateKey1’ ,

ENCRYPTION BY PASSWORD = ‘passwordBackup’ )

)

CLOSE MASTER KEY