Administar Inicios de Sesión de SQL en entornos diferentes

En este post quiero mostrar como podemos administrar inicios de sesion en SQL Server para evitar un problema bastante comun En SQL Server tenemos la posibilidad de autenticarnos de dos maneras diferentes, una está basada en la Autenticación de Windows y permite que los usuarios de Windows (locales o de dominio) se conecten al servidor usando sus credenciales y la otra está basada en inicios de sesión (logins) en el propio SQL Sever, los cuales se mantienen dentro de la instancia de SQL Server.

La autenticación de Windows siempre está habilitada y no se puede deshabilitar (en la version para Windows de SQL Server) y, por el contrario, la autenticación de SQL Server debe habilitarse bien sea durante la instalación proporcionando una contraseña para [sa], o en cualquier otro momento en SSMS yendo a

Server properties -> Security

 
Antecedentes

Es posible que la mayoría de nuestros servidores tengan ambos habilitados por lo que tarde o temprano nosotros, como DBAs, tendremos que lidiar con inicios de sesión de SQL, pero además de proporcionar un nombre y una contraseña (una fuerte, por supuesto) hay más cosas.

Si aparte de eso, tenemos diferentes entornos, lo más probable es que queramos crear inicios de sesión diferentes para evitar que las aplicaciones o usuarios de DEV se conecten a LIVE o viceversa.

Pero cuando tenemos inicios de sesión diferentes para cada entorno y, por defecto, usuarios de bases de datos, cuando necesitemos actualizar DEV (TEST, QA …) habrá que volver a aplicar todos los permisos otorgados al usuario correcto porque ese inicio de sesión no existe en ese entorno. ¿Os suena familiar?

En esta publicación, os mostraré cómo podemos manejar este problema de una manera muy simple.

 
Crear un inicio de sesión de SQL

Podemos crear un inicio de sesión en SQL Server Management Studio navegando a

Server -> Security -> Logins -> New login

Como es habitual, debemos proporcionar la mayor cantidad de información posible, incluida la base de datos predeterminada y los usuarios de la base de datos asignados, no olvidéis anular la opción de db_owner para el nuevo usuario. Para ser sincero, no veo por qué está marcado por defecto.

 
Independientemente de si hacemos clic en «Aceptar» o «Script», hay algo que falta y es probablemente de lo más importante. Incluso una vez que el inicio de sesión existe y generamos el script de creación, no se mostrará.

¿Ya sabes de lo que estoy hablando? Efectivamente… el SID.

 
El SID es todo

A diferencia de lo que la gente pueda pensar, SQL Server identifica de manera única los inicios de sesión basandose ​​en sus SID, no sus nombres.

Los SID (identificadores de seguridad) pueden ser proporcionados por un dominio o una computadora que use Windows o por un servidor SQL Server. Esa es la clave de lo que estamos tratando de lograr. No existe un mecanismo de emparejamiento por nombre, por lo que todo se basa en el SID del inicio de sesión que intenta conectarse a nuestro servidor SQL.

Los identificadores de seguridad son también el mecanismo para emparejar los inicios de sesión del servidor con los usuarios de las bases de datos.

Como dije antes, no hay forma de que en SSMS generemos un script de un inicio de sesión de SQL Server que incluya el SID, pero por suerte hace algún tiempo publiqué mi procedimiento almacenado [dbo].[sqlg_securityAuditServerLogins] que hace el trabajo por mí (y por vosotros también)

 
Cómo ahorrarnos un problema

La mejor manera de solucionar un problema es no tener un problema, así que veamos cómo podemos abordar el hecho de tener diferentes inicios de sesión, uno por entorno que permita el acceso a la base de datos independientemente del entorno en el que nos encontremos.

Necesitaremos:

• Un nombre de inicio de sesión por entorno, cada uno con una contraseña diferente
• Un usuario de base de datos y solo uno para todos los entornos

 
El primer inicio de sesión se puede hacer de cualquiera de las dos formas, usando TSQL o la interfaz de usuario, no importa.

USE [master]
GO
CREATE LOGIN [SalesApp_dev] WITH PASSWORD=N'StrongPassword.123!'
	, DEFAULT_DATABASE=[AdventureWorks2014]
	, CHECK_EXPIRATION=OFF
	, CHECK_POLICY=ON
GO
USE [AdventureWorks2014]
GO
CREATE USER [SalesApp] FOR LOGIN [SalesApp_dev]
GO
GRANT SELECT ON [Person].[Address] TO [SalesApp]

Fijaos que el usuario de la base de datos no incluye _dev, porque personalmente odio encontrar cosas de _dev en mis servidores de producción, pero eso depende totalmente vosotros.

Una vez que hayamos creado nuestro inicio de sesión, podemos ver el código comandos, incluido el SID, con mi procedimiento almacenado. Simplemente desplácese a la última columna para encontrarlo.

EXECUTE master.dbo.sqlg_securityAuditServerLogins @loginName = N'SalesApp_dev'

El script generado es el siguiente

USE [master]
GO
CREATE LOGIN [SalesApp_dev] WITH PASSWORD = 0x02001144A8F0BFE5A5C2C5E62656B13289912E02A1A483F77BD58D172F8779FBD495504D9F1196545B87983921D0629AB1DD4D0788DC1C1C813DBA66639F6FE0813010DC38DB HASHED
, SID = 0xE27321DFC1DDFC4B83D0398823792E58
, DEFAULT_DATABASE = [AdventureWorks2014]
, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
GO
GRANT CONNECT SQL TO [SalesApp_dev]
GO

Tened en cuenta que la contraseña está cifrada y trae el SID. Así que lo que necesitamos cambiar para ejecutar esto en otros entornos es:

• nombre de inicio de sesión
• contraseña

 
y eso seria todo. Ya podemos coger ese código y crear el inicio de sesión en nuestro servidor de producción (si consideramos que este era nuestro entorno de desarrollo)

Vamos a crear el inicio de sesión en nuestro servidor de «producción», que es mi otra instancia MSSQL2016.

USE [master]
GO
CREATE LOGIN [SalesApp] WITH PASSWORD = N'StrongPassword.456!'
, SID = 0xE27321DFC1DDFC4B83D0398823792E58
, DEFAULT_DATABASE = [AdventureWorks2014]
, CHECK_POLICY = ON, CHECK_EXPIRATION = OFF
GO
GRANT CONNECT SQL TO [SalesApp]
GO

Ahora estamos listos para ver el truco que hemos estado preparando.

Por lo general, sería al revés, pero en este caso vamos a tomar una copia de seguridad del servidor ‘Dev’ y restaurarlo en ‘Prod’ y luego podremos ver cómo funciona de maravilla.

-- Run this in SQLDOUBLEG\MSSQL2014 (aka dev)

BACKUP DATABASE [AdventureWorks2014] TO DISK ='AdventureWorks2014_SalesApp.bkp'

-- Copy the file to a location that can be accessed by the other instance

-- Run this in SQLDOUBLEG\MSSQL2016 (aka prod)


RESTORE DATABASE AdventureWorks2014
FROM DISK = 'AdventureWorks2014_SalesApp.bkp' 
WITH RECOVERY, REPLACE
, MOVE 'AdventureWorks2014_Data'	TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\AdventureWorks2014_Data.mdf'
, MOVE 'AdventureWorks2014_Log'		TO 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQL2016\MSSQL\DATA\AdventureWorks2014_Log.ldf'

GO

 
Si ahora verificamos los emparejamientos de los usuarios de la base de datos en los diferentes servidores, podemos ver que se corresponden con los inicios de sesión correctos

-- Run in SQLCMD mode
:CONNECT SQLDOUBLEG\MSSQL2014

USE AdventureWorks2014

SELECT @@SERVERNAME AS server_name
		, p.name AS dbuser_name
		, p.sid AS dbuser_sid
		, SUSER_SNAME(p.sid) AS login_name
	FROM sys.database_principals AS p
	WHERE name = 'SalesApp'

GO

:CONNECT SQLDOUBLEG\MSSQL2016

USE AdventureWorks2014

SELECT @@SERVERNAME AS server_name
		, p.name AS dbuser_name
		, p.sid AS dbuser_sid
		, SUSER_SNAME(p.sid) AS login_name
	FROM sys.database_principals AS p
	WHERE name = 'SalesApp'

Y es como, mis queridos lectores, podemos atajar este problema tan común sin tener que volver a correlacionar los inicios de sesión y los usuarios cada vez que restauran una base de datos de un entorno a otro.

 
Conclusión

Aunque es recomendable utilizar la autenticación de Windows siempre que sea posible, existen motivos legítimos para usar los inicios de sesión de SQL, para eso existen, pero con un poco de conocimiento de los mecanismos internos, podemos hacer que su mantenimiento sea un poco más fácil.

¡Gracias por leer!