Kerberos, SPN's duplicados e o Kerberos Configuration Manager.


Olá pessoal! 
 
Recentemente passei por um problema com o Kerberos em produção, e que tinha relação com o registro de SPN’s (Service Principal Name) duplicados para uma instância do SQL Server. Para detectar e resolver o problema, usei uma ferramenta bem interessante chamada Kerberos Configuration Manager for SQL Server:

Um SPN nada mais é do que o registro que o AD usa para identificar determinada instância de um serviço que está rodando em um host, ele basicamente é composto do nome deste host mais a conta utilizada para manter este serviço no ar. A autenticação de um login integrado via Kerberos depende deste registro, para quem quiser ir mais a fundo neste assunto recomendo os artigos do Adam W. Saxton que é um especialista da área, em especial recomendo um checklist sobre autenticação Kerberos feito por ele: http://blogs.msdn.com/b/psssql/archive/2010/06/23/my-kerberos-checklist.aspx.

Para a autenticação de um login integrado funcionar com o Kerberos no SQL Server, são necessários dois SPNs, assumindo que está sendo utilizada uma instancia nomeada do SQL Server e com uma porta fixa, teríamos algo seguindo o modelo abaixo:

MSSQLSvc/nomedohost.nomedodominio:NOMEDAINSTANCIADOSQLSERVER                                             
MSSQLSvc/nomedohost.nomedodominio:PORTADOSQLSERVER

Simulando o problema: SPN duplicado

Irei simular o problema que tive onde um dos registros SPN do SQL Server estava duplicado. Vamos primeiro entender meu cenário. Tenho uma instância do SQL Server 2014 chamada MSSQL2014 rodando sob a conta conta_sql2 que está no domínio DOMAINWIN2008 instalada em um Windows Server 2008 R2 SP1, conforme abaixo:


Por padrão, uma instância do SQL Server 2008 ou superior tenta registrar seus SPNs automaticamente quando seu serviço está sendo iniciado ou finalizado, contudo, na maioria dos ambientes a conta que mantem a instância no ar não tem as permissões necessárias no domínio para fazer este registro e no ERRORLOG você acaba vendo a seguinte mensagem:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the
SQL Server service.

Desta forma, os registros SPN acabam sendo feitos manualmente por um administrador do domínio. Inclusive, em um ambiente cluster com versões inferiores ao SQL Server 2012, a recomendação é evitar que o registro dos SPN’s sejam controlados de forma automática pelo SQL Server (http://support.microsoft.com/kb/319723). Em meu caso por exemplo, os seguintes comandos foram usados por um administrador do domínio para registrar os SPN’s da minha instância:

Setspn –s “MSSQLSvc/win2k8sql14.DomainWin2008.net:MSSQL2014” “domainwin2008\conta_sql2”
Setspn –s “MSSQLSvc/win2k8sql14.DomainWin2008.net:1433” “domainwin2008\conta_sql2”

Para verificar que os SPNs estão corretamente registrados, basta rodar o comando abaixo em um prompt de comando elevado:

Setspn –l “domainwin2008\conta_sql2”

 
Sempre é possível que numa troca de conta de serviço, algum SPN seja deixado para trás, e foi o que aconteceu no meu caso. Vou simular isto usando o SETSPN para adicionar um SPN para a mesma instância do SQL Server, mas usando outra conta de serviço. Isto é um SPN duplicado:

Setspn –a “MSSQLSvc/win2k8sql14.DomainWin2008.net:MSSQL2014” “domainwin2008\conta_sql”

 
Note que estou usando o SETSPN embarcado com o Windows Server 2008 R2 SP1. Nesta versão o parâmetro –a adiciona um SPN sem verificar a existência de duplicatas. Nesta versão do SETSPN, o recomendado seria usar o parâmetro –s para verificar a duplicidade e fazer a adição do SPN. Também é possível usar o SETSPN com o parâmetro –x para procurar SPNs duplicados em todo o diretório (http://blogs.msdn.com/b/psssql/archive/2009/02/13/searching-for-duplicate-spn-s-got-a-little-easier.aspx), mas isto pode levar bastante tempo em ambientes muito grandes. No Windows Server 2012, o SETSPN com –a já faz a verificação de duplicidade na adição do SPN, o que teria evitado o problema neste caso, ( http://blogs.msdn.com/b/psssql/archive/2012/08/17/setspn-a-with-windows-2012-does-a-duplicate-check-upfront.aspx). Exemplificando, depois de adicionar o SPN duplicado, se rodar o SETSPN com o parâmetro –x recebo a seguinte saída:

Setspn –x

 
Como saber se existem SPNs duplicados, ou outro problema que impeça o uso do Kerberos para autenticação dos logins integrados, para uma determinada instância do SQL Server, sem ter que varrer todo o diretório ou fazer verificações manuais e extensivas de configuração? É aí que entra o Kerberos Configuration Manager for SQL Server (KCM).

O KCM pode ser instalado em qualquer servidor ou estação que tenha o .NET Framework 4.0 instalado, não é necessário instalar a ferramenta diretamente nos servidores que suportam as instâncias do SQL Server. Executo o KCM e vejo a tela a seguir:

 
Clico em “Connect” e forneço o nome da instância do SQL Server que desejo validar, WIN2K8SQL14 no meu caso. Não forneço um login do SQL Server, e portanto o KCM vai usar autenticação integrada:


Com o KCM conectado na instância, clico na aba “SPN”, e na coluna “Status” verifico que existem problemas. No meu caso, o SPN está duplicado:

 
Clicando em “Fix”, o KCM pode corrigir o problema para mim, se eu estiver logado com uma conta que possua os privilégios necessários. Se eu não tenho privilégios, o KCM pode gerar os comandos necessários para fazer a correção, basta clicar em “Generate”. Clico em “Generate”, salvo o arquivo em local adequado. Ao verificar o arquivo de comando gerado pelo KCM, encontro o comando abaixo. Note que o parâmetro –d indica a deleção do SPN.

Setspn –d “MSSQLSvc/win2k8sql14.DomainWin2008.net:MSSQL2014” “DomainWin2008\conta_sql”

Após a execução do arquivo de comando gerado pelo KCM, basta clicar em “Refresh” na barra de menu para conferir novamente o status:

 
Basicamente é isso pessoal! Espero que o post tenha sido útil e ajude vocês em problemas deste tipo. Um abraço!

Recuperando a base de sistema tempdb após um crash de disco!

Olá pessoal tudo certo? Espero que sim! Hoje irei simular a perda de um disco com os arquivos da base de sistema tempdb em uma instalação do SQL Server stand-alone, após o crash da instância, irei mostrar como você pode contornar a situação! Vamos lá!

Simulando o problema...

Tenho dois discos, o disco T: é exclusivo da TEMPDB.


Rodando o comando sp_helpfile no banco de dados TEMPDB, veja que os dois arquivos estão no disco T:


Parei o serviço da instância do SQL Server via Configuration Manager, por estar em um SQL Server stand-alone também seria possível fazê-lo através de outros mecanismos como por exemplo usando o NET STOP.:


Depois fui até o Disk Management, cliquei com o botão direito sobre o disco T:, selecionei “Delete Volume” e confirmei a operação:
 


Pronto, o disco T: está fora da jogada e os arquivos do TEMPDB também:


De volta ao Configuration Manager, tentei iniciar a instância do SQL Server:
 

Não funcionou, fui até o diretório de log da instância, e abri o ERRORLOG:


No ERRORLOG visualizei os seguintes erros no final do arquivo:

2014-05-30 13:26:22.24 spid10s     Clearing tempdb database.
2014-05-30 13:26:22.24 spid10s     Error: 5123, Severity: 16, State: 1.
2014-05-30 13:26:22.24 spid10s     CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'T:\Data\tempdb.mdf'.
2014-05-30 13:26:22.29 spid10s     Error: 17204, Severity: 16, State: 1.
2014-05-30 13:26:22.29 spid10s     FCB::Open failed: Could not open file T:\Data\tempdb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2014-05-30 13:26:22.33 spid10s     Error: 5120, Severity: 16, State: 101.
2014-05-30 13:26:22.33 spid10s     Unable to open the physical file "T:\Data\tempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2014-05-30 13:26:22.45 spid10s     Error: 1802, Severity: 16, State: 4.
2014-05-30 13:26:22.45 spid10s     CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2014-05-30 13:26:22.45 spid10s     Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2014-05-30 13:26:22.46 spid10s     SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Resolvendo o problema...

Para superarmos este problema, temos que subir o SQL Server usando o parâmetro /f e o traceflag 3608, os mesmos usados no post: (http://sqlmagu.blogspot.com.br/2014/01/movimentacao-incorreta-da.html). O parâmetro -f, indica que quero subir o SQL Server com uma configuração mínima, montando apenas o Resource Database e o master. E o trace flag 3608 serve para não fazer o recovery dos bancos de dados de usuário. Uma vez que meu SQL Server é uma instalação stand alone, irei usar o NET START num prompt de comando elevado. E não se esqueça de trocar o texto em vermelho pelo nome da sua instância do SQL Server:

NET START MSSQL$SQLTEST /f /t3608

Se tudo estiver correto vocês verão a mensagem abaixo:


Notem que na snap-in de Services, o serviço da instância do SQL Server está iniciado:


Como estamos no modo mínimo, apenas um sysadmin poderá se logar por vez no SQL Server, então, vou me conectar ao SQL Server via SQLCMD rodando o comando abaixo:

SQLCMD –S .\SQLTEST –E
  
O –S server para indicar o nome da instância, enquanto que o –E indica uma conexão com login integrado, ou seja, o usuário que está logado no Windows deve possuir privilégios para acessar o SQL Server. Se o comando der certo, estaremos dentro do SQL Server:


Agora basta executarmos um ALTER DATABASE e apontar os arquivos da TEMPDB para um caminho novo em outro disco. Para saber qual nome dos arquivos da TEMPDB que foram perdidos junto com o disco T:,  rode o comando abaixo:

SELECT * FROM SYSALTFILES WHERE DBID = 2
GO

Você verá algo como a seguir:


Agora vou rodar os comandos abaixo para apontar novos caminhos para estes arquivos: 

ALTER DATABASE TEMPDB MODIFY FILE (NAME=’TEMPDEV’, FILENAME=’C:\DATA\TEMPD.MDF’)
GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME=’TEMPLOG’, FILENAME=’C:\LOG\TEMPLOG.LDF’)
GO 

Agora, saia do SQLCMD pressionando CTRL + C, pare o serviço do SQL Server e inicie-o normalmente. Se o serviço iniciar sem problemas, vá até o Management Studio, conecte-se na instância e dispare os comandos abaixo:

USE TEMPDB
GO
SP_HELPFILE 

Pronto! É possível ver que os arquivos estão localizados no novo caminho dentro da unidade "C":
 

Espero que tenham gostado do post! Um abraço!