Autenticación de SQL en Azure Synapse Analytics

Azure Synapse Analytics tiene dos factores de forma SQL que le permiten controlar el consumo de recursos. En este artículo se explica de qué forma controlan los dos factores de forma la autenticación de usuario.

Para realizar la autorización en Synapse SQL, puede usar dos tipos de autorización:

• Authorization de Microsoft Entra
• Autorización de SQL

La autorización de SQL permite a las aplicaciones heredadas conectarse a Azure Synapse SQL de forma muy familiar. Sin embargo, la autenticación de Microsoft Entra le permite administrar de forma centralizada el acceso a los recursos de Azure Synapse, como grupos de SQL. Azure Synapse Analytics admite la deshabilitación de la autenticación local, como la autenticación de SQL, durante y después de la creación del área de trabajo. Una vez deshabilitada, los usuarios autorizados pueden habilitar la autenticación local en cualquier momento. Para obtener más información sobre la autenticación solo de Microsoft Entra, consulte Deshabilitación de la autenticación local en Azure Synapse Analytics.

Cuentas administrativas

Hay dos cuentas administrativas (nombre de usuario administrador de SQL y administrador de SQL Active Directory) que actúan como administradores. Para identificar estas cuentas de administrador para los grupos de SQL, abra Azure Portal y vaya a la pestaña "Propiedades" del área de trabajo de Synapse.

• Nombre de usuario administrador de SQL

  Al crear una instancia de Azure Synapse Analytics, debe asignar un nombre a un inicio de sesión de administrador del servidor. El servidor de SQL crea esa cuenta como inicio de sesión en la base de datos de master. Esta cuenta se conecta mediante la autenticación de SQL Server (nombre de usuario y contraseña). Solo puede existir una de estas cuentas.

• Administrador de SQL Active Directory

  Una cuenta de Microsoft Entra, individual o una cuenta de grupo de seguridad, también se puede configurar como administrador. Es opcional configurar un administrador de Microsoft Entra, pero debe configurarse un administrador de Microsoft Entra si desea usar cuentas de Microsoft Entra para conectarse a Synapse SQL.

  • La cuenta de administrador de Microsoft Entra controla el acceso a grupos de SQL dedicados, mientras que los roles RBAC de Synapse se usan para controlar el acceso a grupos sin servidor, por ejemplo, con el rol administrador de Synapse y administrador de Synapse SQL.

Las cuentas de nombre de usuario administrador de SQL y administrador de SQL Active Directory tienen las siguientes características:

• Son las únicas cuentas que pueden conectarse automáticamente a cualquier instancia de SQL Database en el servidor. (Para conectarse a una base de datos de usuario, las otras cuentas deben ser el propietario de la base de datos o tener una cuenta de usuario en la base de datos de usuario).
• Acceden a las bases de datos de usuario como usuario dbo y tienen todos los permisos en las bases de datos de usuario. (El propietario de una base de datos de usuario también accede a la base de datos como usuario dbo).
• No acceden a la base de datos master como usuario dbo y tienen permisos limitados en la base de datos master.
• No son miembros del rol de servidor fijo sysadmin de SQL Server estándar, que no está disponible en SQL Database.
• Puede crear, modificar y quitar bases de datos, inicios de sesión, usuarios de la base de datos master y reglas de firewall de IP de nivel de servidor.
• Pueden agregar y quitar miembros en los roles dbmanager y loginmanager.
• Pueden ver la tabla del sistema sys.sql_logins.

Nota:

Si un usuario está configurado como administrador de Active Directory y administrador de Synapse y, luego, se quita del rol de administrador de Active Directory, perderá el acceso a los grupos de SQL dedicados en Synapse. Para que recupere el acceso a los grupos de SQL dedicados, se deberá quitar del rol de administrador de Synapse y, luego, volver a agregarlo.

Grupo de SQL sin servidor

Para administrar los usuarios que tienen acceso al grupo de SQL sin servidor, puede usar las instrucciones siguientes.

Para crear un inicio de sesión en el grupo de SQL sin servidor, use la siguiente sintaxis:

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
-- or
CREATE LOGIN [Mary@domainname.net] FROM EXTERNAL PROVIDER;

Cuando exista el inicio de sesión, puede crear usuarios en las distintas bases de datos del punto de conexión del grupo de SQL sin servidor y concederles los permisos necesarios. Para crear un usuario, puede utilizar la siguiente sintaxis:

CREATE USER Mary FROM LOGIN Mary;
-- or
CREATE USER Mary FROM LOGIN Mary@domainname.net;
-- or
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Una vez que se crean el inicio de sesión y el usuario, puede usar la sintaxis de SQL Server normal para conceder derechos.

Grupo de SQL dedicado

Ruta de acceso de administrador

Cuando el firewall de nivel de área de trabajo está configurado correctamente, el administrador de SQL Server y el administrador de SQL Azure Active Directory pueden conectarse mediante herramientas de cliente como SQL Server Management Studio o SQL Server Data Tools. Solo las herramientas más recientes proporcionan todas las características y funcionalidades.

El diagrama siguiente muestra una configuración típica para las dos cuentas de administrador:

Si se usa un puerto abierto en el firewall de nivel de servidor, los administradores pueden conectarse a cualquier SQL Database.

Creadores de bases de datos

Uno de estos roles administrativos es dbmanager. Los miembros de este rol pueden crear bases de datos nuevas. Para usar este rol, cree un usuario en la base de datos master y, después, agréguelo al rol de base de datos dbmanager.

Para crear una base de datos, el usuario debe haber iniciado sesión con SQL Server en la base de datos master o ser un usuario de una base de datos independiente basado en Microsoft Entra.

1. Con una cuenta de administrador, conéctese a la base de datos master.

2. crear un inicio de sesión de autenticación de SQL Server mediante la instrucción CREATE LOGIN. Instrucción de ejemplo:

   CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';
   

   Nota

   Al crear un inicio de sesión o un usuario de una base de datos independiente, use una contraseña segura. Para obtener más información, consulte Strong Passwords.

   Para mejorar el rendimiento, los inicios de sesión (entidades de nivel de servidor) se almacenan temporalmente en caché en el nivel de base de datos. Para actualizar la caché de autenticación, consulte DBCC FLUSHAUTHCACHE.

3. Cree un usuario de base de datos mediante la instrucción CREATE USER. El usuario puede ser un usuario de base de datos independiente de autenticación de Microsoft Entra (si ha configurado el entorno para la autenticación de Microsoft Entra), un usuario de base de datos independiente de autenticación de SQL Server o un usuario de autenticación de SQL Server que usa un inicio de sesión de autenticación de SQL Server (creado en el paso anterior).

   Instrucciones de ejemplo:

   Cree un usuario con Microsoft Entra ID:

   CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER; 
   

   Cree un usuario de base de datos independiente de SQL Database:

   CREATE USER Ann WITH PASSWORD = '<strong_password>';
   

   Cree un usuario de SQL Server basado en un inicio de sesión de autenticación de SQL Server:

   CREATE USER Mary FROM LOGIN Mary;
   

4. Agregue el nuevo usuario al rol de base de datos dbmanager en master mediante el procedimiento sp_addrolemember (tenga en cuenta que no se admite la instrucción ALTER ROLE en SQL aprovisionado). Instrucciones de ejemplo:

   EXEC sp_addrolemember 'dbmanager', 'Mary'; 
   EXEC sp_addrolemember 'dbmanager', 'mike@contoso.com]'; 
   

   Nota

   El rol dbmanager es un rol de base de datos de la base de datos master, por lo que solo puede agregar un usuario de la base de datos al rol dbmanager. No puede agregar un inicio de sesión de nivel de servidor al rol de nivel de base de datos.

5. Si es necesario, configure una regla de firewall para permitir que el nuevo usuario se conecte. (El nuevo usuario puede estar cubierto por una regla de firewall existente).

Ahora, el usuario puede conectarse a la base de datos master y puede crear nuevas bases de datos. La cuenta que crea la base de datos se convierte en la propietaria de la base de datos.

Administradores de inicio de sesión

El otro rol administrativo es el rol de administrador de inicio de sesión. Los miembros de este rol pueden crear nuevos inicios de sesión en la base de datos master. Si lo desea, puede realizar los mismos pasos (crear un inicio de sesión y un usuario, y agregar un usuario al rol loginmanager) para que los usuarios puedan crear nuevos inicios de sesión en la base de datos maestra. Normalmente los inicios de sesión no son necesarios, ya que Microsoft recomienda utilizar los usuarios de base de datos independiente que se autenticarán en el nivel de base de datos, en lugar de utilizar los usuarios basándose en los inicios de sesión. Para obtener más información, vea Usuarios de base de datos independiente: hacer que la base de datos sea portátil.

Usuarios no administradores

Por lo general, las cuentas que no son de administrador no necesitan acceso a la base de datos master. Cree usuarios de una base de datos independiente en el nivel de base de datos con la instrucción CREATE USER (Transact-SQL) .

El usuario puede ser un usuario de base de datos independiente de Microsoft Entra (si ha configurado el entorno para la autenticación de Microsoft Entra), o un usuario de base de datos independiente de autenticación de SQL Server o un usuario de autenticación de SQL Server basándose en un inicio de sesión de autenticación de SQL Server (creado en el paso anterior).

Para crear usuarios, conectarse a la base de datos y ejecutar instrucciones similares a los siguientes ejemplos:

CREATE USER Mary FROM LOGIN Mary;
CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

Inicialmente, solo pueden crear usuarios uno de los administradores o el propietario de la base de datos. Para autorizar que usuarios adicionales creen nuevos usuarios, conceda al usuario seleccionado el permiso ALTER ANY USER , para lo que debe emplear una instrucción como:

GRANT ALTER ANY USER TO Mary;

Para proporcionar a otros usuarios un control total de la base de datos, conviértalos en miembros del rol fijo de base de datos db_owner.

En Azure SQL Database o Synapse sin servidor, use la instrucción ALTER ROLE.

ALTER ROLE db_owner ADD MEMBER Mary;

En un grupo de SQL dedicado, use EXEC sp_addrolemember.

EXEC sp_addrolemember 'db_owner', 'Mary';

Nota

Un motivo habitual para crear un usuario de base de datos basado en un inicio de sesión de servidor es que algunos usuarios necesitan acceder a varias bases de datos. Como los usuarios de base de datos son entidades individuales, cada una de ellas tiene un usuario y contraseña propios. Esto puede producir una sobrecarga, ya que el usuario debe recordar las contraseñas de las bases de datos, y volverse insostenible al tener que cambiar la contraseña de numerosas bases de datos.

Grupos y roles

La administración de acceso eficiente utiliza los permisos asignados a grupos y roles, en lugar de a usuarios individuales.

• Al usar la autenticación de Microsoft Entra, coloque los usuarios de Microsoft Entra en un grupo de Microsoft Entra. Cree un usuario de base de datos independiente para el grupo. Coloque uno o varios usuarios de base de datos en un rol de base de datos y, luego, asigne permisos al rol de base de datos.

• Si utilizar la autenticación de SQL Server, cree usuarios de base de datos contenidos en la base de datos. Coloque uno o varios usuarios de base de datos en un rol de base de datos y, luego, asigne permisos al rol de base de datos.

Los roles de base de datos pueden ser roles integrados como db_owner, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter y db_denydatareader. db_owner se usa habitualmente para conceder permiso completo solo a algunos usuarios. Los restantes roles fijos de base de datos son útiles para obtener rápidamente una base de datos simple en la fase de desarrollo, pero no se recomiendan para la mayoría de las bases de datos de producción.

Por ejemplo, el rol fijo de base de datos db_datareader concede acceso de lectura a todas las tablas de la base de datos, algo que normalmente es más de lo estrictamente necesario.

Es mucho mejor usar la instrucción CREATE ROLE para crear sus propios roles de base de datos definidos por el usuario y otorgar cuidadosamente a cada rol los permisos mínimos necesarios para la necesidad empresarial. Cuando un usuario es miembro de varios roles, realiza la agregación de los permisos de todos ellos.

Permisos

En SQL Database, hay más de 100 permisos que pueden conceder o denegar individualmente. Muchos de estos permisos están anidados. Por ejemplo, el permiso UPDATE de un esquema incluye el permiso UPDATE en cada tabla dentro de dicho esquema. Al igual que en la mayoría de los sistemas de permisos, la denegación de un permiso anula su concesión.

Dada la naturaleza anidada y el número de permisos, es preciso realizar un estudio meticuloso para diseñar un sistema de permisos apropiado para proteger adecuadamente la base de datos.

Empiece con la lista de permisos de Permisos (motor de base de datos) y revise el gráfico a tamaño de póster de los permisos del motor de base de datos.

Consideraciones y restricciones

Al administrar los inicios de sesión y los usuarios en SQL Database, tenga en cuenta los siguientes puntos:

• Debe estar conectado a la base de datos master cuando ejecute las instrucciones CREATE/ALTER/DROP DATABASE.
• El usuario de la base de datos correspondiente al inicio de sesión del administrador de servidor no se puede modificar ni quitar.
• El administrador del servidor se deshabilitará si solo la autenticación de Microsoft Entra está habilitada.
• El inglés de Estados Unidos es el idioma predeterminado del inicio de sesión del administrador del servidor.
• Solamente los administradores (inicio de sesión de administrador de servidor o de administrador de Microsoft Entra) y los miembros del rol de base de datos dbmanager de la base de datos master tienen permiso para ejecutar las instrucciones CREATE DATABASE y DROP DATABASE.
• Debe estar conectado con la base de datos master al ejecutar la instrucción CREATE/ALTER/DROP LOGIN. Sin embargo, el uso de inicios de sesión no es recomendable. En su lugar, utilice los usuarios de la base de datos independiente. Para obtener más información, vea Usuarios de base de datos independiente - Conversión de la base de datos en portátil.
• Para conectarse a una base de datos de usuario, debe proporcionar el nombre de la base de datos de la cadena de conexión.
• Solamente el inicio de sesión principal del nivel de servidor y los miembros del rol de base de datos loginmanager de la base de datos master tienen permiso para ejecutar las instrucciones CREATE LOGIN, ALTER LOGIN y DROP LOGIN.
• Al ejecutar las instrucciones CREATE/ALTER/DROP LOGIN y CREATE/ALTER/DROP DATABASE en una aplicación ADO.NET, no se permite el uso de comandos con parámetros. Para obtener más información, consulte Comandos y parámetros.
• Cuando se ejecuta la instrucción CREATE USER con la opción FOR/FROM LOGIN, debe ser la única instrucción de un lote de Transact-SQL.
• Cuando se ejecuta la instrucción ALTER USER con la opción WITH LOGIN, debe ser la única instrucción de un lote de Transact-SQL.
• Las instrucciones CREATE/ALTER/DROP LOGIN y CREATE/ALTER/DROP USER no se admiten cuando solo la autenticación de Microsoft Entra está habilitada para el área de trabajo de Azure Synapse.
• Para CREATE/ALTER/DROP un usuario requiere el permiso ALTER ANY USER de la base de datos.
• Cuando el propietario de un rol de base de datos intenta agregar otro usuario de base de datos a ese rol o bien quitarlo, se puede producir el siguiente error: El usuario o el rol "Nombre" no existe en esta base de datos. Este error se produce porque el usuario no es visible para el propietario. Para resolver este problema, conceda al propietario del rol el permiso VIEW DEFINITION del usuario.

Pasos siguientes

Para obtener más información, vea Usuarios de base de datos independiente: hacer que la base de datos sea portátil.