SQL Server 2016 – Always Encrypted

Olá pessoal, tudo certo?

Vou aproveitar o lançamento oficial do SQL Server 2016, e vou começar a fazer uma serie de post sobre as novas features disponibilizadas, neste primeiro post da série vou abordar o Always Encrypted uma feature de segurança que achei muito interessante.

A feature Always Encrypted  permite proteger os dados sensíveis de qualquer acesso não autorizado nem mesmo o DBA consegue visualizar as informações, é uma feature de criptografia do lado da aplicação.

Os dados quando são escritos no banco de dados são criptografados, e descriptografados para o cliente quando o acesso é realizado por uma aplicação autorizada. É importante saber que o Always Encrypted é diferente do TDE (Transparent Data Encryption) que criptografa os dados no disco, mas permite que os dados sejam lidos por qualquer aplicativo que consulta os dados.

Driver suportados:

A Microsoft suportou os driver .NET Framework Data Provider for SQL Server, para instalar é necessário atualizar o .Net Framework para o .NET Framework 4.6, Microsoft JDBC 6.0 e o ODBC Microsoft 13.

O driver do banco de dados Always Encrypted se conecta ao banco de dados usando uma chave de criptografia, os dados só podem ser descriptografado usando a chave de criptografia, outras aplicações ou consultas de dados pode recuperar os valores criptografados, mas esses valores não será descriptografado e, portanto, permanecerá seguro.

Tipos de criptografia suportados:

Always Encrypted supporta dois tipos de criptografia, randomized encryption e deterministic encryption

Randomized encryption –  Como o nome diz “Random”, ele gera um valor diferente para cada execução, apesar de ser um modo mais seguro, ela não suporta pesquisa de igualdade e indexação. Você deve usar o tipo de criptografia randomizados para colunas usadas apenas para fins de exibição.

Deterministic Encryption – Ao contrario do tipo randomized encryption, o deterministic encryption sempre retorna o mesmo valor para codificado para qualquer pesquisa. A utilização do deterministic encryption permite agrupar, filtrar por igualdade e fazer consulta utilizando join. As colunas do tipo texto (varchar, char) devem ser criadas com o collation  Latin1_General_BIN2.

Utilize deterministic encryption para colunas que serão usados como parâmetros de busca ou de agrupamento, por exemplo, CPF, RG.

Criando Column Master Key e Column Encryption Key

Para começar a utilizar o Always Encrypted, primeiro precisamos criar a Master key e depois chave de criptografia utilizando a Master Key.

Column Master Key

• Navegar na Database => Security => Always Encrypted Keys => New Column Master Keys

Gerar a nova chave.

Após criar a chave, você deve gerar o certificado e aplicar nos dispositivos autorizados.

Para exportar o certificado utilizaremos o Power Shell.

-- O valor da chave (E4200028AB17692F5B7B934C4FB56BC633D2C5E2) foi o valor gerado ao criar a chave na coluna tumbiprint da imagem anterior 

$cert = (Get-ChildItem -Path cert:\CurrentUser/My/E4200028AB17692F5B7B934C4FB56BC633D2C5E2)

Export-Certificate -Cert $cert -FilePath C:\Temp\Certificado\SQL2016.sst -Type SST

Para mais informações de como exportar o certificado gerado você pode ver nos links abaixo:

• Windows 7 and Windows Server 2008 R2: https://technet.microsoft.com/en-us/library/cc730988.aspx
• Windows 8 and Windows Server 2012: https://technet.microsoft.com/en-us/library/hh848628(v=wps.620).aspx
• Windows 8.1 and Windows Server 2012 R2: https://technet.microsoft.com/en-us/library/hh848628(v=wps.630).aspx
• Windows 10 and Windows Server 2016: https://technet.microsoft.com/en-us/library/hh848628(v=wps.640).aspx

Após exportar o certificado, você tem que importar o certificado para as aplicações e dispositivos autorizados.

$file = ( Get-ChildItem -Path C:\Temp\Certificado\SQL2016.sst )

$file | Import-Certificate -CertStoreLocation cert:\CurrentUser\Root

Para mais informações de como importar o certificado você pode ver nos links abaixo:

• Windows 7 and Windows Server 2008 R2: https://technet.microsoft.com/en-us/library/cc754489.aspx
• Windows 8 and Windows Server 2012: https://technet.microsoft.com/en-us/library/hh848630(v=wps.620).aspx
• Windows 10 and Windows Server 2016: https://technet.microsoft.com/en-us/library/hh848630(v=wps.640).aspx
• Windows 8.1 and Windows Server 2012 R2: https://technet.microsoft.com/en-us/library/hh848630(v=wps.630).aspx

Column Encryption Key

Após gerar a Column Master Key, devemos gerar a Column Encryption Key.

• Navegar na Database => Security => Always Encrypted Keys => New Column Encrytion Keys

De um nome para sua chave, e selecione a Column Master Key que você deseja utilizar, no caso vamos utilizar a chave criada no step anterior.

Criando uma tabela com Always Encrypted

Depois de criar as chaves, podemos criar uma tabela e criptografar a coluna. A sintaxe de criação de tabela é a mesma, porem, precisamos adicionar alguns parâmetros na coluna que vamos criptografar os dados.

Se a coluna for do tipo varchar precisamos colocar ela com a colattion Latin1_General_BIN2, depois informamos qual é o tipo de criptografia que vamos utilizar se é DETERMINISTIC ou RANDOMIZED, o algoritmo de criptografia padrão do SQL Server e a informar qual é a chave que criamos.

CREATE TABLE dbo.[Employee](
	[BusinessEntityID] [int] NOT NULL,
	[NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_BIN2
	ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,
	ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
	COLUMN_ENCRYPTION_KEY = Demo1) NOT NULL,
	[LoginID] [nvarchar](256) NOT NULL,
	[OrganizationNode] [hierarchyid] NULL,
	[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),
	[JobTitle] [nvarchar](50) NOT NULL,
	[BirthDate] [DATE]
	ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED,
	ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
	COLUMN_ENCRYPTION_KEY = Demo1) NOT NULL,
	[MaritalStatus] [nchar](1) NOT NULL,
	[Gender] [nchar](1) NOT NULL,
	[HireDate] [date] NOT NULL,
	[SalariedFlag] [dbo].[Flag] NOT NULL,
	[VacationHours] [smallint] NOT NULL,
	[SickLeaveHours] [smallint] NOT NULL,
	[CurrentFlag] [dbo].[Flag] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Employee_BusinessEntityID] PRIMARY KEY CLUSTERED 
(
	[BusinessEntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Importando dados de uma tabela normal para uma tabela criptografa

Como eu não criei uma aplicação para inserir os dados, vou demonstrar como exportar os dados de uma tabela normal, para a tabela com coluna criptografa.

Botão direito sobre a database => Task = > Import Data

Selecione qual é o driver que você vai utilizar para conectar na origem, com vamos conectar no próprio SQL Server vamos utilizar o SQL Server Native Client.

Colocamos o nome da instância e o nome da base.

Selecionamos o destino, como foi dito como vamos utilizar uma tabela com coluna criptografada, precisamos utilizar uma conexão .Net 4.6, então selecionamos o driver “.Net Framework Data Provider for SqlServer”  => habilitamos a opção “Column Encryption Setting” => Marcamos “Integrated Security = TRUE” => Informamos o Data Source “GDSQLT52\SQL2016″ => Informamos o nome da base de dados ” AventureWorks”.

Selecionamos as tabelas de origem e destino.

Informamos se vamos escrever uma query ou se vamos importar todos os dados da tabela.

Informações de resumo do que vai ser realizado.

Resumo da importação.

Visualizando os dados criptografados

Para visualizar os dados basta fazer um select normal da tabela criptografada.

SELECT
    *
FROM
    AdventureWorks.dbo.Employee;

Como podemos ver as colunas “NationalIDNumber” e “BirthDate” retornaram as informações criptografadas.

Visualizando os dados descriptografados

Para visualizar os dados sem criptografia o primeiro passo é importar o certificado na sua maquina, servidor de aplicação e etc… isso já foi demonstrado no inicio do post.

Partindo que o certificado está instalado na sua maquina ou servidor, agora você precisa adicionar o parâmetro “Column Encryption Setting = enabled;” na string de conexão da aplicação.

Para visualizar os dados no management studio, você tem que acionar o mesmo parâmetro na propriedades avançadas.

Na tela de autenticação selecione “Options”

Selecione a guia “Additional Connection Parameters”

Adicione o parâmetro “Column Encryption Setting = enabled;”

Depois de conectado basta executar a consulta que as colunas “NationalIDNumber” e “BirthDate”  estarão descriptografadas.

SELECT
    *
FROM
    AdventureWorks.dbo.Employee;

Restrições do Always Encrypted

Como nem tudo são flores o Always Encrypted tem suas restrições e desvantagens, os tipos de dados xml, timestamp/rowversion, image, ntext, text, sql_variant, hierarchyid, geography, geometry, alias e user defined-types não são suportados para criptografia.

• String (varchar, char, etc.) columns somente com bin2 collations
• Colunas computadas não são suportadas
• Primary key não podem utilizar criptografia do tipo randomized encryption
• Não podem ser replicadas
• Não suportam Linked Server

No channel 9 tem um vídeo onde Jakub Szymaszek e Kaivalya Hanswadkar mostram como utilizar o Always Encrypted no SSMS.

Referencias:

https://msdn.microsoft.com/en-us/library/mt163865.aspx?f=255&MSPPError=-2147217396

https://channel9.msdn.com/shows/data-exposed/getting-started-with-always-encrypted-with-ssms

https://www.mssqltips.com/sqlservertip/4011/sql-server-2016-always-encrypted/

https://blogs.msdn.microsoft.com/sqlsecurity/2015/06/04/getting-started-with-always-encrypted/

Bom pessoal, por hoje é isso espero que tenham gostado, nos próximos posts vamos continuar a série novas features do SQL Server 2016.

Abraços,

Tiago Neves