SQL Server, ideas y experiencias

Como recuperar la contraseña del SA si se perdió o se olvidó

por Jose Mariano Alvarez 25. febrero 2011

PanicoComo los instaladores del SQL Server de las últimas versiones, no incorporan automáticamente al grupo de administradores de Windows (builtin\Administrators) como administradores del SQL Server (y tampoco es una buena práctica hacerlo), es bastante probable que ocurran este tipo de problemas o accidentes si los procedimientos para la generación, protección y guardado de contraseñas no están documentados y no son estrictos.

A veces, durante los procesos de operación y administración de los servidores, o instalación y configuración, puede suceder que se pierden las contraseñas o los privilegios de los administradores de un motor de base de datos de SQL Server. Entre los casos más habituales se encuentran por ejemplo:

  • Se han quitado todos los inicios de sesión o todos los grupos de Windows que son miembros del rol fijo de servidor sysadmin,
  • Los inicios de sesión que son miembros del rol fijo de servidor sysadmin son personas que no están disponibles porque han dejado la empresa o están de vacaciones por ejemplo
  • Se pierde la contraseña, ya sea del SA o de un inicio de sesión creado como administrador de SQL Server.

Una manera de solucionar este problema es reinstalar SQL Server. Sin embargo este procedimiento tiene algunas consecuencias ya que más allá del tiempo necesario para realizar la reinstalación se pierden en este proceso datos que están grabados en las bases del sistema, como por ejemplo los inicios de sesión (o logins) y por lo tanto se deberá recuperar estos datos desde una copia de seguridad. Se debe tener en cuenta que al realizar este proceso de restauración se vuelve al estado anterior al proceso de instalación porque se reescribe la base de datos del sistema con una copia previa y dependiendo del caso, podría tener los mismos inicios de sesión que antes de la reinstalación y por consiguiente, los administradores aún no tendrán acceso.

Solución

Para resolver este problema, lo mejor es aprovechar las propiedades que tienen los administradores de Windows luego de iniciar la instancia de SQL Server en modo de usuario único utilizando la opción -m. Después de iniciar la instancia del SQL Server en modo de usuario único, cualquier miembro del grupo local de administradores del equipo puede conectarse a la instancia de SQL Server como miembro del rol fijo de servidor sysadmin y realizar tareas administrativas como cambiar permisos. Se debe tener en cuenta que en este modo no se pueden realizar todos los cambios posibles. Esto previene que los administradores de Windows abusen de los privilegios de actuar como un administrador de SQL Server.

Los pasos a seguir son los siguientes:

  1. Abrir el SQL Server Configuration Manager desde el menú de Windows y detener el servicio de la instancia del SQL Server involucrada.
  2. En la solapa de “Avanzado” (Advanced), en el cuadro de texto propiedades (Properties), agregar “;–m” al final de la lista en la opción de parámetros de arranque “Startup parameters”. Debe tenerse en cuenta que no haya espacios entre “;” y “-m” porque si no va a dar error.
  3. Aceptar los cambios presionando “OK”.
  4. Reiniciar la instancia del SQL Server. En este instante se debe verificar que el resto de los servicios que componen el SQL Server están de detenidos, especialmente si alguno de esos corre con una cuenta de servicio que es administrador local, porque estos servicios podrían conectarse utilizando la única conexión disponible antes de que lográramos conectarnos para realizar los cambios necesarios, y ya no sería posible que nos conectáramos porque sería una segunda conexión.
  5. Conectarse al sql server utilizando autenticación Windows mediante la herramienta de línea de comandos “sqlcmd”. El usuario de Windows debe ser administrador local. (*)

    Por ejemplo:

    SQLCMD –S Localhost –E
  6. Ejecutar por ejemplo un comando TSQL como "sp_addsrvrolemember" para agregar un inicio de sesión existente (o uno nuevo recién creado con CREATE LOGIN) como administrador del sql server o cambiar la contraseña de inicio de sesión con ALTER LOGIN.

    Por ejemplo:
    CREATE LOGIN [Dominio\usuario] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    EXEC master..sp_addsrvrolemember             
    @loginame = N'Dominio\usuario',
    @rolename = N'sysadmin' GO
  7. Luego de que se tiene acceso al sql server como administrador, eliminar el “;-m” como parámetro de arranque utilizando el configuration manager y reiniciar la instancia del sql server.

(*) Importante: Si el sistema operativo tiene User Account Control (UAC), y está activado,  a pesar de pertenecer al grupo de administradores locales, no es posible conectarse al SQL Server con privilegios de administrador local a menos que la consola se abra con la opción de “ejecutar como administrador” utilizando el menú de contesto que aparece presionando el botón derecho sobre el icono del CMD.EXE.

Referencias:

Solucionar problemas: conectarse a SQL Server cuando los administradores del sistema no tienen acceso

http://msdn.microsoft.com/es-ar/library/dd207004.aspx

Iniciar SQL Server en modo de usuario único

http://msdn.microsoft.com/es-ar/library/ms188236.aspx

Tags: ,

Artículos

FIX para el Error 315 The backup set holds backup of a database other than the existing database

por Jose Mariano Alvarez 25. noviembre 2010

Hace poco migrando de equipo y edición (pero no de versión) una base de datos de SQL Server utilizada por Blackberry que estaba en un SQL Server 2005 SP3 me dio un error al realizar un RESTORE con el siguiente comando en el nuevo servidor:


RESTORE
DATABASE [BESMgmt] FROM DISK = N'O:\MSSQL\Blackberry\BESMgmt20101125.bak' WITH MOVE N'BESMgmt_data' TO N'O:\MSSQL\Blackberry\BESMgmt.mdf', MOVE N'BESMgmt_log' TO N'O:\MSSQL\Blackberry\BESMgmt.ldf', NOUNLOAD, STATS = 10 GO

El error fue el siguiente

Error 3154: The backup set holds a backup of a database other than the existing database.

Lo extraño fue que había hecho el Backup con el siguiente comando con lo cual estaba totalmente seguro que el archivo de Backup contenía la base de datos correcta.

BACKUP DATABASE [BESMgmt] 
TO  DISK = N'C:\Backup\BESMgmt20101125.bak' 
WITH NOFORMAT, NOINIT,  
NAME = N'BESMgmt-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

La solución

La solución es simple e ingeniosa. Únicamente debemos “mentirle” para que crea que debe sobrescribir una base de datos existente utilizando WITH REPLACE. Aunque la base de datos no existe la ejecución es exitosa y resuelve el problema, permitiendo realizar el RESTORE.

Ejemplo

RESTORE DATABASE [BESMgmt] 
FROM  DISK = N'O:\MSSQL\Blackberry\BESMgmt20101125.bak' 
WITH REPLACE,  
MOVE N'BESMgmt_data' 
TO N'O:\MSSQL\Blackberry\BESMgmt.mdf',  
MOVE N'BESMgmt_log'      
TO N'O:\MSSQL\Blackberry\BESMgmt.ldf',  
NOUNLOAD,  STATS = 10
GO

Tags: , ,

Artículos

Como transferir los roles de servidor entre instancias de SQL Server 2005 o 2008

por Jose Mariano Alvarez 13. agosto 2010

Recientemente escribí un post que habla de cómo transferir los Logins entre instancias de SQL Server utilizando scripts documentados en la base de conocimiento de Microsoft.  Pueden encontrar este post en:

Cómo transferir los inicios de sesión y las contraseñas entre instancias de SQL Server

Además de transferir la contraseña, normalmente queremos que el nuevo Login tenga el mismo SID para evitar que los usuarios queden huérfanos dentro de las bases de datos migradas o transferidas. Esto lo realizan los script  documentados en la base de conocimiento que hago referencia en el post.

Sin embargo uno de los lectores me hizo notar que no había explicado que los scripts generados por estos documentos no generan los scripts correspondientes a los roles (funciones) de servidor que tenían en la instancia de origen.  Solo el método 2 del documento en inglés que transfiere los Logins desde el SQL Server 2000 al SQL Server 2005/2008 tiene la posibilidad de generar el script para transferir los roles de servidor entre las instancias. Las otras versiones del script no lo hacen.

Si ya hemos creado los Login con el script de estos documentos de la base de conocimiento y debemos  además, transferir los roles de servidor a esta nueva instancia, y nuestro origen es un SQL Server 2005 o un SQL Server 2008, podemos usar el siguiente script para generar el script de transferencia:

-- Genera el script con los Roles para los Logins
Select 
 'exec master.dbo.sp_addsrvrolemember @loginame=''' 
 + sp.name + ''', @rolename='''+sp2.name +''';' 
from 
master.sys.server_principals as sp 
join master.sys.server_role_members as srm 
on sp.principal_id = srm.member_principal_id 
join master.sys.server_principals as sp2 
on srm.role_principal_id=sp2.principal_id
where sp.type in ('S','U','G')


-- Genera el script con la base de datos por default (omision) para los Logins
Select 
 'ALTER LOGIN ['+ sp.name 
 +'] WITH DEFAULT_DATABASE = [' 
 + sp.default_database_name +'];'
from 
master.sys.server_principals as sp 
where sp.type in ('S','U','G')
and sp.default_database_name is not null

Tags: , ,

Artículos

Cómo transferir los inicios de sesión y las contraseñas entre instancias de SQL Server

por Jose Mariano Alvarez 9. agosto 2010

Una de las tareas fundamentales a la hora de hacer las migraciones o mover las bases de datos de una instancia a otra es transferir los logins. Cuando queremos transferir un login entre instancias de SQL Server, además de transferir la contraseña, normalmente queremos que el nuevo login tenga el mismo SID para evitar que los usuarios queden huérfanos dentro de las bases de datos migradas o transferidas.

En la base de conocimiento de Microsoft existen dos artículos que describen los pasos y los detalles a tener en cuenta para realizar esta tarea. Se deberá seleccionar el que corresponda dependiendo de la versión de SQL Server de origen como de destino.

El método usado es el de crear dos procedimientos almacenados que luego al ser invocados generan una secuencias de comandos T-SQL que permiten crean los inicios de sesión con el mismo SID y la contraseñas original cuando se los ejecutan en la nueva instancia.

 

How to transfer logins and passwords between instances of SQL Server

En este artículo (en inglés) se describe cómo transferir los inicios de sesión y las contraseñas a un nuevo servidor. El método explicado en el documento se aplica cuando:

  • Transfiere inicios de sesión y contraseñas de SQL Server 7.0 a SQL Server 7.0.
  • Transfiere inicios de sesión y contraseñas de SQL Server 7.0 a SQL Server 2000.
  • Transfiere inicios de sesión y contraseñas de SQL Server 7.0 a SQL Server 2005.
  • Transfiere inicios de sesión y contraseñas entre servidores que ejecutan SQL Server 2000.
  • Transfiere inicios de sesión y contraseñas de SQL Server 2000 a SQL Server 2005.

http://support.microsoft.com/?scid=kb;en-us;246133&x=13&y=12

IMPORTANTE: Existen diferencias entre el artículo de la versión en inglés que contiene dos scripts diferentes, un script para transferir los logins entre versiones SQL Server 7.0/2000 y SQL Server 7.0/2000 y otro script para transferirlos entre SQL Server 7.0/2000 y SQL Server 2005/8.  En el mismo artículo en español solo está documentado el procedimiento entre versiones SQL Server 7.0/2000 a SQL Server 7.0/2000.

Cómo transferir inicios de sesión y contraseñas entre servidores SQL Server

Versión en español (incompleto)

http://support.microsoft.com/kb/246133/es

 

Cómo transferir los inicios de sesión y las contraseñas entre instancias de SQL Server 2005 y SQL Server 2008

Este artículo describe cómo transferir los inicios de sesión y las contraseñas entre instancias de Microsoft SQL Server 2005 y Microsoft SQL Server 2008.

http://support.microsoft.com/kb/918992/es

Tags: , ,

Documentos

Cómo ejecutar xp_cmdshell con mínimos permisos

por Jose Mariano Alvarez 3. agosto 2009

El procedimiento almacenado xp_cmdshell es esencialmente un mecanismo para ejecutar llamadas en el sistema operativo utilizando el contexto de SQL Server (es decir, la cuenta de Windows utilizada para iniciar el servicio del SQL Server) o una cuenta proxy que puede ser configurada para ejecutar xp_cmdshell con diferentes credenciales. Si bien habilitar y utilizar este procedimiento almacenado no es una buena práctica, ciertas aplicaciones usan esta técnica y a veces puede ser necesario acceder a la línea de comandos del sistema operativo para ejecutar algún programa fuera del SQL Server. En estos casos tenemos que tener mucho cuidado en como habilitamos y damos permisos para que el usuario pueda acceder a los recursos del sistema operativo con mínimos permisos. En este artículo analizaremos algunos detalles y de cómo habilitarlo con menos permisos que los indicado en la ayuda.

El procedimiento almacenado xp_cmdshell

Para ejecutar un programa fuera del contexto del SQL Server se utiliza el procedimiento almacenado extendido xp_cmdshell, Con este procedimiento almacenado extendido podemos ejecutar cualquier proceso de línea de comandos, por lo que no solo se pueden ejecutar programas EXE sino que se puede ejecutar un archivo de lotes (BAT o CMD).

Debemos tener sumo cuidado en cómo se utiliza este procedimiento almacenado ya que si el contexto de ejecución tiene permisos de administrador local del equipo por ejemplo el programa o un código inyectado pueden agregar un usuario administrador del equipo (del WIndows) utilizando NET USER y NET GROUP.

En SQL Server 2000, por los riesgos de seguridad que implica y para limitar el acceso al xp_cmdshell , su uso está restringido por defecto o en forma predeterminada solo a los miembros de la función de servidor o rol sysadmin. Para extender los derechos a otros usuarios se puede utilizar el comando GRANT.

En SQL Server 2005 el uso de xp_cmdshell está desactivado por defecto como mecanismo de protección para minimizar los riesgos de seguridad de código no deseado en ejecución dentro o fuera de SQL Server de SQL Server. Por ello, si no se habilita la ejecución del xp_cmdshell ni siquiera los administradores del SQL Server (grupo de servidor sysadmin) podrán ejecutarlo.

Por ejemplo si ejecutamos como administradores:

exec xp_cmdshell 'dir c:\*.*'

El resultado es:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

 

Para poder ejecutar el procedimiento almacenado extendido xp_cmdshell debemos habilitarlo haciendo:

--Habilito la ejecucion del xp_cmdshell

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE
GO

Ahora vemos que si volvemos a ejecutar el scrip T-SQL anterior como administrador del SQL Server vemos que nos responde cuando estamos autenticados como administradores. El proceso de Windows creado por xp_cmdshell dispone de los mismos derechos de seguridad que la cuenta de servicio de SQL Server la cual tenía los permisos adecuados en la raíz del disco.

 

Cuenta proxy para el xp_cmdshell

Cuando es llamada por un usuario que no pertenece al rol (función de servidor) sysadmin, el xp_cmdshell se conecta a Windows con el nombre de la cuenta y la contraseña almacenados en la credencial con el nombre ##xp_cmdshell_proxy_account## en lugar de usar la cuenta de servicio por lo que habrá que indicarla previamente o sino dará error.

Vamos a probar ahora con un login que solo tiene permisos mínimos

--Creamos el login 
CREATE LOGIN [Prueba]
WITH PASSWORD=N'<la contraseña del usuario>',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Para crear la credencial de la cuenta de proxy hay que ejecutar sp_xp_cmdshell_proxy_account y como parámetros el nombre del usuario y la contraseña de Windows.

-- Asigno la proxy account
EXEC sp_xp_cmdshell_proxy_account
N'sqltotal\mariano',
N'<la contraseña del usuario>'

El resultado es:

Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1

An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.

Aquí nos encontramos con un nuevo problema si estamos usando Windows Vista como es mi caso. No podemos asignar esta contraseña por más que el usuario usado sea administrador del SQL Server debido al UAC. Por lo tanto debemos ejecutar este comando en una nueva instancia del SQL Server Management Studio como administrador usando la opción “RUN AS ADMINISTRATOR” (ejecutar como administrador).

Luego de asignar la cuanta proxy como administrador local y del SQL Server abrimos una nueva ventana con el usuario prueba y verificamos que pasa:

exec xp_cmdshell 'dir c:\*.*'

Y el resultado es:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

En este punto si revisamos la ayuda en el link siguiente podemos verificar que nos dice que se requiere el permiso CONTROL SERVER, para poder ejecutar xp_cmdshell el cual no es adecuado ya que posibilita tomar el control total del SQL Server.

xp_cmdshell en la ayuda del SQL Server

Por lo tanto vamos a crear el usuario dentro de la base de datos master relacionado con el login y asignar solo el permiso de ejecución del procedimiento almacenado:

-- Creo el usuario en la base de datos
-- master para el login anterior
USE [master]
GO

CREATE USER [Prueba] FOR LOGIN [Prueba]
GO

-- Asigno el permiso de ejecucion al usuario
use [master]
GO

GRANT EXECUTE ON [sys].[xp_cmdshell]
TO [Prueba]
GO

Si ahora probamos veremos que es posible ejecutar el procedimiento almacenado xp_cmdshell sin ser administrador sin asignar permisos de CONTROL SERVER.

Veamos qué pasa si tenemos asignado solo el permiso de ejecución del procedimiento almacenado pero no existe la credencial.

Como administradores eliminamos la cuenta proxy:

EXEC sp_xp_cmdshell_proxy_account null

Probamos la ejecucion del procediemiento almacenado nuevamente con el usuario prueba

exec xp_cmdshell 'dir c:\*.*'

El resultado es:

Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. 

Conclusiones

  • No es recomendable utilizar el procedimiento almacenado xp_cmdshell.
  • Para poder ejecutarlo se requiere habilitar su ejecución en la configuración del SQL Server utilizando el procedimiento almacenado sp_configure.
  • Si somos administradores podemos ejecutar el procedimiento xp_cmdshell el cual utiliza la cuenta de servicio del SQL Server
  • Si no somos administradores utiliza la cuenta proxy que debe estar definida.
  • Si no somos administradores solo debemos tener permiso de ejecución en el procedimiento almacenado xp_cmdshell y no hace falta asignar el permiso CONTROL SERVER como dice la ayuda.

Tags: , ,

Artículos

Cómo permitir el uso del Profiler o la creación de Trazas sin tener derechos de administrador

por Jose Mariano Alvarez 31. julio 2009

La ejecución del SQL Server Profiler requiere al menos del permiso de servidor ALTER TRACE. Este es el mismo permiso que se requiere para poder ejecutar los procedimientos almacenados de T-SQL que se utilizan para crear las trazas.

Veamos un ejemplo.

Creamos un usuario con mínimos permisos :

USE [master]

GO

-- creamos el Login 

CREATE LOGIN [prueba] 

WITH PASSWORD=N'pruebaPass', 

DEFAULT_DATABASE=[master], 

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Luego podemos verificar que no se puede acceder mediante el profiler:

ProfilerPrbPerm

Hacer click en la imagen para agrandarla.

Asignamos el permiso de servidor ALTER TRACE con lo cual le concedemos el permiso de hacer trazas y conectar el Profiler para realizar un seguimiento.

GRANT ALTER TRACE 

TO [Prueba]

GO

Luego nos conectamos desde el Profiler y verificamos que ahora si se puede conectar.

Entonces ejecutamos :

CREATE LOGIN [prueba2] 

WITH PASSWORD=N'pruebaPass', 

DEFAULT_DATABASE=[master], 

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

exec sp_help 'sys.indexes'

Como puede verse en la próxima imagen siguiente, el Profiler funciona y como seleccionamos el evento SQL:BatchStarting nos muestra las sentencias que ejecutamos.

CreateLoginEnProfiler

Hacer click en la imagen para agrandarla.

Consideraciones de seguridad importantes al asignar el permiso ALTER TRACE

Es importante ver que en el caso del CREATE LOGIN nos oculta los datos del usuario y la contraseña. Sin embargo nos muestra el parámetro del procedimiento almacenado sp_help.

Como los usuarios que tienen el permiso ALTER TRACE pueden ver las consultas capturadas, se debe tener cuidado ya que las mismas pueden contener datos confidenciales, como contraseñas que se pasan como parámetros en los procedimientos almacenados. El caso mas común es cuando se utilizan mecanismos propietarios de autenticación. Por lo tanto, es importante conceder estos permisos solo a los usuarios que tengan autorización para ver información confidencial.

Tags: , , , ,

Artículos

Usando el Resource Governor - Documento

por Jose Mariano Alvarez 25. julio 2009

CPUPerfGraf Entre las nuevas características que se encuentran en el SQL Server 2008 está el Resource Governor (regulador de recursos), que ofrece la capacidad de vigilar y controlar el uso de CPU y memoria.

Este “White Paper” explica varios escenarios de uso práctico del Resource Governor y ofrece orientación sobre las mejores prácticas para emplearlo de manera eficiente. Se encuentra dirigido a los profesionales de IT y administradores de base de datos responsables de la gestión de los servidores SQL Server 2008. Entre las características que cubre el documento se encuentran la forma de utilizar el Resource Governor tomando en cuenta los requerimientos de concurrencia, de alta disponibilidad, la consolidación, la virtualización, o los acuerdos de nivel de servicio (Service Level Agreement o SLA) para los casos de entornos de producción con SQL Server.

En el documento se destacan varios escenarios de uso común, que pueden ayudar a decidir cuándo y dónde utilizar esta tecnología, y un resumen de las mejores prácticas cuando se utiliza el Resource Governor.

Les dejo el enlace para poder descargar el documento (en inglés) desde el sitio de Microsoft

Using the Resource Governor (DOCX)

Si les interesan los conceptos básicos les dejo un par de links a los libros de ayuda:

Introducción al regulador de recursos

Conceptos del regulador de recursos

Tags: , , , , , ,

Documentos

Powered by SQL Total Consulting


View Jose Mariano Alvarez's profile on LinkedIn

 Add to Technorati Favorites 

Calendar

<<  agosto 2017  >>
lumamijuvido
31123456
78910111213
14151617181920
21222324252627
28293031123
45678910

View posts in large calendar

Locations of visitors to this page

Widget Twitter not found.

Root element is missing.X


Valid XHTML 1.0 Transitional

Valid CSS!