Hacer que tu base de datos sea portátil con las bases de datos independientes

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Usa usuarios de base de datos independiente para autenticar conexiones de SQL Server y Azure SQL Database a nivel de base de datos. Una base de datos independiente es una base de datos que está aislada de otras bases de datos y de la instancia de SQL Server o SQL Database (y la base de datos master) que aloja la base de datos.

SQL Server admite usuarios de base de datos independientes para la autenticación de Windows y SQL Server. Si usas SQL Database, combina usuarios de base de datos independiente con reglas de firewall a nivel de base de datos.

En este artículo se revisan las ventajas de utilizar el modelo de base de datos independiente en comparación con el modelo de inicio de sesión o usuario tradicional y las reglas del Firewall de Windows o de nivel de servidor. Es posible que la lógica de escenarios específicos, de manejabilidad o de software empresarial todavía pueda necesitar el uso de reglas de inicio de sesión o usuario tradicionales y de firewall de nivel de servidor.

Inicio de sesión tradicional y modelo de usuario

En el modelo de conexión tradicional, los usuarios de Windows o los miembros de grupos de Windows se conectan al motor de base de datos proporcionando credenciales de usuario o grupo autenticadas por Windows. Los usuarios también pueden proporcionar un nombre y una contraseña y conectarse mediante la autenticación de SQL Server. En ambos casos, la base de datos maestra debe tener un inicio de sesión que coincida con las credenciales de conexión.

Después de que el motor de base de datos confirme las credenciales de autenticación de Windows o autentique las credenciales de autenticación de SQL Server, la conexión suele intentar conectarse a una base de datos de usuario. Para conectarse a una base de datos de usuarios, el inicio de sesión debe asignarse (es decir, asociarse) a un usuario de la base de datos de usuario. La cadena de conexión también podría especificar la conexión a una base de datos específica, lo cual es opcional en SQL Server pero obligatorio en SQL Database.

El principio importante es que tanto el inicio de sesión (en la base de datos master) como el usuario (en la base de datos de usuario) deben existir y estar relacionados entre sí. La conexión a la base de datos de usuario tiene una dependencia del inicio de sesión en la base de datos master. Esta dependencia limita la capacidad de mover la base de datos a una instancia de SQL Server de hospedaje diferente o a un servidor de Azure SQL Database.

Si una conexión a la base de datos master no está disponible (por ejemplo, una conmutación por error está en curso), el tiempo total de conexión aumentará o es posible que se agote el tiempo de espera de la conexión. Una conexión no disponible puede reducir la escalabilidad de la conexión.

Modelo de usuario de base de datos independiente

En el modelo de usuario de base de datos independiente, el inicio de sesión en la base de datos master no está presente. En su lugar, el proceso de autenticación se produce en la base de datos de usuario. El usuario de base de datos de la base de datos de usuario no tiene un inicio de sesión asociado en la base de datos master.

El modelo de usuario de base de datos independiente admite tanto la autenticación de Windows como la autenticación de SQL Server. Puedes usarlo tanto en SQL Server como en SQL Database.

Para conectarse como un usuario de base de datos independiente, la cadena de conexión siempre debe contener un parámetro para la base de datos de usuario. El Motor de base de datos usa este parámetro para saber qué base de datos es responsable de administrar el proceso de autenticación.

La actividad del usuario de base de datos independiente se limita a la base de datos de autenticación. La cuenta de usuario de base de datos debe crearse de forma independiente en cada base de datos que el usuario necesite. Para cambiar de base de datos, los usuarios de SQL Database deben crear una nueva conexión. Los usuarios de base de datos independiente en SQL Server pueden cambiar de base de datos si un usuario idéntico está presente en otra base de datos. hay un usuario idéntico en otra base de datos.

En Azure, SQL Database y Azure Synapse Analytics admiten identidades de Microsoft Entra ID (anteriormente, Azure Active Directory) como usuarios de base de datos independiente. SQL Database admite usuarios de bases de datos independientes mediante la autenticación de SQL Server, pero Azure Synapse Analytics no. Para más información, consulta Conectarse a SQL Database con la autenticación de Microsoft Entra.

Cuando usas la autenticación de Microsoft Entra, los usuarios pueden realizar conexiones desde SQL Server Management Studio mediante la autenticación universal de Microsoft Entra. Los administradores pueden configurar la autenticación universal para requerir la autenticación multifactor, que comprueba la identidad mediante una llamada de teléfono, un mensaje de texto, una tarjeta inteligente con PIN o una notificación de aplicación móvil. Para más información, consulta Usar la autenticación multifactor de Microsoft Entra.

Para SQL Database y Azure Synapse Analytics, el nombre de la base de datos siempre es necesario en el cadena de conexión. No es necesario que hagas cambios en la cadena de conexión cuando cambias desde el modelo tradicional al modelo de usuario de base de datos independiente. Para las conexiones SQL Server, el nombre de la base de datos debe agregarse a la cadena de conexión, si no está ya presente.

Importante

Cuando usas el modelo tradicional, los roles de nivel de servidor y los permisos de nivel de servidor pueden limitar el acceso a todas las bases de datos. Cuando usas el modelo de base de datos independiente, los propietarios de la base de datos y los usuarios de la base de datos con el permiso ALTER ANY USER pueden conceder acceso a la base de datos. Este permiso reduce el control de acceso de inicios de sesión de servidor con privilegios altos y amplía el control de acceso para incluir los usuarios de la base de datos con privilegios altos.

Firewalls

SQL Server

En el caso de SQL Server, las reglas del Firewall de Windows se aplican a todas las conexiones y tienen el mismo efecto en los inicios de sesión (conexiones de modelo tradicional) y los usuarios de la base de datos independiente. Para obtener más información sobre el Firewall de Windows, consulta Configurar Firewall de Windows para el acceso al motor de base de datos.

Firewalls de SQL Database

SQL Database admite reglas de firewall independientes para las conexiones a nivel de servidor (inicios de sesión) y para las conexiones a nivel de base de datos (usuarios de bases de datos independientes). Cuando SQL Database se conecta a una base de datos de usuario, primero se comprueban las reglas de firewall de la base de datos. Si no hay ninguna regla que permita acceder a la base de datos, SQL Database comprueba las reglas de firewall de nivel de servidor. La comprobación de las reglas de firewall de nivel de servidor requiere acceso a la base de datos master del servidor de SQL Database.

Las reglas de firewall de nivel de base de datos combinadas con los usuarios de la base de datos independiente pueden eliminar la necesidad de acceder a la base de datos master del servidor durante la conexión, lo que puede mejorar la escalabilidad de la conexión. El resultado es una mejora de la escalabilidad de la conexión.

Para obtener más información sobre las reglas del firewall de SQL Database, consulte los siguientes temas:

Diferencias de sintaxis

Modelo tradicional Modelo de usuario de base de datos independiente
Cuando te conectas a la base de datos master:

CREATE LOGIN login_name WITH PASSWORD = 'strong_password';

Después, cuando te conectas a una base de datos de usuario:

CREATE USER 'user_name' FOR LOGIN 'login_name';		 Cuando te conectas a una base de datos de usuario:

CREATE USER user_name WITH PASSWORD = 'strong_password';
Modelo tradicional Modelo de usuario de base de datos independiente
Para cambiar una contraseña en el contexto de la base de datos master:

ALTER LOGIN login_name WITH PASSWORD = 'strong_password';		 Para cambiar una contraseña en el contexto de la base de datos de usuario:

ALTER USER user_name WITH PASSWORD = 'strong_password';

Instancia administrada de SQL

Azure SQL Managed Instance se comporta como SQL Server local en el contexto de las bases de datos independientes. Asegúrate de cambiar el contexto de la base de datos de la base de datos maestra a la de usuario al crear el usuario independiente. Además, no debería haber ninguna conexión activa con la base de datos de usuario cuando estableces la opción de contención. Usa el siguiente código como guía.

Advertencia

El siguiente script de ejemplo usa una instrucción kill para cerrar todos los procesos de usuario de la base de datos. Asegúrate de comprender las consecuencias de este script y de que se adapte a su negocio antes de ejecutarlo. Asegúrate también de que ninguna otra conexión esté activa en la base de datos de SQL Managed Instance, ya que el script interrumpirá otros procesos que se ejecutan en la base de datos.

USE master;

SELECT * FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test')

DECLARE @kill_string varchar(8000) = '';
SELECT @kill_string = @kill_string + 'KILL ' + str(session_id) + '; '  
FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test') and is_user_process = 1;

EXEC(@kill_string);
GO

sp_configure 'contained database authentication', 1;  
GO
 
RECONFIGURE;  
GO 

SELECT * FROM sys.dm_exec_sessions
WHERE database_id  = db_id('Test')

ALTER DATABASE Test
SET containment=partial

USE Test;  
GO 

CREATE USER Carlo  
WITH PASSWORD='Enterpwdhere*'  

SELECT containment_desc FROM sys.databases
WHERE name='Test'

Comentarios

  • Los usuarios de bases de datos independientes deben estar habilitados para todas las instancias de SQL Server. Para más información, consulta Autenticación de base de datos independiente (opción de configuración del servidor).
  • Los usuarios de base de datos independiente y los inicios de sesión con nombres no superpuestos pueden coexistir en las aplicaciones.
  • Supongamos que un inicio de sesión en la base de datos master tiene el nombre name1. Si creas un usuario de base de datos independiente denominado name1, cuando se proporcione un nombre de base de datos en la cadena de conexión, el contexto del usuario de base de datos tendrá prioridad sobre el contexto de inicio de sesión al conectarse a la base de datos. Es decir, el usuario de base de datos independiente tiene prioridad sobre los inicios de sesión con el mismo nombre.
  • En SQL Database el nombre del usuario de la base de datos independiente no puede ser el mismo que el nombre de la cuenta de administrador del servidor.
  • La cuenta de administrador del servidor de bases de datos SQL nunca puede ser un usuario de base de datos independiente. El administrador del servidor tiene los permisos necesarios para crear y administrar los usuarios de la base de datos independiente. El administrador del servidor puede conceder permisos a los usuarios de base de datos independiente en bases de datos de usuario.
  • Ya que los usuarios de base de datos independiente son entidades de seguridad de nivel de base de datos, deberás crear usuarios de base de datos independiente en cada base de datos en la que los utilizarás. La identidad se limita a la base de datos. La identidad es independiente (en todos los aspectos) de un usuario con el mismo nombre y la misma contraseña de otra base de datos del mismo servidor.
  • Usa las mismas contraseñas seguras que usarías normalmente para los inicios de sesión.

Contenido relacionado