Blog Archive

SQL-Server

 
Eladio Rincón

Database Mirroring, inicios de sesión de SQL, y el procedimiento almacenado sp_change_users_login

2008-04-25 19:30:00 por Eladio Rincón

Introducción

En instalaciones que implementéis database mirroring, debéis realizar tareas de administración para cuestiones que se salen del ámbito de la base de datos; por ejemplo: ¿qué haces con los planes de mantenimiento como reindexaciones, copias de seguridad, etc.? Sabes que todo esto está fuera de la base de datos, concretamente en MSDB, por lo que si modificas el plan de mantenimiento de una BD en Mirroring, tendrás que encargarte de transferir ese plan al servidor mirror, y "prepararlo" para que comience a operar cuando suceda el failover.

¿Qué pasa con los inicios de sesión? Como bien sabes, la información del inicio de sesión, va almacenada en la base de datos master, y de esta base de datos, no se transfiere información al servidor mirror. También debes sabes, que la base de datos en mirroring, tiene almacenado "internamente" el usuario de base de datos, que a su vez está relacionado con el inicio de sesión a través de una clave ajena no declarada J esta clave ajena es la columna SID.

El problema

Bajo esta premisa anterior, ¿qué pasaría si a una base de datos que forma parte de una sesión de database mirroring, se le añade un usuario que tiene asociado un inicio de sesión SQL? Por el razonamiento anterior, el paso que habríamos dado sería:

  • Crear inicio de sesión con su contraseña.
  • Crear usuario de base de datos y asignar permisos sobre objetos (GRANTs, DENYs, etc.etc.)

¿Qué sucedería por debajo? La sesión de database mirroring, enviaría a la base de datos del partner, el usuario de base de datos recién creado. Sin embargo, la sesión de mirroring no envía el inicio de sesión. Ahí es el momento en el debes entrar tu como DBA. Te vas al servidor que está como mirror, y allí debes crear el inicio de sesión – que recuerda va almacenado en master. Pero claro, deberás crearlo de forma que el SID que hemos comentado antes, esté en concordancia con el que va a estar en master.

La solución

Para ello en SQL Server 2005, tienes la opción de especificar un SID específico durante la creación del inicio de sesión; un ejemplo sería el siguiente:

CREATE LOGIN usuario WITH
PASSWORD
= 'usuario', CHECK_POLICY = OFF,
SID = 0xA8C8CD1094A7964897C0E38ADBB3233E

Fíjate que el pedazo GUID ese, deberá ser el mismo que tienes en master del servidor principal; ¿de qué forma puedes consultar esa información? Con la siguiente consulta:

select name, sid
from
sys.server_principals
where
name = 'usuario'

También hay un artículo de KB que te puede servir qué básicamente que genera el script de creación de todos los inicios de sesión de una instancia de SQL Server 2005; los inicios de sesión que te interesen los ejecutas en el servidor de destino, y listo:
How to transfer the logins and the passwords between instances of SQL Server 2005 (http://support.microsoft.com/kb/918992).

Lo que debes evitar

Lo que no deberías hacer es usar el procedimiento almacenado sp_change_users_login, que hace precisamente lo contrario: recuerda que con el create login, lo que hacemos es crear un inicio de sesión con un SID específico; lo que hace el procedimiento que acabo de comentar, "arregla" el SID de la base de datos y lo pone en concordancia al SID a nivel de inicio de sesión, así que imagínate el siguiente escenario (que es el origen de esta entrada de blog):

  • Creas inicio de sesión en SRV1 (principal), para usuario XX1, y se le asigna el SID = 0xAA3.
  • Creas el usuario de base de datos XX1, donde se relaciona el usuario con el inicio de sesión por el SID = 0xAA3.
    • SQL Server por debajo envía el usuario de base de datos al otro servidor.
  • Creas inicio de sesión en SRV2 (mirror), para el usuario XX1, y se le asigna (SQL automáticamente lo hace porque no lo especificamos) el SID = 0xAA4.
  • La relación entre el usuario de base de datos, y el inicio de sesión no cuadra porque 0xAA3 != 0xAA4
  • Hasta ahora no ha pasado nada, porque la actividad la gestiona el servidor principal; por la razón que sea se provoca un failover, y la gestión transaccional la toma el servidor mirror; entonces detectas lo del punto anterior, que el usuario XX1, no tiene permiso de acceso sobre la base de datos, cuando en realidad sabes que existe el inicio de sesión, y existe el usuario de base de datos.
  • Detectas el error, y lo arreglas con el procedimiento sp_change_users_login. Lo que hace este procedimiento es cambiar 0xAA3 que está en la base de datos, a 0xAA4 que es el que pertenece al inicio de sesión.
  • Perfecto, todo funciona perfectamente, y los usuarios pueden acceder a la base de datos.
  • ¿qué pasaría ahora si salta otro failover? Volvería a SRV1, y estaríamos con el problema anterior: la BD tiene el SID = 0xAA4 mientras que el inicio de sesión tiene 0xAA3.
  • ¿Vuelta otra vez a ejecutar sp_change_users_login? Nooo, lo que deberíamos haber hecho es crear el inicio de sesión de forma adecuada con el SID que tiene la base de datos J

Notas finales

Esto no sucede con inicios de sesión de Windows porque el SID va asociado a las credenciales de Windows. ¿Una razón más para usar autenticación integrada? Creo que si J

Antiguamente, para Log Shipping, en SQL Server 2000, había un procedimiento almacenado llamado sp_resolve_logins (http://msdn2.microsoft.com/en-us/library/aa238877(SQL.80).aspx), que hacía lo que su nombre indica: "arreglar" logins. Lo hacía de una forma peculiar, necesitabas un BCP de la tabla syslogins de la instancia de origen, y luego el procedimiento se recorría cada login, y lo arreglaba. Por cierto, si usabas el código original en SQL Server 2000, deberías actualizar el script (si lo usas) porque fallaba (http://support.microsoft.com/kb/310882). Si te fijas en el código, utiliza el procedimiento sp_change_users_login para arreglarlo… menos mal que Log Shipping no devuelve credenciales al servidor de origen, porque tendríamos lío otra vez J

Corolario

Utiliza CREATE LOGIN WITH SID, cuando quieras transferir inicios de sesión de SQL Server entre distintas instancia de SQL Server, y quieras mantener el mismo SID.
No utilices el procedimiento sp_change_users_login, que por cada failover tendrás que estar tirando de él.

Comments

#Mmmm...Super Interesante...
Wednesday, June 18, 2008 - 04:09 PM by Anonymous
Particularmente yo desconocía de la opción WITH SID. Es muy común utilizar el sp_change_users_login, sobre todo cuando transferimos bases de datos a ambientes de desarrollo ó QA, simplifica bastante la recreación de las cuentas-SQL. Un caso práctico es cuando por ejemplo tenemos que pasar una DB de producción a un ambiente de desarrollo: al pasarla una primera vez, y recrear las cuentas con sp_change_users_login, tendríamos resuelto el problema de las cuentas. Ahora supongamos que el desarrollador nos pide restaurar nuevamente el mismo respaldo, debido a que tomamos el LOGIN-ID y lo asignamos al USUARIO-ID, entonces la 2da. vez que retornemos, tendremos que ejecutar nuevamente el sp_change_users_login porque los SIDs se habran "desincronizado" nuevamente; sin embargo utilizando desde la primera vez el CREATE LOGIN WITH SID..., que toma el USER-ID y se lo asigna al LOGIN-ID el problema estaría resuelto para futuros restores. Un punto importante, sin embargo es que el sp_change_users_login, tiene la opción REPORT que básicamente despliega cuales son los usuarios-SQL con IDs no sincronizados a ningún login. [Wellington, República Dominicana]
Leave a Comment
(*) Title:
(*) Name:
Your URL:
(*) Comments:
Follow us on: