Eu tenho um servidor de operações executando o back-end do Windows Server 2012R2 e do SQL Server 2014 Enterprise. Este servidor é usado para implantar o novo código em outros servidores de produção por meio do arquivo cmd chamado por um SQL Agent Job. Todos os servidores estão no mesmo domínio.
O servidor A executa o back-end Windows Server 2008R2 e SQL Server 2008R2. Este servidor possui um servidor vinculado conectando-se a um banco de dados no Servidor B, executando o mesmo sistema operacional e a mesma versão do SQL Server que A. O servidor vinculado é configurado com a opção @useself=TRUE.
- Todos os três servidores usam uma conta de serviço habilitada para delegação.
- Essa conta de serviço tem privilégios sa em todas as três instâncias do SQL Server.
- Todos os três servidores têm SPNs configurados com essa conta e são delegados para usar Kerberos para os serviços MSSQLSVC associados.
Posso executar o seguinte em cada um e "Kerberos" é retornado
SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
Além disso, posso fazer telnet, ping etc. de qualquer um desses servidores para qualquer outro servidor sem problemas - tudo está conectado. As implantações de código nunca tiveram problemas e o servidor vinculado é referenciado com frequência e sem problemas ... exceto por um cenário e não entendo o porquê.
Cenário de salto duplo
- Um trabalho do SQL Agent no servidor de Operações é executado ad hoc por qualquer administrador de sistema que não seja a conta de serviço do SQL Agent e executa um arquivo cmd, também no servidor de Operações.
- O trabalho do SQL Agent é configurado para "Executar como" uma conta de serviço do SQL Agent, com privilégios sa.
- O código implantado vem de um arquivo .sql no servidor de operações.
- O arquivo cmd chama o SQLCMD para executar o código no arquivo .sql no Servidor A.
O código no arquivo .sql faz referência ao servidor vinculado e falha com erro
*Msg 18456, Nível 14, Estado 1, Servidor ServidorB, Linha 1 Login falhou para o usuário 'NT AUTHORITY\ANONYMOUS LOGON'. * .
O erro é direcionado ao servidor vinculado - Servidor B. Se eu executar esse código manualmente, usando o SQLCMD, do servidor de Operações no Servidor A, ele rodará bem. Se eu fizer logon em uma caixa como a conta de serviço do SQL Agent e executar o trabalho do SQL Agent, ele funcionará bem. É somente quando o trabalho do SQL Agent é executado por alguém que não seja a conta de serviço do SQL Agent que recebo o erro.
Eu li tantos posts e blogs e artigos do MSDN sobre Kerberos, double hop, etc, me dizendo para fazer o que já foi feito. o que estou perdendo?
Informações Adicionais Finalmente consegui voltar a isso e localizei algumas informações adicionais. Seguindo o conselho de Bogdan, executei o Process Explorer e verifiquei se as credenciais para o primeiro salto são da conta do SQL Agent Service conforme o esperado e se o TCP está sendo usado. Infelizmente, essas são todas as informações úteis que consegui obter da ferramenta.
Eu vasculhei os logs do aplicativo do Windows e procurei informações de login para as diferentes instâncias nas máquinas e notei que o Kerberos nem está sendo usado !!! Em vez disso, o NTLM está sendo usado.
Portanto, esse é o novo caminho que estou seguindo - por que a autenticação NTLM está sendo usada, quando o Kerberos está configurado e os FQDN SDNs adequados existem por porta e instância? Preciso especificar de alguma forma o tipo de autenticação no arquivo cmd ou na chamada SQLCMD? Ou eu tenho algo mal configurado que não estou pensando?
O mistério se aprofunda A chamada SQLCMD referenciou o servidor "primeiro salto" via Alias. Modifiquei a referência do servidor SQLCMD para a instância nomeada real e executei novamente o trabalho. Funcionou! Também usamos cNames para nossas máquinas e instâncias SQL, então tentei usar isso. Também funcionou! Para sorrisos e gargalhadas, tentei novamente o pseudônimo ... funcionou ??? Eu vou verificar os logs de aplicativos do Windows para cada um deles e ainda está relatando a autenticação como NTLM!
Estou completamente perplexo neste ponto e sem saber como explicar esse comportamento para consertar o resto do nosso ambiente.
Finalmente descobri isso!
Fazemos referência a muitos de nossos SQL Servers e instâncias muito ativos com aliases SQL e nomes canônicos. Isso nos permite realizar atualizações paralelas e alternar entre máquinas sem interrupção do código. Os aliases SQL são todos configurados para fazer referência aos nomes canônicos pelo mesmo motivo. Os SPNs padrão que registramos são para os nomes das máquinas.
EXEMPLO SQL Server SSxyz\xyzInstance escutando na porta 12345 registrou SPNs:
Todos eles são registrados na conta de serviço proprietária do MSSQLSVC nessa máquina e a delegação é definida para que cada um passe as credenciais.
O servidor e a instância também têm o nome canônico cxyz\xyzInstance. Finalmente, o SQL Server tem um alias de 32 bits e 64 bits de xyzAlias, apontando para o nome canônico cxyz\xyzInstance.
Durante um cenário de salto duplo do meu pc local para xyzAlias para abcAlias, a autenticação Kerberos falha e volta para NTLM porque o alias e o SPN não podem ser resolvidos. É por isso que os logs estavam mostrando NTLM.
A correção foi adicionar mais alguns SPNs para registrar o nome canônico do servidor no nome real. Em seguida, o serviço SQL é registrado com outro SPN. assim:
Depois de registrado, cada SPN é configurado para delegação. Eles aparecem como SPNs exclusivos e permitem que o alias SQL seja resolvido para o nome real da máquina. A autenticação de salto duplo registrada é Kerberos e todos estão felizes! Exceto os caras do AD... eles terão muitos SPNs para registrar em algumas horas.
Xcopy Process Explorer em seu servidor de operações.
Modifique o arquivo CMD invocado pelo SQL Server Agent para chamar
timeout 600logo antes da chamada sqlcmd.exe com falha. Isso introduzirá um atraso de 10 minutos antes de o sqlcmd ser realmente chamado.Execute o trabalho, primeiro como você sabe que funciona. Vá para o Process Explorer e inspecione a guia Propriedades para o processo cmd.exe correspondente (será aquele que chama timeout.exe). Faça uma captura de tela da guia Segurança, ela durará mais.
Execute o trabalho como você sabe que falha. Verifique a guia Segurança e compare com o caso anterior.
O contexto de segurança usado pelo sqlcmd.exe é aquele herdado do cmd.exe, que é criado pelo SQLAGENT.EXE. Você deve notar uma diferença entre os contextos de segurança criados nos 2 casos e isso levará à solução.