Certificado expirado vinculado ao Endpoint causa falha na sincronização do Always On
Neste post gostaria de compartilhar com a comunidade uma situação no qual envolve uma configuração do Always On High Availability em um ambiente sem Active Directory Domain.
Porém quando o ambiente possui esta necessidade de utilização do Always On sem dominio é necessário a utilização de certificados para a segurança do ponto de extremidade (endpoint). Neste caso é necessário a criação de certificado e vinculado ao Endpoint para que haja a comunicação entre os nodes envolvidos no Always On.
E neste artigo irei demonstrar um erro no Always On no qual parou de sincronizar devido a validade do certificado utilizado no Endpoint.
Para maiores detalhes da configuração acesse o site da Microsoft : Criar um grupo de disponibilidade independente de domínio.
Temos um ambiente composto por 2 host sendo eles “SQLNODE1 e SQLNODE2”, no qual fazem parte do grupo de disponibilidade AGPROD, porém a sincronização foi interrompida pelo erro de certificado vencido.
Podemos observar que o node “SQLNODE2(Secondary)” está com “Not Synchronizing” , no qual significa que está desconectado.
Ao clicar em “Issues : Critical(1), Warnings(2)” podemos observar a informação de “Availabity replica is disconnected”.
Troubleshooting
No Errolog podemos visualizar a mensagem de erro “Database Mirroring login attempt failed with error: ‘Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.'”
No qual é informado sobre o certificado expirado.
Para melhor visualização clique sobre a imagem.
Também é possível a visualização do erro no Event viewer.
Para melhor visualização clique sobre a imagem.
Solução do problema
Para solucionar o problema é necessário a criação de um novo certificado em cada node que faz parte do Always On, neste exemplo temos os nodes “SQLNODE1 e SQLNODE2”.
Como o ambiente já estava configurado o AlwaysOn podemos considerar que a Master Key já está criada corretamente.
Vamos iniciar pelo o node primário “SQLNODE1” , com a query abaixo podemos obter qual é o certificado que está sendo utilizado pelo o endpoint.
SELECT e.name AS 'endpoint'
,c.name AS 'certificado'
,c.expiry_date AS 'data expiração'
FROM sys.database_mirroring_endpoints AS e
JOIN sys.certificates AS c ON c.certificate_id = e.certificate_idResultado obtido pela a query.
Para criar um novo certificado no host “SQLNODE1” iremos utilizar o script a seguir.
No script informei o parâmetro “expiry_date” com a data de expiração para o dia 31/12/2024 , caso você não informe este parâmetro o SQL Server irá criar um certificado com a validade de 1 ano. No exemplo, o certificado criado será com a validade de 2 anos.
CREATE CERTIFICATE [CERTIFICADO-NODE1-v2]
WITH SUBJECT = 'CERTIFICADO-NODE1-v2' , EXPIRY_DATE ='20241231';Para maiores detalhes sobre a criação de certificados acesso o site da microsoft : Create Certificate (Transact-SQL)
Após a criação vamos visualizar com a query abaixo , as informações do certificado.
select name,
pvt_key_encryption_type,
pvt_key_encryption_type_desc,
start_date,
expiry_date
from sys.certificates
where name = 'CERTIFICADO-NODE1-v2'Podemos visualizar a data de criação (start_date) e a data de expiração (expiry_date).
O próximo passo é efetuar o backup do certificado “CERTIFICADO-NODE1-v2” , para que seja utilizado no node SQLNODE2.
No script abaixo é necessário informar o nome do certificado e o diretório no qual será criado um arquivo de extensão “.cer”
BACKUP CERTIFICATE [CERTIFICADO-NODE1-v2]
TO FILE ='F:\CERTIFICADO-NODE1-v2.cer';
Visualizando o arquivo criado no diretório F:\, este arquivo deverá ser copiado para um diretório no SQLNODE2 no qual iremos efetuar a criação do certificado “CERTIFICADO-NODE1-v2” no node SQLNODE2.
Vamos efetuar os passos anteriores no node SQLNODE2, é necessário que seja criado um novo certificado no qual será vinculado ao endpoint neste node.
Como o ambiente já estava configurado o AlwaysOn podemos considerar que a Master Key já está criada corretamente.
Com a query abaixo podemos obter qual é o certificado que está sendo utilizado pelo o endpoint.
SELECT e.name AS 'endpoint'
,c.name AS 'certificado'
,c.expiry_date AS 'data expiração'
FROM sys.database_mirroring_endpoints AS e
JOIN sys.certificates AS c ON c.certificate_id = e.certificate_idResultado obtido pela a query.
O próximo passo é efetuar o backup do certificado “CERTIFICADO-NODE2-v2” , para que seja utilizado no node SQLNODE1.
No script abaixo é necessário informar o nome do certificado e o diretório no qual será criado um arquivo de extensão “.cer”
BACKUP CERTIFICATE [CERTIFICADO-NODE2-v2]
TO FILE ='F:\CERTIFICADO-NODE2-v2.cer';Visualizando o arquivo criado no diretório F:\, este arquivo deverá ser copiado para um diretório no SQLNODE1 no qual iremos efetuar a criação do certificado “CERTIFICADO-NODE2-v2” no node SQLNODE1.
Após a criação dos certificados em cada node “SQLNODE1 e SQLNODE2”, o próximo passo é copiar backup do certificado de cada node entre eles para que seja efetuado a criação utilizando o arquivo.
Vamos iniciar copiando o arquivo “CERTIFICADO-NODE1-v2.cer” localizado no SQLNODE1 para um diretório no SQLNODE2.
O arquivo foi copiado para o diretório F:\ conforme print abaixo.
Após a cópia do arquivo vamos executar o script abaixo , para criar o certificado “CERTIFICADO-NODE1-v2” no node SQLNODE2.
Podemos dizer que vamos restaurar o certificado do host SQLNODE1 no host SQLNODE2.
No comando de “create certificate” é necessário informar um usuário que terá permissão de utilização do certificado, este usuário também irá possuir a permissão de connect on endpoint.
CREATE CERTIFICATE [CERTIFICADO-NODE1-v2] AUTHORIZATION Node2
FROM FILE = 'F:\CERTIFICADO-NODE1-v2.cer'
Após a restauração do certificado no SQLNODE2 podemos visualizar o certificado do SQLNODE1 com a query abaixo.
select name,
pvt_key_encryption_type,
pvt_key_encryption_type_desc,
start_date,
expiry_date
from sys.certificates
where name = 'CERTIFICADO-NODE1-v2'
No SQLNODE1 iremos copiar o arquivo do SQLNODE2.
O arquivo foi copiado para o diretório F:\ conforme print abaixo.
Após a cópia do arquivo vamos executar o script abaixo , para criar o certificado “CERTIFICADO-NODE2-v2” no node SQLNODE1.
Podemos dizer que vamos restaurar o certificado do host SQLNODE2 no host SQLNODE1.
No comando de “create certificate” é necessário informar um usuário que terá permissão de utilização do certificado, este usuário também irá possuir a permissão de connect on endpoint.
CREATE CERTIFICATE [CERTIFICADO-NODE2-v2] AUTHORIZATION node1
FROM FILE = 'F:\CERTIFICADO-NODE2-v2.cer'
Após a restauração do certificado no SQLNODE1 podemos visualizar o certificado do SQLNODE2 com a query abaixo.
select name,
pvt_key_encryption_type,
pvt_key_encryption_type_desc,
start_date,
expiry_date
from sys.certificates
where name = 'CERTIFICADO-NODE2-v2'
Após a restauração a próxima etapa é efetuar a alteração do certificado utilizado pelo o ENDPOINT.
No host SQLNODE1 vamos utilizar o certificado “CERTIFICADO-NODE1-v2” , com o comando abaixo é possível efetuar a alteração do certificado.
ALTER ENDPOINT [Hadr_endpoint] FOR DATA_MIRRORING
(ROLE = ALL, AUTHENTICATION = CERTIFICATE [CERTIFICADO-NODE1-v2] , ENCRYPTION = REQUIRED ALGORITHM AES)
Vamos verificar qual é o certificado que o endpoint do host SQLNODE1 irá utilizar?
Com a query abaixo podemos visualizar qual é o certificado utilizado no endpoint.
SELECT e.name AS 'endpoint'
,c.name AS 'certificado'
,c.expiry_date AS 'data expiração'
FROM sys.database_mirroring_endpoints AS e
JOIN sys.certificates AS c ON c.certificate_id = e.certificate_id
No host SQLNODE2 vamos utilizar o certificado “CERTIFICADO-NODE1-v2” , com o comando abaixo é possível efetuar a alteração do certificado.
ALTER ENDPOINT [Hadr_endpoint] FOR DATA_MIRRORING
(ROLE = ALL, AUTHENTICATION = CERTIFICATE [CERTIFICADO-NODE2-v2] , ENCRYPTION = REQUIRED ALGORITHM AES)
Vamos verificar qual é o certificado que o endpoint do host SQLNODE2 irá utilizar?
Com a query abaixo podemos visualizar qual é o certificado utilizado no endpoint.
SELECT e.name AS 'endpoint'
,c.name AS 'certificado'
,c.expiry_date AS 'data expiração'
FROM sys.database_mirroring_endpoints AS e
JOIN sys.certificates AS c ON c.certificate_id = e.certificate_id
E para finalizar temos os grant abaixo a serem aplicados.
Host SQLNODE1 foram aplicados os grants para o usuário “node1”.
USE master
go
GRANT CONNECT TO ENDPOINT::[Hadr_endpoint] TO node1
go
GRANT CONNECT SQL TO node1
go
GRANT CONNECT TO node1
go
Para o SQLNODE2 foram aplicados os grants para o usuário “node2”.
USE master
go
GRANT CONNECT TO ENDPOINT::[Hadr_endpoint] TO Node2
go
GRANT CONNECT SQL TO Node2
go
GRANT CONNECT TO Node2
go
E para finalizar o post podemos visualizar que após o procedimento podemos visualizar o dashboard com o Availability group state = Healthy e também a replica “SQLNODE2” conectada no availability groups.
Pessoal, esta foi a solução implementada espero que se vocês passarem por esta situação este artigo possa lhe auxiliar.
Uai bora tomar aquele cafezinho com pão de queijo!!!
Um abraço!
