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.
