Este es un caso real, por lo que es posible que algunas fotos o datos estén «censurados».
Problema: Un cliente nos contacta quejándose de que cada vez que se produce un failover en su cluster de alta disponibilidad (Always On AG) uno de sus usuarios pierde los permisos y tienen que volver a otorgárselos para que vuelva a funcionar de nuevo.
Solución: Lo primero de todo es hacerle al cliente las preguntas de rutina. Desde cuando pasa esto? Pasa al hacer failover a todos los nodos o sólo a uno especifico? Ocurre esto con otros usuarios? Ocurre estos en otros cluster de alta disponibilidad o sólo en este? El cliente nos responde a todo que no lo tiene claro y que no nos puede dar mucha más información. Sorpresa para nadie. Salto directamente a revisarlo por mi mismo a ver que puedo encontrar.
Lo primero que reviso es si el usuario tiene activada la opción de que la contraseña expire o algún tipo con característica que pueda hacer que el usuario quede inactivo después de X tiempo. Nada especial, el login parece estar igual en todos los nodos del cluster, hasta que comparé los SIDs de los logins y los usuarios.
Diferencias entre Logins y Usuarios en SQL Server
La diferencia básica es la siguiente: Un login funciona a nivel de instancia y un usuario funciona a nivel de las bases de datos dentro de una instancia.
Cuando nos conectamos a una instancia SQL Server lo hacemos usando un login que tiene permisos sobre la instancia, pero si queremos conectarnos con este login a cualquier base de datos de la instancia, el login tendrá que ser «mapeado» en un usuario dentro de la base de datos en cuestión, este login y ese usuario deben tener el mismo SID (identificación de seguridad).
Cuando el SID del login no coincide con el SID del usuario en la base de datos ocurre lo que se conoce como un usuario «huérfano», esto quiere decir que el usuario de la base de datos no está ligado a ningún login de la instancia por lo tanto queda inutilizado.
Comprobar los SID de los logins y los usuarios
Volvamos al problema que tenemos. Cuando ocurre un failover el usuario de la base de datos deja de funcionar, por lo que alguna diferencia habrá entre los nodos de los cluster, verdad?
Comprobando los SID de lo logins en ambos cluster y comparandolos con el SID del usuario de la base de datos encontramos lo que estaba pasando, los SID no coincidían.
Empezaremos comparando el SID del login y del usuario en la replica principal después de que el cliente otorgase los permisos de nuevo y el usuario esté funcionando correctamente.
USE nombreBaseDeDatos Go Select name, sid from sys.sysuser where name = 'nombreUsuarioBaseDeDatos' Go USE Master Select name, sid from sys.syslogins where name = 'nombreLoginInstancia' Go
Esta query nos entrega el siguiente resultado, primero el SID del usuario a nivel de base de datos y luego el SID del login a nivel de instancia.
Como podéis ver el SID es el mismo, pero ahora revisemos el SID de este login en la instancia que antes era la primaria y ahora es la secundaria.
Usaremos la misma query en la instancia secundaria.
USE Master Select name, sid from sys.syslogins where name = 'nombreLoginInstancia' Go
Y este fue el resultado de la consulta.
Sorpresa sorpresa el SID es distinto, esto quiere decir que cuando ocurrió el failover y la instancia secundaria pasó a ser principal, el SID del usuario y el SID del login no eran el mismo, por lo que el usuario se quedó huérfano y no podía acceder a la instancia provocando el problema del que el cliente se quejaba.
Ahora que conocía el problema simplemente tenia que ponerle una solución, en este caso eliminaré el login de la actual instancia secundaria y lo volveré a crear indicándole que SID debe tener, de esta forma cuando ocurra otro failover, los SID coincidirán.
Crear un login con un SID especifico en SQL Server
Si conocemos la contraseña del login o nuestro cliente puede facilitárnosla simplemente tendremos que crear un login de forma normal añadiendo «SID =» y el numero del SID. En mi caso el cliente no nos podía dar la contraseña y nosotros no la conocemos, por lo que además creamos la contraseña usando el hash de la misma. El código cambia un poco pero básicamente es lo mismo.
Averiguar el hash de la contraseña de un login en SQL Server
SELECT LOGINPROPERTY('nombreLogin', 'PASSWORDHASH')
Esto nos devolverá el hash de la contraseña del login.
Eliminar el login y crearlo con un SID especifico
DROP LOGIN nombreLogin; GO CREATE LOGIN nombreLogin WITH PASSWORD = PASSWORD_HERE HASHED, SID = 0xB3CE8532B33FA94794E93C90186E6C97;
Como veis, estamos eliminando el login en la instancia secundaria y volviéndolo a crear usando el SID que tiene el usuario de la base de datos en la instancia primaria.
Problema solucionado, informamos al cliente sobre este problema y nos pide si podemos hacer una comprobación de todos los logins en los distintos nodos de sus clusters, por lo que sólo tenemos que ejecutar la query en los nodos y compararlos.
Espero que esta información os haya servidor para algo. Recordad que aunque la mayoría de la veces no van a saber responder, es importante hacer las preguntas iniciales a los clientes.
3 comentarios en «Usuario pierde los permisos después de un failover SQL Server»