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.

1

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

SQLServer2016_AlwaysEncrypted_Create_Column_Master_Keys

Gerar a nova chave.

SQLServer2016_AlwaysEncrypted_New_Column_Master_Keys

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:

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:

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

SQLServer2016_AlwaysEncrypted_Create_Column_Column_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.

SQLServer2016_AlwaysEncrypted_New_Column_Encryption_Keys

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

SQLServer2016_AlwaysEncrypted_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.

SQLServer2016_AlwaysEncrypted_Import_Data_Choose_Source

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”.

SQLServer2016_AlwaysEncrypted_Import_Data_Choose_Destination

Selecionamos as tabelas de origem e destino.

SQLServer2016_AlwaysEncrypted_Import_Data_Choose_Table

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

SQLServer2016_AlwaysEncrypted_Import_Data_Copy_Table

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

SQLServer2016_AlwaysEncrypted_Import_Data_Mapping_Table

Resumo da importação.

SQLServer2016_AlwaysEncrypted_Import_Data_Result

Visualizando os dados criptografados

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

SELECT
    *
FROM
    AdventureWorks.dbo.Employee;

SQLServer2016_AlwaysEncrypted_Result_Select_Not_Certificate

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”

SQLServer2016_AlwaysEncrypted_Connect_SSMS

Selecione a guia “Additional Connection Parameters”

SQLServer2016_AlwaysEncrypted_Connect_SSMS_Additional_Parameter

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

SQLServer2016_AlwaysEncrypted_Connect_SSMS_Additional_Parameter_Setinfo

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

SELECT
    *
FROM
    AdventureWorks.dbo.Employee;

SQLServer2016_AlwaysEncrypted_Result_Select_With_Certificate

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

One Comment

Deixe uma resposta