MCDBA Brasil


  • Home
  • Sobre
  • Contato

Livros





Links Rápidos

SQL Server Builds (All Versions/Editions)


Download SQL Server 2017 (trial)


SQL Server 2017 Feature Pack


SQL Server 2016 Feature Pack


Cumulative Update SQL Server 2017 builds


Cumulative Update SQL Server 2016 builds


Cumulative Update SQL Server 2014 builds


Cumulative Update SQL Server 2012 builds


SQL Server 2005/2008 Samples Databases


Documentando o Servidor SQL Server


Analisando a Performance do Servidor-CheckList


Virtual PASS PT


Faça parte do maior virtual chapter do PASS com conteúdos técnicos em Português sobre SQL Server.

Todos os meses um evento Online para você! Acompanhe aqui os WebCasts já agendados

Sindicalize seu blog ou site ao VirtualPASSPT

SQL Server Blogs

SQL Server Query Processing Team


SQL Programmability & API Development Team


SQL Server Manageability Team


Latin America Support Team


Database + Disk + Performance


Microsoft SQL Server Support


SQL CLR Team


SQL Query Optimization Team


SQL 2005 Code Samples


SQL Server Express Team


SQL SMO Samples


SQL Storage Engine Team


SQL CAT Team


SQL Protocols Team


PSS SQL Server Engineers


Slava Oks on SQLOS


Ken Henderson’s blog


LUTI@Microsoft Blog


kimberly L. Trip’s blog


Fernando Garcia Blog

Artigos

Protegendo o seu banco de dados no SQL 2008 como TDE

por MAF SILVA maio 16, 2014 Nenhum comentário

O Que é o TDE?

O TDE (Transparent Data Encrypt) é uma nova funcionalidade adicionada ao SQL Server 2008, e tem como objetivo proteger os arquivos de dados e backups que nas versões anteriores eram usados software de terceiros para conseguir este tipo de segurança.

Para entendermos melhor, vamos imaginar uma base de RH, que contém dados sigilosos. E se alguém copiar o arquivo de backup da base? E se alguém copiar os MDF e LDFs? Bom quem tem a posse deste arquivo conseguia restaurar a base até a versão 2005 do SQL, no SQL 2008 o TDE protege os arquivos fisicamente criando certificados e criptografando os arquivos.

TDE é uma proteção no nível do database que protege os arquivos de dados e de log. Esta criptografia é transparente para qualquer aplicação e ocorre em real-time, o TDE usa o Database Encription Key (DEK), o DEK é uma symmetrickey que usa um certificado que fica armazenado na Master, a symmetric key também pode ser protegida por uma EKM (Extensible Key Management) que é um cryptographic provider em uma fonte externa como um Hardware Security Module (HSM), esta chave é usada durante o processo de Criptografia e Descriptografia.

Por ser transparente, não é necessário modificar as aplicações e podemos usar os algoritmos de criptografia AES (128, 196 e 256 Bit) e 3DES.

Como os dados são criptografados?

Quando ativamos o TDE, o SQL Server inicia um processo em background para criptografar todas as páginas do banco de dados. Este processo cria um shared lock no banco, e ao executar um comando DDL o SQL faz um Update Lock na database, o processo de Encryption será feito assíncrono usando um shared lock.

Os únicos comandos que não são possíveis executar enquanto o processo em background esta sendo executado é modificar as estruturas de arquivos do banco, alterar a base para off-line e fazer o dettach da base.

Esta criptografia do arquivo de banco de dados é executada na página, as páginas são criptografadas antes de serem gravadas no disco e descriptografadas quando lidas na memória, para o MDF o SQL inicia um processo (SCAN) que vai ler as páginas do disco e gravá-las novamente criptografadas, este processo parece ser custoso, mas em meus testes teve um aumento de cerca de 5% do baseline da máquina. Para o LDF o processo é um pouco diferente, o LOG é um arquivo sequencial, e o SQL não fica manipulando os dados como é feito MDF, o LOG é um arquivo físico, mas que internamente contém algumas partições lógicas o que chamamos de Virtual Log File (VLF) que funcionam de modo cíclico, o SQL preenche um arquivo passa para o próximo e vai liberando os VLF conforme o processo de checkpoint vai ocorrendo, para não ter parte de um VLF descriptografada e a outra parte criptografada, o SQL Server força o rollover (next VLF) isto garante que todos os registro do VLF atual estará criptografado.

Os dados são criptografados seguindo a seguinte estrutura hierárquica:

Como pode ser observado, a arquitetura do TDE possui 3 níveis: o Nível do sistema, Nível da Instância de SQL e o Nível do banco de dados.

Nível de sistema: o SQL usa uma API do Windows chamada DPAPI (Data Protection API) para proteger o Service Master Key (SMK)

Nível da Instância do SQL: o SQL usa o DPAPI para criptografar o Service Master Key que é criado no momento do Setup da máquina

Nível do Banco de dados: Neste ponto começa as intervenções manuais para ativar o TDE, neste ponto deve ser criado a Master Key sendo informando uma senha.
A Master Key vai proteger um Certificado que por sua vez protegerá a base de dados em que desejamos ativar o TDE.

Ativando o TDE

NOTA
Antes de executar os comandos abaixo no ambiente de produção, teste primeiro um de desenvolvimento ou homologação!

O Primeiro passo é criarmos a master Key que deve ser protegida por uma senha, para isto execute o comando abaixo:

use master
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@ssw0rd’;

Neste ponto recomendo um backup da master key

BACKUP MASTER KEY TO FILE = ‘C:BKPmasterkey’
    ENCRYPTION BY PASSWORD =
‘P@ssw0rd’

A próxima etapa é criarmos um certificado

use master
go
CREATE CERTIFICATE CertificadoTeste WITH SUBJECT = ‘Certificado Teste’, EXPIRY_DATE = ’12/31/2100′;–EXPIRY_DATE = ‘MM/DD/YYYY’

Observe que estou informando uma data para expiração que deve estar no formando MM/DD/YYYY, se eu não informar uma data para expiração o SQL atribui um ano a partir da data da criação do certificado.

Agora devemos fazer um backup deste certificado, pois se perdermos o servidor ou em um restore da base criptografada com o TDE, sem o certificado não será possível.

BACKUP CERTIFICATE CertificadoTeste TO FILE = ‘c:BKPCertificadoTeste’
    WITH PRIVATE KEY ( FILE = ‘C:PrivKeyCertificadoTeste’ , 
    ENCRYPTION BY PASSWORD = ‘P@ssw0rd’ )
GO

A próxima etapa é criarmos uma chave de criptografia na base que será criptografada com um algoritmo (AES ou 3DES) informando qual o certificado que usaremos para criptografar a base de dados.

USE dbTeste;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE CertificadoTeste;

Agra bastar ativar a feature de criptografia na base

ALTER DATABASE dbTeste SET ENCRYPTION ON;

Podemos acompanhar o status desta criptografia do banco com o comando abaixo:

SELECT db_name(database_id), encryption_state, percent_complete
FROM sys.dm_database_encryption_keys

Após a conclusão da criptografia do banco, o SQL passará a criptografar o novos dados no momento da gravação no disco, esta criptografia é feita por página.

O TDE é uma criptografia de arquivos de dados e log (LDF e MDF), sendo assim os dados em memória e em transito não são criptografados pelo TDE, pois já foram descriptografados quando as paginas foram lidas e colocadas em memória, ou seja, você não esta criptografando uma coluna, uma tabela… e sim o arquivo MDF e LDF e consequentemente os seus arquivos de backup.

NOTA
O TDE não vai aumentar ou reduzir o tamanho do seu banco.

Restaurando um Backup criptografado

Se você tentar restaurar um arquivo criptografado com o TDE em uma base sem o certificado que criptografou a base você receberá o erro abaixo:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ’0xC68044FDF5DD2C38B38282031C461ABB518CBE20′.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Para restaurar uma base que foi criptografada com o TDE outro ambiente precisaremos restaurar o backup do certificado no servidor em questão.
O primeiro passo é criar a máster key no servidor destino

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD =
‘P@ssw0rd’

O passo seguinte é restaurar o certificado

CREATE CERTIFICATE CertificadoTeste 
FROM FILE = ‘C: CertificadoTeste’
WITH PRIVATE KEY (FILE = ‘C: PrivKeyCertificadoTeste’,
DECRYPTION BY PASSWORD =
‘P@ssw0rd’)

Feito isto será possível restaurar o backup.

Criptografia do Windows

O Windows fornece dois tipos de criptografia, o EFS (Encryption File System) e o BitLocker, o EFS (Windows 2000 e superiores) faz a criptografia por arquivos o BitLocker, nova feature do Windows 2008, efetua a criptografia no volume.

O EFS é criptografado pela FEK (File Encryption Key) que por sua vez é protegida por um certificado de um usuário, no TDE o certificado protege um objeto do servidor, no EFS podemos atribuir vários certificados a um usuário, no TDE um objeto só pode ser criptografado por um certificado e este certificado pode criptografar outras bases, mas uma base só pode ser protegida por um certificado.

O BitLocker usa um chip TPM (Trusted Module Plataform) existente na motherboard para proteger o disco, assim não é possível inicializar o disco em outro servidor, somente o servidor onde foi ativado o BitLocker terá acesso às informações do disco.

BitLocker e EFS x TDE

• Bitlocker e TDE fazem proteção offline;
• EFS e Bitlocker possuem mecanismo para recuperação dos dados em caso de perda do certificado, o TDE ainda não possui este mecanismo (deve ser feito backup!);
• O Bitlocker possui fácil administração, transparência e a abstração da chave, mas a proteção do BitLocker estende-se apenas ao volume, fazer o backup ou mover os arquivos para uma pasta fora do volume deixará os arquivos sem proteção;
• No Bitlocker qualquer usuário com acesso ao volume terá acesso ao arquivo;
• O EFS não foi projetado para ambiente com muitos I/Os.
• O TDE mantém a criptografia mesmo movendo o arquivo para outras unidades;

Automatizador de TDE

Desenvolvi um aplicativo que auxilia na criação da masterKey, certificado e no restore, a grande sacada esta em proteger a senha, que hoje é explicita no SSMS ao executar o comando.

Esta ferramenta é Opensource, portanto quem quiser dar continuidade e aumentaqr features sinta-se a vontade (desde que a mesma continue opensource).

OBS: Não sou um programador, tentei ao máximo dentro de minhas limitações como programador aplicar as boas práticas, mas pode ser que tenha esquecido de alguns detalhes.

Você poderá fazer o download da solution para Visual Stúdio 2010 no link abaixo.
https://skydrive.live.com/?cid=90d5282d41444578&sc=documents&uc=1&id=90D5282D41444578%21263#

Pontos chave

• Os dados são criptografados durante a gravação no disco;
• Os dados são descriptografados durante a leitura do disco;
• A criptografia / descriptografia ocorre por página;
• O FILESTREAM não é criptografado;
• O TDE não aumenta ou reduz o tamanho da base;
• O TDE pode afetar a performance do autogrowth se você usa o Instant File Initialization;
• Habilitar o TDE em qualquer base fará com que o TempDB também seja criptografado, a performance pode ser afetada *;
• Totalmente transparente para as aplicações;
• Backups e Snapshots de uma base criptografada com o TDE também serão criptografados;
• O Backup Compression de uma base criptografada pode não ser muito eficiente;
• O TDE está disponível apenas nas edicões Enterprise e Developer do SQL 2008
• Um certificado pode criptografar múltiplos databases, mas um database só pode ser criptografado por um certificado;
• Não é possível habilitar o TDE nas bases de sistema;
• Não é possível habilitar o TDE em bases com filegroup readonly;
• O restore de uma base só será possível com o certificado!!!!

Links adicionais

http://www.mcdbabrasil.com.br

http://msdn.microsoft.com/en-us/library/bb934049.aspx

http://technet.microsoft.com/en-us/library/cc645957.aspx

Att.
Marcelo Fernandes

@marcelodba
http://marcelodba.wordpress.com

Avaliação:
Compartilhe:
  • Anterior Como ficam minhas certificações com a chegada do SQL Server 2014?7 anos atrás
  • Próximo Fundamentos: Conhecendo a Arquitetura do SQL Server7 anos atrás

Deixe uma resposta Cancelar resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

MVP Reconnect Award

Categorias

  • Artigos (359)
  • Dica da Semana (95)
  • Documentação (54)
  • Downloads (113)
  • MSDE 2000 (3)
  • Sem categoria (1)
  • Tutoriais (9)

Posts recentes

  • #FechouBrasil #PartiuPortugal
  • Brigando com o erro “The cached MSI file is missing”
  • MCDBABRASIL está de volta
  • Documentando o Servidor SQL Server
  • Brigando com os Erros 17182, 17826 e 17120

SQL Server AlwaysOn Video Series

Video1: Introdução ao SQLServer2012 AlwaysOn


Video2: Introdução ao SQLServer2012 AlwaysOn Availability Group


Video3: Introdução ao SQLServer2012 AlwaysOn AVG-Demo


Video4: Introdução ao SQLServer2012 AlwaysOn Listener


Video5: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries


Video6: Introdução ao SQLServer2012 AlwaysOn Readable Secondaries-Demo


Video7: Introdução ao SQLServer2012 AlwaysOn Failover Clustering


Serie SQL Server Failover Clustering End-to-End

Parte 1: Configuração da Rede e Ambiente


Parte 2: Configurando o Windows 2008 R2 Domain Controler e DNS


Parte 3: Preparando os nós para o Failover Cluster


Parte 4: Configurando um Failover Cluster de 2 nós


Parte 5: Configurando as LUNs no iSCSI Software Target (Parte 1)


Parte 6: Configurando as LUNs no iSCSI Software Target (Parte 2)


Parte 7: Apresentando as LUNs para os nós do Failover Cluster


Parte 8: Configurando os discos no Failover Cluster


Parte 9: Instalando a primeira instância virtual do SQL Server 2008


Parte 10: Instalando a segunda instância virtual do SQL Server 2008


Parte 11: Instalando e Configurando o MSDTC no Failover Cluster


Parte 12: Configurando Mount Points no Cluster e SQL Server 2008


Vídeo Extra: Removendo uma Instância do SQL Server 2008 R2 em Cluster


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering Overview


Alta Disponibilidade no SQL Server 2008 R2: Failover Clustering na Prática

Menu

  • Home
  • Sobre
  • Contato

Mais

  • RSS Feeds
2021 MCDBA Brasil.