¡Hola, chicos!
En este artículo me gustaría documentar y compartir una experiencia que tuve HOY, en la consultora donde trabajo, en la que tuvimos un problema con un cliente que hacía que todos los Servidores Vinculados que apuntaban a una determinada instancia comenzaran a mostrar el siguiente error, tanto al intentar consultar datos como al intentar cambiar objetos (como Procedimientos Almacenados) que utilizaban este servidor vinculado. Y con gran apoyo de Rodrigo Ribeiro Gomes, logramos resolver este problema.

Mensaje 18456, Nivel 14, Estado 1, Línea 4
Error de inicio de sesión para el usuario "NT AUTHORITY\ANONYMOUS LOGON".

Mi primera sospecha cuando encontré este mensaje fue obviamente que había algún problema con Kerberos Double Hop, que es un término utilizado para describir nuestro método de mantener las credenciales de autenticación Kerberos del cliente en dos o más conexiones. De esta forma, podemos mantener las credenciales del usuario y actuar en nombre del usuario en otras conexiones a otros servidores. como ocurre cuando usamos un Servidor Vinculado configurado para usar el contexto de seguridad del usuario que inició sesión (“Se realizará usando el contexto de seguridad actual del inicio de sesión”) y estamos usando una conexión con autenticación AD para usar este Servidor Vinculado.

De forma predeterminada, SQL Server siempre intentará utilizar el modo de autenticación Kerberos cuando utilice una cuenta con autenticación AD. Si Kerberos no está disponible, intentará utilizar el modo de autenticación NTLM (generalmente utilizado en sistemas independientes).

Usando una consulta simple, pudimos verificar la cantidad de conexiones en cada modo de autenticación:

SELECT auth_scheme, COUNT(*)
FROM sys.dm_exec_connections
GROUP BY auth_scheme

Ejemplo:

Como ocurrió durante el análisis, no hubo conexión usando autenticación Kerberos, solo SQL (cuando usa inicios de sesión de SQL Server para conectarse a SQL Server) y NTLM (conexión usando autenticación de Windows cuando Kerberos no está disponible). Esta es una fuerte indicación de que Kerberos no está funcionando correctamente.

Activé el registro de Kerberos para intentar identificar cualquier problema. Para hacer esto, creé la clave de registro LogLevel (DWORD) con el valor = 1 en la dirección HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters.

Después de habilitar el registro de Kerberos, analicé los registros en el Visor de eventos e identifiqué el siguiente error de Kerberos:

Código de error: 0x19 KDC_ERR_PRAUTH_REQUIRED

Cuando consultamos Internet, podemos entender que este error es “normal” para Kerberos y puede ignorarse. Por lo tanto, analicé los servidores registrados en el SPN de la cuenta de servicio de SQL Server Database Engine, que es un usuario de AD en el formato “DOMINIO\Usuario”:

Listado de los SPN registrados para la cuenta AD que ejecuta el servicio SQL Server

setspn -L DOMINIO\usuario

Como esta instancia es parte de un clúster de Windows de 2 nodos y el problema comenzó después de una conmutación por error, ejecuté los resultados de setspn -L en ambos nodos y comparé los resultados usando Notepad++, lo que mostró que los registros eran idénticos en ambos nodos.

El siguiente paso fue analizar los registros de SQL Server con el xp_readerrorlog, para verificar si el SPN se registró normalmente:

xp_readerrorlog 0,1,N'spn'

Resultado:

Si el registro no se realiza correctamente, verá un mensaje como este en el registro de SQL Server:

Según el registro, SQL Server registró correctamente el SPN de la instancia, pero por alguna razón, ya no aparece cuando uso el comando setspn -L que demostré anteriormente. Probablemente sean estos registros faltantes los que estén causando el error en esta publicación.

Por lo tanto, registraré manualmente los registros SPN para esta instancia de la misma manera que se registraron inicialmente:

setspn -A MSSQLSvc/INSTANCIA.dominio.local:porta DOMINIO\USUARIO
setspn -A MSSQLSvc/INSTANCIA.dominio.local:INSTANCIA DOMINIO\USUARIO

Después de registrar manualmente estas entradas, los servidores vinculados que apuntan a esta instancia volvieron a funcionar normalmente y se corrigió el error. Ejecuté una consulta nuevamente en DMV sys.dm_exec_connections y ya se están realizando nuevas conexiones de usuario usando autenticación de Windows (AD) usando Kerberos en lugar de NTLM (las conexiones existentes deben cerrarse para que cuando se conecten nuevamente, comiencen a usar Kerberos).

Resultado:

Una vez más, me gustaría agradecer el apoyo y la orientación de Rodrigo Ribeiro Gomes, que fueron fundamentales para resolver este problema hoy.

Espero que este artículo le ayude a resolver este problema si se enfrenta a esta situación. Los errores en Kerberos suelen requerir algo de trabajo para identificarlos y corregirlos, por lo que la solución no siempre será exactamente esa. Como me comentó el propio Rodrigo, hay casos en los que incluso cuando los registros SPN son correctos se pueden producir errores en Kerberos, siendo necesario analizar los paquetes de Kerberos mediante herramientas como Wireshark.

Referencias:
– https://serverfault.com/questions/808198/how-to-enable-logging-for-kerberos-on-windows-2012-r21
– https://blogs.msdn.microsoft.com/sql_protocols/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections/
– https://blogs.technet.microsoft.com/askds/2008/06/13/understanding-kerberos-double-hop/
– https://comunidadesqlserver.wordpress.com/2017/06/22/troubleshooting-kerberos-configuration/
– https://community.microstrategy.com/s/article/KB34369-How-to-use-Wireshark-to-troubleshoot-Kerberos-Issues
– https://docs.microsoft.com/en-us/windows/desktop/secauthn/microsoft-ntlm
– https://blogs.technet.microsoft.com/askds/2008/05/29/kerberos-authentication-problems-service-principal-name-spn-issues-part-1/

¡Eso es todo, amigos!
Espero que hayas disfrutado de este artículo y ¡hasta luego!