Transferir o Mover Contraseñas de Inicios de Sesión de SQL Server entre Servidores
| | En alguna ocasión, me ha resultado necesario mover o transferir las contraseñas de los Inicios de Sesión de SQL Server entre Instancias. Lo suyo (en muchos casos), es crear los Inicios de Sesión en destino con el mismo SID y Password que en origen, pero si esto no se hizo así, y queremos mantener las mismas contraseñas, deberemos cambiarlas a posteriori, y surge la duda ¿Cómo mover contraseñas de SQL Server entre Instancias? En este artículo, se incluye Scripts de ejemplo para mover contraseñas de SQL Server, considerando las casuísticas de SQL Server 2000 y SQL Server 2005. |
El objetivo principal de este artículo, es incluir unos Scripts de ejemplo para mover contraseñas entre Instancias de SQL Server, algo no muy habitual, pero que en alguna ocasión nos puede resultar útil, así que, teniendo ya los Scripts preparados, eso que nos ahorramos. Mover las contraseñas entre Instancias de SQL Server, junto corregir los Usuarios Huérfanos, son tareas habituales en Migraciones y Consolidaciones de Instancias y Bases de Datos SQL Server. Antes de continuar, es importante recordar que SQL Server almacena el HASH de las contraseñas de los Inicios de Sesión de SQL Server, que podremos consultar en las tablas y/o vistas del sistema. Es decir, las contraseñas no se almacenan en texto claro. Así que, no podemos utilizar el procedimiento almacenado sp_password, puesto que no podremos averiguar la contraseña. Realmente, lo que queremos es obtener el HASH de la contraseña en el origen, y asignar ese HASH al Inicio de Sesión correspondiente en el destino. Es importante considerar que trataremos los siguientes casos: - Mover Contraseñas de SQL Server 2000 a SQL Server 2000.
- Mover Contraseñas de SQL Server 2000 a SQL Server 2005.
- Mover Contraseñas de SQL Server 2005 a SQL Server 2005.
Digo esto, porque existen algunas diferencias al respecto entre estas dos versiones de SQL Server, en particular: - En SQL Server 2000 la password no es susceptible de mayúsculas y minúsculas, mientras que en SQL Server 2005 SI es susceptible de mayúsculas y minúsculas.
- En SQL Server 2000 podemos obtener el hash de la contraseña del campo password de la tabla de sistema master.dbo.sysxlogins, mientras que en SQL Server 2005 y SQL Server 2008 necesitaremos recurrir a la vista del sistema master.sys.sql_logins. Existen más detalles, pues en sysxlogins está la definición de todos los Inicios de Sesión (sean Grupos de Windows, Usuarios de Windows, etc.) por lo que deberemos filtrar, mientras que en sql_logins sólo se almacena información de Inicios de Sesión de SQL Server.
- Para cambiar el HASH de la contraseña de un Inicio de Sesión en SQL Server 2000, deberemos utilizar una sentencia UPDATE sobre la tabla del sistema master.dbo.sysxlogins, para lo cual, previamente deberemos habilitar las actualizaciones de las tablas del sistema (sp_configure ‘allow updates’, 1). Sin embargo, en SQL Server 2005 y SQL Server 2008, podremos utilizar la sentencia ALTER LOGIN WITH PASSWORD HASHED.
Dicho todo esto, estamos en condiciones de presentar el código correspondiente para realizar el cambio de contraseñas. Téngase en cuenta, que la ejecución del código aquí incluido sobre la instancia origen, obtendrá como salida las sentencias que seguidamente deberemos ejecutar sobre la instancia destino. A continuación, se incluyen los scripts correspondientes: Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2000DECLARE curDBs CURSORREAD_ONLYFOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA) SELECt name, password FROM master.dbo.sysxlogins WHERE srvid IS NULL AND password IS NOT NULL AND name IN (SELECT name FROM master.dbo.syslogins WHERE isntname=0 AND isntgroup=0) AND name <> 'sa' ORDER BY 1 DECLARE @name VARCHAR(256)DECLARE @password VARBINARY(256)DECLARE @text_password VARCHAR(256)OPEN curDBs FETCH NEXT FROM curDBs INTO @name,@passwordWHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN exec sp_hexadecimal @password, @text_password OUT DECLARE @message varchar(2000) SELECT @message = 'update master.dbo.sysxlogins ' SELECT @message = @message + 'set password=' + @text_password + ' ' SELECT @message = @message + 'where name=''' + @name + ''' ' SELECT @message = @message + 'and srvid is null ' PRINT @message END FETCH NEXT FROM curDBs INTO @name,@passwordEND CLOSE curDBsDEALLOCATE curDBsGO |
Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2005DECLARE curDBs CURSORREAD_ONLYFOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA) SELECt name, password FROM master.dbo.sysxlogins WHERE srvid IS NULL AND password IS NOT NULL AND name IN (SELECT name FROM master.dbo.syslogins WHERE isntname=0 AND isntgroup=0) AND name <> 'sa' ORDER BY 1 DECLARE @name VARCHAR(256)DECLARE @password VARBINARY(256)DECLARE @text_password VARCHAR(256)OPEN curDBs FETCH NEXT FROM curDBs INTO @name,@passwordWHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN exec sp_hexadecimal @password, @text_password OUT DECLARE @message varchar(2000) SELECT @message = 'ALTER LOGIN ' + @name + ' ' SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED' PRINT @message END FETCH NEXT FROM curDBs INTO @name,@passwordEND CLOSE curDBsDEALLOCATE curDBsGO |
Mover Contraseñas de SQL Server de SQL Server 2005 a SQL Server 2005DECLARE curDBs CURSORREAD_ONLYFOR -- Inicios de Sesión de SQL Server (excepto SA) SELECt name, password_hash AS password FROM master.sys.sql_logins WHERE name <> 'sa' ORDER BY 1 DECLARE @name VARCHAR(256)DECLARE @password VARBINARY(256)DECLARE @text_password VARCHAR(256)OPEN curDBs FETCH NEXT FROM curDBs INTO @name,@passwordWHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN exec sp_hexadecimal @password, @text_password OUT DECLARE @message varchar(2000) SELECT @message = 'ALTER LOGIN ' + @name + ' ' SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED' PRINT @message END FETCH NEXT FROM curDBs INTO @name,@passwordEND CLOSE curDBsDEALLOCATE curDBsGO |
Por ultimo, aprovecho para colgar los anteriores Scripts, para que resulte más fácil su descarga y utilización, por parte de quienes deseen probarlos (ojo, que son susceptibles de fallo). Como siempre, espero que os guste ! El objetivo principal de este artículo, es incluir unos Scripts de ejemplo para mover contraseñas entre Instancias de SQL Server, algo no muy habitual, pero que en alguna ocasión nos puede resultar útil, así que, teniendo ya los Scripts preparados, eso que nos ahorramos. Mover las contraseñas entre Instancias de SQL Server, junto corregir los Usuarios Huérfanos, son tareas habituales en Migraciones y Consolidaciones de Instancias y Bases de Datos SQL Server. Antes de continuar, es importante recordar que SQL Server almacena el HASH de las contraseñas de los Inicios de Sesión de SQL Server, que podremos consultar en las tablas y/o vistas del sistema. Es decir, las contraseñas no se almacenan en texto claro. Así que, no podemos utilizar el procedimiento almacenado sp_password, puesto que no podremos averiguar la contraseña. Realmente, lo que queremos es obtener el HASH de la contraseña en el origen, y asignar ese HASH al Inicio de Sesión correspondiente en el destino. Es importante considerar que trataremos los siguientes casos: - Mover Contraseñas de SQL Server 2000 a SQL Server 2000.
- Mover Contraseñas de SQL Server 2000 a SQL Server 2005.
- Mover Contraseñas de SQL Server 2005 a SQL Server 2005.
Digo esto, porque existen algunas diferencias al respecto entre estas dos versiones de SQL Server, en particular: - En SQL Server 2000 la password no es susceptible de mayúsculas y minúsculas, mientras que en SQL Server 2005 SI es susceptible de mayúsculas y minúsculas.
- En SQL Server 2000 podemos obtener el hash de la contraseña del campo password de la tabla de sistema master.dbo.sysxlogins, mientras que en SQL Server 2005 y SQL Server 2008 necesitaremos recurrir a la vista del sistema master.sys.sql_logins. Existen más detalles, pues en sysxlogins está la definición de todos los Inicios de Sesión (sean Grupos de Windows, Usuarios de Windows, etc.) por lo que deberemos filtrar, mientras que en sql_logins sólo se almacena información de Inicios de Sesión de SQL Server.
- Para cambiar el HASH de la contraseña de un Inicio de Sesión en SQL Server 2000, deberemos utilizar una sentencia UPDATE sobre la tabla del sistema master.dbo.sysxlogins, para lo cual, previamente deberemos habilitar las actualizaciones de las tablas del sistema (sp_configure ‘allow updates’, 1). Sin embargo, en SQL Server 2005 y SQL Server 2008, podremos utilizar la sentencia ALTER LOGIN WITH PASSWORD HASHED.
Dicho todo esto, estamos en condiciones de presentar el código correspondiente para realizar el cambio de contraseñas. Téngase en cuenta, que la ejecución del código aquí incluido sobre la instancia origen, obtendrá como salida las sentencias que seguidamente deberemos ejecutar sobre la instancia destino. A continuación, se incluyen los scripts correspondientes: Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2000DECLARE curDBs CURSORREAD_ONLYFOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA) SELECt name, password FROM master.dbo.sysxlogins WHERE srvid IS NULL AND password IS NOT NULL AND name IN (SELECT name FROM master.dbo.syslogins WHERE isntname=0 AND isntgroup=0) AND name <> 'sa' ORDER BY 1 DECLARE @name VARCHAR(256)DECLARE @password VARBINARY(256)DECLARE @text_password VARCHAR(256)OPEN curDBs FETCH NEXT FROM curDBs INTO @name,@passwordWHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN exec sp_hexadecimal @password, @text_password OUT DECLARE @message varchar(2000) SELECT @message = 'update master.dbo.sysxlogins ' SELECT @message = @message + 'set password=' + @text_password + ' ' SELECT @message = @message + 'where name=''' + @name + ''' ' SELECT @message = @message + 'and srvid is null ' PRINT @message END FETCH NEXT FROM curDBs INTO @name,@passwordEND CLOSE curDBsDEALLOCATE curDBsGO |
Mover Contraseñas de SQL Server de SQL Server 2000 a SQL Server 2005DECLARE curDBs CURSORREAD_ONLYFOR -- Inicios de Sesión de SQL Server con la Password rellena (excepto SA) SELECt name, password FROM master.dbo.sysxlogins WHERE srvid IS NULL AND password IS NOT NULL AND name IN (SELECT name FROM master.dbo.syslogins WHERE isntname=0 AND isntgroup=0) AND name <> 'sa' ORDER BY 1 DECLARE @name VARCHAR(256)DECLARE @password VARBINARY(256)DECLARE @text_password VARCHAR(256)OPEN curDBs FETCH NEXT FROM curDBs INTO @name,@passwordWHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN exec sp_hexadecimal @password, @text_password OUT DECLARE @message varchar(2000) SELECT @message = 'ALTER LOGIN ' + @name + ' ' SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED' PRINT @message END FETCH NEXT FROM curDBs INTO @name,@passwordEND CLOSE curDBsDEALLOCATE curDBsGO |
Mover Contraseñas de SQL Server de SQL Server 2005 a SQL Server 2005DECLARE curDBs CURSORREAD_ONLYFOR -- Inicios de Sesión de SQL Server (excepto SA) SELECt name, password_hash AS password FROM master.sys.sql_logins WHERE name <> 'sa' ORDER BY 1 DECLARE @name VARCHAR(256)DECLARE @password VARBINARY(256)DECLARE @text_password VARCHAR(256)OPEN curDBs FETCH NEXT FROM curDBs INTO @name,@passwordWHILE (@@fetch_status <> -1)BEGIN IF (@@fetch_status <> -2) BEGIN exec sp_hexadecimal @password, @text_password OUT DECLARE @message varchar(2000) SELECT @message = 'ALTER LOGIN ' + @name + ' ' SELECT @message = @message + 'WITH PASSWORD = ' + @text_password + ' HASHED' PRINT @message END FETCH NEXT FROM curDBs INTO @name,@passwordEND CLOSE curDBsDEALLOCATE curDBsGO |
Por ultimo, aprovecho para colgar los anteriores Scripts, para que resulte más fácil su descarga y utilización, por parte de quienes deseen probarlos (ojo, que son susceptibles de fallo). Como siempre, espero que os guste ! |
| | |
