Saltar al contenido

Diario de un DBA

Problemas y soluciones para SQL Server

Menú
  • Inicio
  • Consultoria gratuita
  • tempdb
  • Curso Azure DP-300
  • Rendimiento
  • Seguridad
  • Sobre Mi
Menú

Usuario pierde los permisos después de un failover SQL Server

Publicada el julio 16, 2022abril 11, 2024 por solracamo

Contenido del post:

Toggle
  • Diferencias entre Logins y Usuarios en SQL Server
  • Comprobar los SID de los logins y los usuarios
  • Crear un login con un SID especifico en SQL Server
    • Averiguar el hash de la contraseña de un login en SQL Server
    • Eliminar el login y crearlo con un SID especifico

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»

  1. Pingback: 💻SQL Server - Migrar logins entre instancias SQL Server
  2. Pingback: Migrar logins entre instancias con DBAtools en SQL Server - Diario de un DBA
  3. Pingback: Always On en SQL Server. Todo lo que necesitas saber - Diario de un DBA

Deja una respuesta Cancelar la respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Consultoria Gratis

¿Tienes un problema o una consulta sobre tu servidor SQL Server? ¿Necesitas hacer algún tipo de mantenimiento o tarea en tu instancia, pero no tienes claro cómo hacerlo?

Envíame un email con tu caso y te contestaré lo antes posible de forma totalmente gratuita.

Puedes utilizar el formulario que encontrarás haciendo click aquí.

Sobre mi

Efectivamente, soy un muñeco de slack con la camiseta de Fernando Alonso en Renault, pero también soy una DBA mid level que vive y trabaja en inglaterra como consultor SQL Server.

El objetivo de este blog es escribir los problemas con los que me he ido encontrando y cómo los solucioné. Si estas soluciones han servido para mí, es posible que también lo hagan para ti, ¿no?

Otras soluciones

  • Recuperar la password de la cuenta SA en SQL Serveroctubre 5, 2024
  • Always On en SQL Server. Todo lo que necesitas saberagosto 24, 2024
  • Recuperar una tabla borrada en SQL Serveragosto 18, 2024
  • Mover bases de datos a otro disco en SQL Serveragosto 11, 2024
  • Comprobar los últimos backups tomados en una instancia SQL Serverjunio 29, 2024
  • Comprobar fragmentación en SQL Serverjunio 23, 2024

Curso Azure DP-300 Gratis

  • Azure DP-300 curso en Español – Parte 1noviembre 17, 2022
  • Crear una máquina virtual con SQL server en Azurenoviembre 17, 2022
  • Crear un servidor SQL Server en Azure.noviembre 22, 2022
  • Azure DP-300 curso en Español – Parte 2noviembre 24, 2022
  • Azure DP-300 curso en Español – Parte 3diciembre 1, 2022
  • Azure DP-300 curso en Español – Parte 4diciembre 14, 2022

Consulta las páginas que nadie consulta:
Privacidad
Cookies

Escríbeme!
Contacto

© 2025 Diario de un DBA