Criptografia Transparente de Dados - Final

Irei mostrar todos os testes que fiz ao desabilitar a TDE de um banco de dados.

Estou utilizando o  SQL Server 2008 Developer Edition com SP2, build 10.0.4000.

Vamos lá...

-- Desabilitando a criptografia do banco de dados   
ALTER DATABASE Criptografia
SET ENCRYPTION OFF
GO
Após executar este comando e desabilitar a TDE vamos executar a consulta:

SELECT DB_NAME(e.database_id) AS DatabaseName,
            e.encryption_state,
    CASE e.encryption_state
                WHEN 0 THEN 'No database encryption key present, no encryption'
                WHEN 1 THEN 'Unencrypted'
                WHEN 2 THEN 'Encryption in progress'
                WHEN 3 THEN 'Encrypted'
                WHEN 4 THEN 'Key change in progress'
                WHEN 5 THEN 'Decryption in progress'
    END AS encryption_state_desc,
   key_algorithm +'_'+ convert(nvarchar,key_length) [Algoritmo],
            c.name [Nome Certificado],
            set_date [Data Criptografia]
    FROM sys.dm_database_encryption_keys AS e
    LEFT JOIN master.sys.certificates AS c
    ON e.encryptor_thumbprint = c.thumbprint

Teremos o seguinte o resultado:

Vemos que a criptografia está desabilitada mas o banco de dados ainda está associado ao certificado, vamos então excluir então a chave de criptografia do banco:

USE Criptografia
GO
DROP DATABASE ENCRYPTION KEY
GO

Executando a consulta novamente veremos que somente o banco tempdb sairá no resultado:

Neste momento se reiniciarmos o serviço do SQL e executarmos a consulta não retornará nada.

Vamos agora desatachar o banco e tentar restaurar em outra instância usando o mesmo SQL Server 2008 Developer Edition com SP2, build 10.0.4000 só que sem nenhuma chave de criptografia ou certificado, teoricamente era para funcionar já que desabilitamos a TDE do banco, mas vamos ver o resultado.

--Desatachando o banco
USE master
GO   
sp_detach_db 'Criptografia'
GO

e agora vamos tentar atachar na outra instância:

EXEC sp_attach_db @dbname = 'Criptografia',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_Log.ldf'
GO

Teremos o seguinte retorno:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xAA16E63CE30B041330332FA87A4C37E82F8ABC33'.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Criptografia'. CREATE DATABASE is aborted.

Neste momento eu não acreditei que não seria possível tirar a criptografia do banco, então fiz um backup da banco e tentei restaurar, tive o seguinte retorno:

Processed 256 pages for database 'Criptografia', file 'Criptografia' on file 1.
Processed 1 pages for database 'Criptografia', file 'Criptografia_log' on file 1.
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xAA16E63CE30B041330332FA87A4C37E82F8ABC33'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Após alguns dias pesquisando vi que no CU10 do SQL Server 2008 SP1 esse bug (Microsoft assumiu) foi corrigido. Decidi então fazer o teste.
Instalei o SQL Server 2008 SP1 com CU10, build 2799

Fiz os mesmos testes utilizando o SQL Server 2008 SP1 com CU10, build 2799 e realmente conseguir atachar e/ou restaurar o banco após desabilitar a criptografia TDE. Segundo a Microsoft o erro ocorria porque o arquivo .ldf do banco ficava com referências do certificado.

Agora como sabemos o SQL Server 2008 SP2 só inclui o CU1 até CU8, então aconselho que quando for usar TDE utilize o SQL Server 2008 SP1 com CU10 ou então espere mais um tempo até sair o SP3 (se sair, hehehe), agora a outra é opção é NUNCA perder o certificado, assim você pode levar o banco para qualquer servidor utilizando o certificado.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Recuperando o banco SUSPECT criando um novo arquivo de LOG

Galera esse é um artigo relativamente simples e bem útil para o dia a dia do DBA, quem ainda não ficou de frente com um banco de dados corrompido?
Pois é irei mostrar uma maneira de recuperar um banco de dados em modo SUSPECT, criando um novo arquivo de LOG do banco. Claro que antes de realizar este procedimento tem que olhar o log do SQL Server e ver qual motivo o banco ficou suspect, se no log tiver a mensagem que não foi possível recuperar o arquivo de log e o banco foi marcado como suspeito então este scritp resolverá seu problema.

Este scritp é válido para o SQL Server 2000, pois o comando DBCC REBUILD_LOG foi descontinuado no SQL Server 2005 e 2008.

Segue script comentado:

--VOLTA BANCO SUSPECT CRIANDO OUTRO ARQUIVO DE LOG
/*
1° Passo -> Verificar porque o banco ficou suspect, se foi porque não conseguiu fazer recovery do arquivo de log
este script resolverá o problema
*/
--EXECUTAR TODOS OS SCRIPTS SEPARADAMENTE
--Executar este primeiro comando, com este configuração é possível alterar as tabelas de sistema
sp_configure 'allow updates',1
reconfigure with override
go
--Executar e verificar se o banco e as tabelas estão acessíveis
sp_resetstatus 'NomeBancoDeDados'
go
--Caso o comando acima não funcione, executa este comando que deixará o banco em emergency mode
Update sysdatabases set status = 32768   where name ='NomeBancoDeDados'
go
--Após o comando ser executado com sucesso, executar este comando para criar novo arquivo de log para o banco,
--passa o nome do banco e o caminho onde será criado o novo arquivo
DBCC Rebuild_log ('NomeBancoDeDados','C:\CAMINHO\NomeDoArquivo.ldf')
go
-- Aqui o banco já estará on line... Mas temos que verificar se existem erros de integridade
-- Vamos colocar o banco e modo single user
Use Neo
go
sp_dboption 'NomeBancoDeDados','single_user', true
go
--Executamos o comando para reparar os erros, caso existam
use master
go
dbcc checkdb ('NomeBancoDeDados',repair_allow_data_loss)
go
--Tirar o banco do modo single user
use Neo
go
sp_dboption 'NomeBancoDeDados','single_user', false
go
--Voltar este parametro para 0
sp_configure 'allow updates',0
reconfigure with override
go

Apenas explicando o parâmetro REPAIR_ALLOW_DATA_LOSS, quando uma página está corrompida ao usar este parâmetro no DBCC CHECKDB a página anterior simplesmente muda o ponteiro e aponta para a próxima página que estiver OK, então é possível que exista perda de dados. Então em qualquer situação de banco de dados corrompido o mais adequado é recuperar o backup.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Criptografia Transparente de Dados - Parte II

Bem pessoal, como disse na primeira parte deste tema mostrei como implantar a criptografia transparente de dados em um banco de dados, agora irei passar para vocês todos os testes que realizei.

Continuo usando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.

1° Teste: Desatachar o banco de dados criptografado e tentar atachar em outro servidor SQL que não existe nenhuma chave nem certificado de criptografia

USE master
GO   
sp_detach_db 'Criptografia'
GO

e em seguida tentarei atachar no outro servidor:

EXEC sp_attach_db @dbname = 'Criptografia',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO

após executar este comando receberemos a seguinte mensagem do SQL Server:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x2400EE11A47193208473BE1225A55AC13B4405B1'.

Para conseguirmos atachar o banco de dados criptografado em outro servidor é necessário criar a Master Key com a mesma senha do servidor onde o banco foi criptogrado, levar os arquivos do certificado e da chave criptografada para este servidor e em seguinda criar um certificado passando esses arquivos.

No servidor onde queremos atachar o banco executamos os seguintes scripts:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO

USE master
GO
CREATE CERTIFICATE CertificadoCriptografia
 FROM FILE = 'C:\Criptografia_certificate.cer'
 WITH PRIVATE KEY (FILE = 'C:\Criptografia_certkey.pvk', DECRYPTION BY PASSWORD = 'MasterKeysenha')
GO

após criado a Master Key e o certificado vamos tentar a atachar o banco novamente:

EXEC sp_attach_db @dbname = 'Criptografia',
    @filename1 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia.mdf',
    @filename2 = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Criptografia_log.ldf'
GO

e receberemos a mensagem:

Command(s) completed successfully.

2° Teste: Fazer backup do banco criptografado e tentar restaurar o backup em outro servidor que não possui nenhuma chave nem certificado de criptografia

BACKUP DATABASE Criptografia TO DISK='C:\Backup Criptografia\Criptografia.bak'
GO

agora vamos tentar restaurar este backup no outro servidor:

RESTORE DATABASE Criptografia FROM DISK='C:\Backup Criptografia\Criptografia.bak'

após tentar restaurar o SQL exibe a seguinte mensagem:

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


Para conseguir restaurar o backup em outro servidor é necessário realizar o mesmo procedimento relizado no teste 1. Tem que criar a Master Key e depois criar o certificado, assim o restore é concluído com sucesso.


Com estes dois testes foi possível ver que a criptografia transparente de dados realmente funciona e é bastante interessante, pois ainda não existia nenhuma criptografia interna do SQL Server a nível dos arquivos de dados e de log.

Na próxima parte iremos realizar os teste mais interessantes, mostrarei como desabilitar a TDE de um banco de dados se é possível atachar e/ou restaurar o banco de dados em outro servidor após desabilitar a criptografia.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

Criptografia Transparente de Dados - Parte I

Passei essa semana testando a nova funcionalidade de criptografia do SQL Server 2008, a TDE(Transparent Data Encryption), comecei a ler e fiquei muito interessado pois era exatamente o que eu precisava, pois ela funciona a nível dos arquivos (.mdf e .ldf) do banco de dados.

A TDE criptografa os arquivos isso quer dizer que após o acesso ao banco de dados todas as informações estarão disponíveis, mas o problema esta justamente ai, para ter este acesso...

Nesta primeira parte veremos como implementar esta funcionalidade em um banco de dados, nos próximos post veremos os detalhes dos testes realizados.

Estou utilizando o SQL Server 2008 Developer Edition com SP2, build 10.0.4000.

Primeiramente criei um banco de dados simples com somente uma tabela:

USE Master
GO

CREATE DATABASE Criptografia
GO

USE Criptografia
GO

CREATE TABLE Pessoa(
 Codigo int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 Nome varchar(20) NOT NULL,
 Idade int NULL) ON [PRIMARY]
GO

Em seguida inserir alguns registros:

INSERT INTO Pessoa VALUES ('Rodrigo Figueiredo',25)
GO 100

Vamos começar agora a implantar a TDE no banco Criptografia:

--Primeiro passo: Criar Master Key encryption no banco master
USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeysenha'
GO

/* Segundo passo: Criar um certificado no banco master.
 A chave privada deste certificado será protegida pela Master Key*/
USE master
GO

CREATE CERTIFICATE CertificadoCriptografia
WITH SUBJECT = 'Certificado Criptografia'
GO

Agora que já criamos a Master Key e o Certificado podemos criptografar o banco de dados:

/* Terceiro passo: criar uma chave de criptografia para a base de dados,
   usando o certificado criado anteriormente.
   Podemos escolher o algoritmo de criptografia que queremos usar:
   triple DES, AES 128, AES 192 ou AES 256 */

USE Criptografia
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CertificadoCriptografia
GO

/* Este comando vai retornar um Warning,
 Lembrando que devemos fazer um backup de nosso certificado imediatamente. */

USE master
GO

BACKUP CERTIFICATE CertificadoCriptografia
TO FILE = 'C:\Backup Criptografia\Criptografia_certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\Backup Criptografia\Criptografia_certkey.pvk',
 ENCRYPTION BY PASSWORD = 'MasterKeysenha');
GO

--Quarto passo: habilitar o TDE na nossa base
ALTER DATABASE Criptografia
SET ENCRYPTION ON
GO

Neste momento o banco Criptografia está criptografado utilizando o algoritmo AES_256. Se executarmos a consulta:

SELECT DB_NAME(e.database_id) AS DatabaseName,
            e.encryption_state,
    CASE e.encryption_state
                WHEN 0 THEN 'No database encryption key present, no encryption'
                WHEN 1 THEN 'Unencrypted'
                WHEN 2 THEN 'Encryption in progress'
                WHEN 3 THEN 'Encrypted'
                WHEN 4 THEN 'Key change in progress'
                WHEN 5 THEN 'Decryption in progress'
    END AS encryption_state_desc,
   key_algorithm +'_'+ convert(nvarchar,key_length) [Algoritmo],
            c.name [Nome Certificado],
            set_date [Data Criptografia]
    FROM sys.dm_database_encryption_keys AS e
    LEFT JOIN master.sys.certificates AS c
    ON e.encryptor_thumbprint = c.thumbprint

Veremos que dois bancos estão criptografados usando o algoritmo AES_256, o banco Criptografia utilizando certificado criado por nós e o banco tempdb sem utilizar nenhum certificado. O banco tempdb é criptografado sempre que qualquer banco da instância for criptgrafado, isso acontece porque o tempdb é utilizado por todos os banco de dados existentes na instância.

Como sabemos toda criptogafia tem um custo de I/O e principalmente de CPU, a criptografia após aplicada no banco de dados funciona da seguinte maneira: Toda vez no momento que a informação é gravada no disco ela é criptografada e sempre que for lida do disco para a mémoria ela é descriptograda, isso gera um custo de I/O, mas praticamente imperceptível. O custo maior fica por conta do processamento, devido ao algoritmo usado, quanto mais seguro for o algoritmo maior será o custo de CPU.

Bem galera esta foi a primeira parte e mostrei apenas como implementar TDE em um banco de dados, nós próximos posts falarei dos testes feitos, de backup e restore do banco de um servidor para outro e também detach e attach entre servidores utilizando TDE.

Abraços,
Rodrigo Figueiredo
figueiredo.rodrigo@hotmail.com

MCTS SQL Server 2008

Depois de alguns meses de estudos e já com alguma experiência nas novas features do SQL Server 2008 resolvi fazer a prova 70-432, bem galera fui aprovado e acho que o resultado foi fruto do meu estudo... Passei com 980 :DD

Fiquei muito feliz e agora já comecei os estudos para a 70-450. Essa foi minha segunda certificação Microsfot e a parti de agora irei focar bastante nestas certificações.

Abraços,

Rodrigo Figueiredo

figueiredo.rodrigo@hotmail.com 

Aberta as inscrições para o Worldwide Online TechDay 2010

Galera abriu hoje as inscrições para o Worldwide Online TechDay 2010. As palestras iniciam às 08:45 do dia 30 de outubro e vão até 4h da manhã do dia 31 de outubro, horário de São Paulo.
As palestras serão ministradas em português e inglês.

Quem ainda não viu a programação com certeza vale a pena, muitas palestras bem interessantes sobre SQL Server e PowerShell.

Para inscrições e programação: http://www.online.techday.net.br/

Com certeza esse evento será um sucesso, todos os palestrantes são muito bons e o conteúdo técnico também está muito bom.

Eu já estou inscrito!!

Abraços,

Rodrigo Figueiredo

figueiredo.rodrigo@hotmail.com

Liberado SQL Server 2008 Service Pack 2

A Microsoft liberou ontem a versão final do SQL Server 2008 Service Pack 2. O Service Pack 2 atualiza qualquer instância do SQL Server 2008, do SQL Express ao SQL Enterprise e todas as ferramentas clientes. Esse service pack não serve para o SQL Server 2008 R2.

Um "detalhe" bem importante e que você deve estar totalmente certo antes de instalar o SP2, pois ele contem os hotfixes que foram incluídos nos Cumulative Update 1 até o Cumulative Update 8. Ou seja, se sua instância de SQL Server já está atualizada com o CU9 ou CU10 e se você realmente corrigiu o problema com um destes CU, pode ser que  atualizando para o SP2 volte a ter o problema.
Segue link com a lista de bugs corrigidos pelo SQL Server 2008 SP2.
http://support.microsoft.com/kb/2285068

O build do SQL após ser aplicado o SP2 será 10.0.4000

Para mim as correções e atualizações mais importantes foram relacionadas a Clusterização e também que agora o SQL Server dará suporte ao PowerShell 2.0.

Sugestão: Não deixe de homologar o SP2 em seu ambiente de desenvolvimento

Abraços,

Rodrigo Figueiredo

figueiredo.rodrigo@hotmail.com