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
Azure SQL Database How To SQL Server Virtual PASS BR

Data Migration Assistant – Migration

[bing_translator]

A ferramenta DMA v3.3 nesse momento tem duas opções de projetos: Assessment e Migration.
No post anterior Data Migration Assistant – Assessment falei um pouco sobre a funcionalidade de Assessment para assessorar e minimizar o impacto da migração de databases. Esse é o primeiro passo que você deve tomar para realizar uma migração e após verificar todos os itens dos relatórios você está “pronto” para iniciar a migração, pelo menos por parte do database não esqueça de verificar se a aplicação está preparada para a migração.

Ao iniciar a ferramenta DMA crie um projeto do tipo Migration onde iremos definir qual tipo de servidor de origem e destino.

Nesse momento temos que indicar qual o escopo da migração, que pode ser:

  • Schema and data
  • Schema only
  • Data only

A partir dessas opções podemos criar vários cenários para migração de um database como por exemplo:

  • Criar um servidor de teste e um database apenas com os metadados (schema); realizar o apontamento da aplicação para esse servidor fazendo assim um teste também com a aplicação.
  • Se precisar de poucos dados, aquelas famosas tabelas de configurações, você pode importar apenas o que precisa.

Desse ponto de vista temos uma flexibilidade enorme para trabalhar e eu escolhi o escopo: Schema and data para demonstrar do início ao fim o processo.

De forma simples o wizard irá te ajudar e nesse processo teremos 6 etapas sendo a primeira escolher a instancia e database de origem.

Uma tarefa que o wizard não faz é criar o database no Azure e por isso você deve criar um database vazio através do portal, powershell ou CLI antes de continuar. Basta seguir os passos desse link.

Eu criei um database chamado AdventureWorksDMA através do portal e o próximo passo é escolher quais objetos serão migrados.

Uma vez que tenha escolhido os objetos que serão migrados o passo a seguir é gerar os scripts desses objetos.

Aqui você tem a opção de salvar o script para analisar posteriormente, copiar para qualquer editor de texto ou realizar o deploy diretamente através do DMA que irá fazer o deploy para o Azure SQL Database.

Uma janela com informações dos resultados irá mostrar o progresso do deploy e os comandos executados. Ao finalizar existe a opção de redeploy ou iniciar a migração dos dados e como nosso objetivo é ter uma migração completa vamos seguir com a migração dos dados.

Selecione as tabelas desejadas ou todas e inicie o seu processo de migração.

O tempo que a migração depende de alguns fatores dentre eles posso citar: quantidade de tabelas e registros em cada uma delas e a performance do seu Azure SQL Database (DTU), no meu caso utilizei S0 (10 DTU).

Uma vez que a migração dos dados terminou com sucesso você pode se conectar no Azure SQL database através do SSMS (Azure SQL Database – SQL Authentication) para verificar que sua migração está finalizada com sucesso.

Categorias
Azure SQL Database How To SQL Server Virtual PASS BR

Data Migration Assistant – Assessment

[bing_translator]

Migração de um database é uma rotina comum na vida de um DBA e essa atividade pode ser para uma versão mais atual do SQL Server ou para o Azure SQL Database. A Microsoft sempre tentou auxiliar essas migrações com ferramentas como por exemplo o Upgrade Advisor. Por muito tempo utilizei essa ferramenta para as migrações que realizei pois ela gerava um relatório com todos os possíveis “problemas” que poderia ter.

Recentemente precisei migrar um database on-premise para cloud e ao verificar a versão mais atual do Upgrade Advisor descobri que a Microsoft decidiu descontinuar essa ferramenta. Você ainda pode baixar a ferramenta atrás desse link porem eu aconselho a utilizar a nova ferramenta o Data Migration Assistant (DMA). Essa ferramenta trouxe novas capacidades e facilidades onde uma delas é centralizar na mesma ferramenta as opções de realizar a migração ou apenas realizar a validação do database para uma futura migração.

É sobre essa nova ferramenta DMA que irei falar um pouco hoje.

Após fazer o download através do link acima e instalar a ferramenta, você pode notar na tela iniciar uma facilidade que é a criação de projetos. Estou utilizando a versao 3.3

Nesse momento existem dois tipos de projetos:

  • Assessment
  • Migration

O Assessment é a funcionalidade que iremos utilizar para verificar os possíveis problemas e incompatibilidades que temos ao migrar um database.

Depois de dar um nome ao seu projeto você deve escolher o tipo de servidor de origem que no meu caso só tenho a opção de SQL Server.

Nesse momento você deve selecionar qual o seu objetivo final, ou seja, qual o tipo de servidor que o seu database será hospedado. No meu caso 3 opções são apresentadas: Azure SQL Database, SQL Server e SQL Server on Azure Virtual Machines.

Como tenho trabalho com Azure SQL Databases vou utilizar essa opção para demonstração.

Agora que você escolheu as opções e criou o projeto uma nova janela irá apresentar quais os tipos de relatórios você gostaria de gerar.

Nesse momento é possível optar por gerar relatórios separados, o que pode ser útil quando existe uma lista grande de itens que estão sendo tratados aos poucos e você pode medir quanto esforço foi realizado ou falta a ser feito.

  • Check database compatilibity – verificará problemas que podem ser bloqueadores para a migração assim como features descontinuadas.
  • Check feature parity – verificará por features que não são suportadas ou são parcialmente suportadas.

Um terceiro tipo de relatório está por vim e parece ser bem interessante uma vez que ele informará quais são as possíveis features que podem ser utilizadas no seu database uma vez que você realizar a migração e isso é um benefício que pode ser traduzido em performance, tamanho, segurança entre outros (essa é a minha opinião sobre o que esperar desse relatório)

Próximo passo é escolher a instancia SQL Server e o(s) database(s) que fará parte do projeto de migração.

 

Uma lista com os databases selecionados será apresentada e você pode adicionar ou remover servidores de origem dentro do seu projeto, ou seja, pense que uma aplicação tem mais de um database em mais de uma instancia e com isso é possível criar um projeto com múltiplas instancias SQL Server e múltiplos databases.

Após iniciar o Assessment e a ferramenta terminar de analisar, você terá seus relatórios prontos e sua análise desses é extremamente fácil uma vez que está divido em seções.

Relatório: SQL Server features parity

  1. Recomendações e informações sobre features
  2. Detalhes sobre uma das recomendações selecionadas.
  3. Nível de aplicabilidade da recomendação. (Não se aplica a todas as recomendações)

Relatório: Compatility issues

  1. Issue (problema) agrupado por categorias
  2. Detalhes sobre o issue
  3. Objetos dentro database que são impactados pelo issue.

Dessa forma fica fácil visualizar o que devemos fazer para corrigir cada um dos issues antes de migrar o database.

No campo superior direito existem duas opções: Restart Assessment e Delete Assessment. Uma forma rápida para regerar o relatório ou excluir!

Para concluir é possível exportar o relatório para dois formatos: CSV e JSON. Particularmente o formato CSV não me agradou e o JSON é a nova onda do momento de transferia de dados de forma leve!

Categorias
Azure SQL Database How To SQL Server Virtual PASS BR

Data Files no Azure – Uma outra forma de criar um database

[bing_translator]

Você já deve ter criado inúmeros databases dentro do seu ambiente on-premise ou em uma VM no Microsoft Azure utilizando IaaS e isso deve ser algo trivial para você.
Comece a pensar em uma outra forma de criar o seu database fazendo um mix entre on-premise ou VM e o Microsoft Azure Storage, é isso mesmo, você ter os dados armazenados na cloud e continuar usando seus recursos locais (memoria, CPU, Network e etc.)

Aquele velho dilema com o administrador de storage quando o DBA precisa de mais espaço pode ser minimizado com essa nova forma de criar um database.
O que seria essa forma? Isso seria armazenar seus Data Files no Microsoft Azure Blobs o que permite várias soluções híbridas, fornecendo vários benefícios para a virtualização de dados, movimento de dados, segurança e disponibilidade.

Alguns benefícios que podemos ter:

  • Migração simples e rápidos
  • Custo e armazenamento ilimitados
  • Alta disponibilidade e recuperação de desastres
  • Segurança
  • Snapshot backup

Vamos ver como isso funciona!

O que iremos precisar é de uma Storage Account:

Duas configurações são importantes: Account kind e Replication. Até nesse momento alguns tipos de replicação não suportam data files do SQL Server.
Sua storage account pode conter alguns tipos de serviços e nós iremos utilizar o BLOBs.

Acesse o serviço de Blobs e crie um novo container e acesse suas propriedades para pegar a URL. Copie a URL para o seus SSMS pois irá precisar dela em breve.

Volte a blade da sua storage account e acesse Shared Access Signature. Clique em Generate SAS para obter o token de acesso e copie o SAS token para seu SSMS.

Observe que o primeiro caractere é um ponto de interrogação “?” e deve ser removido!!!
Nesse momento já temos tudo o que precisamos do lado do Microsoft Azure e podemos criar a credencial necessária para acesso.

Para criar as credencias utilize o T-SQL abaixo

CREATE CREDENTIAL [https://sqlgeneral.blob.core.windows.net/database] 
WITH IDENTITY = N'Shared Access Signature',  --- NÃO MUDE. ISSO É MANDATORIO
SECRET = N'sv=2017-04-17&ss=bfqt&srt=sco&sp=rwdlacup&se=2017-08-23T22:14:51Z&st=2017-08-22T14:14:51Z&spr=https&sig=k1kHJoJKFKPxAJu'
GO

Nome da credencial deve ser a URL do seu container. Em SECRET insira o SAS token sem o primeiro caractere “?”

Com a credencial criada você pode criar seu database

CREATE DATABASE [FirstDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'FirstDB', FILENAME = 'https://sqlgeneral.blob.core.windows.net/database/FirstDB.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'FirstDB_LOG', FILENAME = 'https://sqlgeneral.blob.core.windows.net/database/FirstDB_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

A única diferença é que você deve apontar seus data files para a URL e não mais para um disco local.
Uma nova coluna (credential_id) na DMV sys.master_databases foi introduzida no SQL Server 2014 para fazer essa referência.

Você também pode verificar os arquivos através do portal do Azure acessando o container que criou:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/sql-server-data-files-in-microsoft-azure

https://docs.microsoft.com/en-us/azure/storage/common/storage-introduction

Categorias
Azure SQL Database How To SQL Server Virtual PASS BR

Azure SQL Database – Dynamic Data Masking

[bing_translator]

O Azure SQL Database estende a funcionalidade de Dynamic Data Masking (DDM) que foi introduzido no SQL Server 2016.
A intenção desse post é ter um overview sobre o que o DDM e mostrar como utilizar essa funcionalidade no Azure SQL Database

O que é o DDM

Ofuscar dados em tempo real para impedir o acesso não autorizado
Os dados armazenados em disco não são criptografados ou alterados
Útil para ocultar dados sensíveis e confidenciais, como números de identificação pessoal, números de cartão de crédito, data de nascimento e assim por diante.

Como o DDM funciona

Ofuscamento sobre os dados nos resultados da consulta
As regras podem ser definidas em colunas particulares
Não há mudanças físicas (storage)
Os dados permanecem intactos e estão totalmente disponíveis para usuários / aplicativos autorizados.
Os dados em máscaras têm o mesmo tipo de dados que os dados originais
Múltiplas funções de máscara disponíveis para várias categorias de dados sensíveis
Flexibilidade para definir um conjunto de logins privilegiados para acesso de dados não mascarados
Por padrão, db_owner do banco de dados tem acesso aos dados sem máscara.
Pode ser aplicado sem fazer alterações nos procedimentos do banco de dados ou no código da aplicação.

Existem 5 tipos de máscaras disponíveis (espere que tem uma surpresa)

  • Default ou Full masking
  • Email
  • Custom String ou Partial masking
  • Random
  • Credit card – Até o momento somente disponível do Azure SQL Database
Função Antes da Máscara Depois da Máscara
Default () –  O valor é completamente ofuscado.

Number: 487

Text: Tiago

(FUNCTION = ‘default()’)

Number: 000

Text: XXX

Email() – Ofusca quase tudo menos a primeira letra, @ e sufixo tiago@email.com

(FUNCTION = ‘email()’)

tXX@XXXXX.com

Partial() – Personalizada em que você determina o quanto será mostrado 912-564-897

(FUNCTION = ‘partial(1,”XXXXXXX”,0)’)

9XXXXXXXXX

(FUNCTION = ‘partial(3,”XXXXXXX”,0)’)

912XXXXXXX

Random() – Usado com tipos de dados numéricos. Ofusca os dados com um valor aleatório em um determinado intervalo 487

(FUNCTION = ‘random(1,1000)’)

198, ou 633, ou 1000

Credit card – expõe os últimos quatro dígitos e adiciona uma sequência constante como um prefixo na forma de um cartão de crédito 1234-5678-9876-1234 XXXX-XXXX-XXXX-1234

Usando o portal do Azure

Para utilizar essa funcionalidade acesse seu database através do portal e procure a opção Dynamic Data Masking. Uma vez que você acessar o próprio Azure irá te sugerir tabelas e colunas.

Um simples clique em ADD MASK você terá criado a máscara para aquela coluna e uma lista com todas as mascaras irá aparecer

O Azure SQL Database tenta identificar a tipo de dados e sugere um tipo de máscara (muitas vezes acerta outras nem tanto) porém é possível mudar o tipo de máscara clicando sobre o nome do mascara uma nova blade irá abrir.  Escolha a nova máscara que melhor se aplique para o tipo de dados da coluna e atualize a máscara. Da mesma forma é possível apagar a máscara que foi criada apenas clicando no botão DELETE.

Assim você pode rever todas as colunas que deseja e é possível mudar isso de forma simples e rápido. Quando tiver pronto salve as alterações.

Uma forma de verificar as colunas e as máscaras é consultar uma DMV nova – sys.masked_columns.

E agora com PowerShell?

Tudo se torna fácil para automatizar com PS e uma surpresa (na opinião boa) apareceu quando estava criando os comandos. Um outro tipo de máscara “SocialSecureNumber” está disponível e até o momento não é possível utilizar pelo portal (o que eu posso concluir que existem outros comandos com esse mesmo comportamento e que existe muito trabalho para o time de desenvolvimento do portal)

Porém uma vez que você criou a máscara por PS a informação fica disponível para visualização com o tipo de máscara correto!

Aqui esta a lista de comandos PS:

#region connect to Azure

Login-AzureRmAccount
Get-AzureRmSubscription 
Select-AzureRmSubscription -SubscriptionName 'MSDN Platforms'

#endregion disk caching

#list of commands 
Get-Command -Module AzureRM.Sql -Noun *masking*

#region get all masking rule in all databases or masking policy

$ResoureGroup = Get-AzureRmResourceGroup | where {$_.ResourceGroupName -contains “SQL-Database”} #or $ResoureGroup = “SQL-Database”
$ServerName = Get-AzureRmSqlServer -ResourceGroupName $ResoureGroup.ResourceGroupName
$Databases = Get-AzureRmSqlDatabase -ResourceGroupName $ResoureGroup.ResourceGroupName -ServerName $ServerName.ServerName | where {$_.CurrentServiceObjectiveName -ne “System0”} 

foreach ($db in $Databases)
{
    Get-AzureRmSqlDatabaseDataMaskingRule -ResourceGroupName $ResoureGroup.ResourceGroupName -ServerName $ServerName.ServerName -DatabaseName $db.DatabaseName | Format-Table
   # Get-AzureRmSqlDatabaseDataMaskingPolicy -ResourceGroupName $ResoureGroup.ResourceGroupName -ServerName $ServerName.ServerName -DatabaseName $db.DatabaseName | Format-Table
}

#endregion

#region add new masking rule

New-AzureRmSqlDatabaseDataMaskingRule -MaskingFunction SocialSecurityNumber -SchemaName "HumanResources" -TableName "Employee" -ColumnName "NationalIDNumber" -DatabaseName "AzureAdventureWorks" -ServerName $ServerName.ServerName -ResourceGroupName $ResoureGroup.ResourceGroupName

#endregion

#region remove new masking rule

Remove-AzureRmSqlDatabaseDataMaskingRule -SchemaName "HumanResources" -TableName "Employee" -ColumnName "NationalIDNumber" -DatabaseName "AzureAdventureWorks" -ServerName $ServerName.ServerName -ResourceGroupName $ResoureGroup.ResourceGroupName

#endregion

#region update a masking rule

Set-AzureRmSqlDatabaseDataMaskingRule -MaskingFunction Text -PrefixSize 1 -SuffixSize 5 -ReplacementString "XDXDXDXD" -SchemaName "Person" -TableName "PersonPhone" -ColumnName "PhoneNumber" -DatabaseName "AzureAdventureWorks" -ServerName $ServerName.ServerName -ResourceGroupName $ResoureGroup.ResourceGroupName
#endregion

#region update a masking policy

Set-AzureRmSqlDatabaseDataMaskingPolicy -DataMaskingState Enabled -DatabaseName "AzureAdventureWorks" -ServerName $ServerName.ServerName -ResourceGroupName $ResoureGroup.ResourceGroupName 

#endregion

Links:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started

https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/New-AzureRmSqlDatabaseDataMaskingRule?view=azurermps-4.2.0

Categorias
Azure SQL Database How To SQL Server Virtual PASS BR

Azure SQL Database – Azure Active Directory Authentication

[bing_translator]

No post anterior (link) expliquei como utilizar SQL Authentication para permitir acesso do Azure SQL Database e agora vamos ver como utilizar o Azure Active Directory Authentication.

Recapitulando! Nesse momento o Azure SQL Database permite dois tipos de autenticação:

  • SQL Authentication
    • A identidade é através de um usuário e senha. Quando você está criando um servidor logico você define um “server admin” que irá usar esse tipo de autenticação para se conectar a qualquer database.
  • Azure Active Directory Authentication
    • A identidade é gerenciada pelo Azure Active Directory e aqui você tem várias opções de identidade, não sou especialista nessa área, mas tentarei mostrar um pouco de como utilizar. Se você quiser usar esse tipo de autenticação, você deve criar outro administrador do servidor chamado “Administrador do Azure AD”, que tem permissão para administrar usuários e grupos do Azure AD. Este administrador também pode executar todas as operações que um administrador do servidor normal pode. Esse novo administrador também pode ser um grupo de usuários.

Azure Active Directory Authentication

Aqui a brincadeira começa a ficar interessante e uma vez que não sou especialista nessa área de AD irei mostrar como utilizar recursos nativos sem criar cenários complexos.

Criando usuário no Azure AD

Acesse o Azure AD através do Azure portal para criar usuários e grupo de usuários

Comece pelo grupo de usuário que irá administrar o servidor logico do Azure SQL Database.

Agora crie um novo usuário e aqui tem um jeito diferente para utilizar pois não temos um domino criado para vincular, mas sua conta do Azure tem um domínio vinculado.

Porem existe uma forma de utilizar o domain name para criar um usuário onde o user name deve ser: <name>@<domain name>, por exemplo deadpool@seudominio.onmicrosoft.com

Uma vez que você criou o usuário e verificou a senha que vem predefina já pode começar a usar porem eu esqueci alguma vezes de verificar qual a senha! Não tem problema é possível alterar a senha. Acesso o usuário e é possível usar a opção Reset password porem você pode definir uma senha, mas dessa vez você deve guardar essa senha pois ela é temporária.

Agora que o usuário está criado e senha é temporária, devemos alterar essa senha e para isso acesse o portal do Azure e faça login com o usuário e senha. Dessa forma será solicitado para que você altere a senha.

Para confirmar que você está utilizando o usuário que acabou de criar verifique o canto superior direito.

Definir Active Directory Admin

Agora que já tem o usuário e senha prontos, vamos para o servidor logico e configurar o outro administrador, o Active Directory Admin

Simplesmente acesse o seu servidor logico, procure a opção Active Directory Admin e adicione um novo usuário ou grupo. No meu caso eu vou utilizar o grupo no qual vinculei o usuário.

Você deve ter agora configurado dois administradores do seu Azure SQL Database, um Server Admin e outro Active Directory Admin

Usando Powershell para configurar um Active Directory Admin

Login-AzureRmAccount

#Verifica qual grupo/usuario configurado como adminstrador
Get-AzureRmSqlServerActiveDirectoryAdministrator -ServerName balabuchsqldb -ResourceGroupName SQL-database

#Define um grupo/usuario como adminstrador
Set-AzureRmSqlServerActiveDirectoryAdministrator -ServerName balabuchsqldb -ResourceGroupName SQL-database -DisplayName SQL-Admin

# Remove um grupo/usuario como adminstrador
Remove-AzureRmSqlServerActiveDirectoryAdministrator -ServerName balabuchsqldb -ResourceGroupName SQL-database

Acessando o database

Agora utilize o SSMS para se conectar no Azure SQL Database, lembre-se que você deve configurar o firewall (link).

Primeiramente eu utilizei a opção de autenticação: Active Directory Universal Authentication. Isso fará que uma janela seja levantada para autenticação onde você deve colocar usuário e senha.

Finalmente depois de realizar todos esses passos você conseguiu acesso ao Azure SQL Database como administrador

Mas e se eu não quiser acessar como administrador, como fazer? Siga os passos para criar um novo usuário igual acima porem não inclua esse usuário no grupo de SQL-ADMIN.

Acesse o SQL Azure Database com um Active Directory Admin, isso é obrigatório pois apenas esse usuário/grupo pode criar outros usuários de AD, escolha o database para se conectar, no meu caso dbMarvel, e eu criei um usuário chamado loki

CREATE USER [loki@seudominio.onmicrosoft.com] FROM EXTERNAL PROVIDER

Agora você pode utilizar a opção Active Directory Password Authentication:

Uma vez que temos um usuário criado podemos conceder ou revogar permissões da mesma forma que fazemos hoje no SQL Server e isso fica para um futuro post!

Dessa forma nos utilizamos dois métodos de autenticação novos que devemos começar a nos acostumar, eu acredito que esse tipo de autenticação irá se tornar mais comuns nos próximos anos!

 

 

 

 

 

 

Algumas referências:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

https://blogs.msdn.microsoft.com/sqlsecurity/2015/09/28/examples-of-some-connection-errors-for-azure-active-directory-authentication/

Categorias
Azure SQL Database How To SQL Server Virtual PASS BR

Azure SQL Database – SQL Authentication

[bing_translator]

No post anterior (link) falei um pouco sobre como o firewall pode ser configurado para permitir acesso ao Azure SQL Database e agora vem a pergunta de como posso me autenticar?

Nesse momento o Azure SQL Database permite dois tipos de autenticação:

  • SQL Authentication
    • A identidade é através de um usuário e senha. Quando você está criando um servidor logico, que ira hospedar seu database, você define um “Server Admin” que irá usar esse tipo de autenticação para se conectar a qualquer database.
  • Azure Active Directory Authentication
    • A identidade é gerenciada pelo Azure Active Directory e aqui você tem várias opções de identidade, não sou especialista nessa área, mas tentarei mostrar um pouco de como utilizar esse recurso. Se você quiser usar esse tipo de autenticação, você deve criar outro administrador do servidor chamado “Active Directory Admin“, que tem permissão para administrar usuários e grupos do Azure AD. Este administrador também pode executar todas as operações que um administrador do servidor normal pode. Esse novo administrador também pode ser um grupo de usuários

Vou utilizar o SQL Server Management Studio (SSMS) para se conectar ao Azure SQL Database, porem existem outras formas de se conectar e você pode utilizar o link (https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-ssms)

SQL Authentication

O primeiro acesso é feito com o “Server Admin” do servidor logico, ou seja, o usuário e senha que você criou quando estava criando o servidor logico.

Você pode acessar as informações através do portal do Azure. Primeiro na blade de overview ira te trazer algumas informações, mas você pode acessar a blade de propriedades onde você terá as informações necessárias sobre o server name e server admin login.

Agora que você tem as informações necessárias pode ir ao SSMS e se conectar ao Azure SQL Database. Acesse as opções e especifique o nome do database que você irá se conectar.

Uma vez que você conseguiu acessar o Azure SQL Database como adminstrador, você pode começar a fazer as suas atividades normalmente como por exemplo verificar algumas informações servidor, versão e edição.

Ou criar outros usuários para utilizar a SQL Authentication:

-- ======================================================================================
-- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
-- ======================================================================================
 
CREATE LOGIN Daredevil 
WITH PASSWORD = 'zxc456)(*' 
GO

Depois de criar o login voce deve se criar o usuário dentro de um database e para isso alterei minha conexão para o database (no meu caso dbMarvel) e executei o comando abaixo.

-- ========================================================================================
-- Create User as DBO template for Azure SQL Database and Azure SQL Data Warehouse Database
-- ========================================================================================
-- For login <login_name, sysname, login_name>, create a user in the database
 
CREATE USER Daredevil
FOR LOGIN Daredevil
WITH DEFAULT_SCHEMA = dbo
GO

Para utilizar o usuário basta indicar o database que deseja se conectar a informar a usuário e senha igual foi feito acima com o usuário administrador.

Eu sei que isso é bem basico e muito parecido com o que voce ja está acostumado mas o mais legal ainda esta por vir! Aguardem o proximo posto sobre Azure AD Authentication.

Categorias
Azure SQL Database How To SQL Server Virtual PASS BR

Azure SQL Database Firewall

[bing_translator]

Recentemente eu assisti uma palestra do Roberto Cavalcanti (twitter) no Pass – Global Portuguese (http://globalportuguese.pass.org) sobre Azure SQL Database e me fez pensar que eu deveria me aprofundar mais no assunto e comecei meus estudos.

A minha intenção nesse post não é demostrar como criar um database, mas sim como fazer com que as conexões dos clientes sejam permitidas a acessar esse database. Assim como no mundo on-premise o firewall deve permitir conexões em seu servidor no mundo cloud existe a mesma funcionalidade com algumas adaptações.
Para você criar um database siga esse documento (https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-portal)

No Azure SQL Database existem dois níveis de firewall: server-level e database-level

Server-Level Firewall Rules: podemos dizer que é igual ao mundo on-premise onde você libera a porta de acesso para o servidor como um todo, ou seja, o cliente é permitido a se conectar a instancia SQL Server e se tiver permissões suficientes pode acessar todos os databases.

Database-Level Firewall Rules: Esse novo modelo de segurança é interessante pois você define as regras de acesso diretamente a um único database. Esse tipo de regra era possível ser criado no mundo on-premise através de ferramentas de terceiros como por exemplos: Imperva SecureSphere for Data ou Oracle Audit Vault and Database Firewall.

Primeiramente é verificado se sua conexão é a nível de database, ou seja, Database-level Firewall Rules. Uma vez que sua conexão seja permitida você terá acesso ao database, caso sua conexão seja negada uma nova verificação é realizada a nível de servidor, ou seja, Server-level Firewall Rules e se agora sua conexão for permitida você terá acesso ao database especifico ou a qualquer outro database no servidor. Por último se seu acesso for negado uma exceção será mostrada e sua conexão irá falhar.

A recomendação é sempre utilizar Database-level Firewall Rules pela simples questão de que quanto mais granular for a sua regra de acesso menos exposto você estará.
Existem várias formas de criar uma regra a nível de servidor, eu vou mostrar algumas, porem existe apenas uma forma (até o momento) de criar uma regra a nível de database que é através de T-SQL.

Server-level Firewall Rules

Vou mostrar 3 formas (existem mais) diferentes: Portal Azure, Powershell e T-SQL para configurar server-level firewall.

  • Portal Azure:

Encontre o servidor que você irá aplicar a regra e procure pela opção Firewall
Acesse a opção e adicione a nova regra! O portal mostra para você qual seu IP e isso facilita a criação da regra para que a sua máquina possa se conectar.

Você deve especificar um IP inicial e um final, ou seja, é possível trabalhar com um range de IP. Caso não queira trabalhar com um range especifique o mesmo IP nos dois campos.

  • Powershell

Para iniciar você deve ter o modulo do AzureRM instalado e importado em sua máquina para que depois você possa fazer login com sua conta e começar a usar os comandos.

Uma vez que você já tenha o modulo pronto para utilizar podemos executar os seguintes comandos:

Get-AzureRmSqlServerFirewallRule Lista todas as regras existem
New-AzureRmSqlServerFirewallRule Cria uma nova regra
Set-AzureRmSqlServerFirewallRule Atualiza uma regra
Remove-AzureRmSqlServerFirewallRule Remove uma regra

Um exemplo de como utilizar cada um desses comando

Para validar se sua regra foi criada com sucesso basta ir no portal na opção de firewall citada acima ou utilizar o comando Get-AzureRmSqlServerFirewallRule

A regra com IP 0.0.0.0 é criada pelo próprio Azure para permitir conexoes de IPs do Windows Azure.

  • T-SQL

Uma vez que você tenha acesso permitido para o seu IP é possível se conectar via SSMS e executar comando T-SQL para criar regras de firewall.

sp_set_firewall_rule Cria ou atualiza a regra
sp_delete_firewall_rule Exclui a regra

Database-level Firewall Rules

Esse nível de permissão é apenas criado através de T-SQL até esse momento. Novamente você pode especificar um unico IP ou um range de IP.

sp_set_database_firewall_rule Cria ou atualiza a regra a nível de database
sp_delete_database_firewall_rule Exclui a regra a nível de database

Para mais informações acessem: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

Categorias
Monitoramento SQL Server Troubleshooting Virtual PASS BR

Monitorar Index Online com extended events

[bing_translator]

Recentemente eu precisei realizar uma movimentação de dados fisicamente, ou seja, retirar dados do arquivo1 e passar esses dados para o arquivo2.

A estratégia adotada foi a recriação do índice clustered em um novo filegroup;

CREATE CLUSTERED INDEX [IX_01] ON [dbo].[TEST]
(
   [ID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [NovoFileGroup](ID)
GO

Esse procedimento é bem simples e já tinha executado muitas vezes. Porem dessa vez tinha algumas variáveis que levei em consideração no meu ambiente de teste:

Primeira: quantidade de registros envolvidos nessa tabela, aproximadamente 2 bilhões.

Segunda: Essa mesma tabela possuía 3 índices non-clustered no qual no ambiente de teste eu pude apagar antes de recriar o meu índice non-clustered.

Realizei todos os meus testes e obtive um tempo de 5 horas para execução do comando. A minha janela de manutenção era de 8 horas. A criação dos outros dois índices non-clustered levaram em torno de 3h e meia conversei com a equipe de negócio e eles não viram problema em invadir por um pouco tempo, ou seja, eu estava tudo certo para executar a minha manutenção.

Antes de iniciar a manutenção em produção, eu decidi não apagar os índices non-clustered (meu erro fatal).
Iniciei a manutenção sem nenhuma preocupação e fiquei acompanhando através de algumas DMV (sys.dm_exec_requests, sys.dm_os_tasks, sys.dm_os_waiting_tasks entre outras) a execução do comando, porém não sei se existe, eu desconheço, uma forma de saber exatamente quanto tempo falta para o comando de CREATE INDEX terminar. No meu acompanhamento eu podia ver o que estava em execução porem muito lento!

Após 10 horas a criação do índice não havia terminado e a operação do dia-a-dia já havia iniciado e nesse momento a preocupação era em não impactar o negócio com possíveis bloqueios que poderiam gerar lentidão no sistema.

Nesse momento os ânimos estavam exaltados na área de negócio e na minha gerencia uma vez que a manutenção não terminou, porem eu estava “super” tranquilo porque não haveria impacto simplesmente pelo fato de eu ter tomado o cuidado e ter utilizado a clausura ONLINE = ON.

Como estávamos usando a versão Enterprise do SQL Server 2012 essa opção é uma das principais funcionalidades que tenho utilizado quando faço manutenção em índices.

A partir desse momento a área de negócio percebeu que eles não estavam sendo afetados pela minha manutenção e que o sistema estava sendo operado normalmente (Essa criação de índice era uma das principais tabelas do sistema)

Com os ânimos mais calmos agora vinham as perguntas: Quando irá terminar? Quanto tempo falta? O que realmente está fazendo? Está travado? Posso realizar rollback? Quanto tempo para o rollback terminar? E muitas outras perguntas referentes ao processo.

Para me auxiliar em relação a algumas dessas perguntas abri um caso na Microsoft e algumas das respostas eu já tinha:

P: Quando irá terminar?

R: Não tem como estimar

P: O que realmente está fazendo?

R: Está em execução realizando a criação do índice lentamente.

P: Posso realizar rollback? Quanto tempo para o rollback terminar?

R: Pode, como está sendo realizado a criação com a clausura ONLINE = ON e DROP_EXISTING = ON o rollback é para ser rápido, mas não é possível precisar o tempo

Basead0s nessas respostas e que não havia impacto para o negócio decidimos deixar a execução rodando e monitorar o que estava fazendo. Mas agora a pergunta era como monitorar!!??

Resolvi tentar achar algum evento via XE (extended events) e existe um REPORT com informações que realmente são valiosas para monitorar o processo.

O que a opção ONLINE = ON faz exatamente? Aqui você entra nos detalhes e sabe o que está acontecendo.

https://msdn.microsoft.com/en-us/library/ms191261(v=sql.110).aspx

Através desse evento podemos monitorar todas as fases que o índice passa:

  • Fase 1: Preparation

  • Fase 2:  Build

  • Fase 3:  Final

Após saber como monitorar passamos a tentar estimar um tempo de finalização, o qual demorou 38 horas. Esse tempo todo  é completamente justificado pelo comportamento da opção ONLINE.

Usar XE é sempre uma boa opção…