Control del acceso a la cuenta de almacenamiento del grupo de SQL sin servidor en Azure Synapse Analytics

  • Artículo

Una consulta del grupo de SQL sin servidor lee los archivos directamente desde Azure Storage. Los permisos para tener acceso a los archivos en Azure Storage se controlan en dos niveles:

  • Nivel de almacenamiento: el usuario debe tener permiso de acceso a los archivos de almacenamiento subyacentes. El administrador de almacenamiento debe permitir que la entidad de seguridad de Microsoft Entra lea y escriba archivos o que genere una clave SAS (firma de acceso compartido) que se usará para acceder al almacenamiento.
  • Nivel de servicio de SQL: el usuario debe tener permiso para leer datos mediante una tabla externa o para ejecutar la función OPENROWSET. Conozca más información sobre los permisos necesarios en esta sección.

En este artículo se describen los tipos de credenciales que puede usar y cómo se realiza la búsqueda de credenciales para los usuarios de SQL y Microsoft Entra.

Permisos de almacenamiento

Un grupo de SQL sin servidor en un área de trabajo de Synapse Analytics puede leer el contenido de los archivos almacenados en Azure Data Lake Storage. Es necesario configurar permisos en el almacenamiento para que un usuario que ejecute una consulta SQL pueda leer los archivos. Hay tres métodos para habilitar el acceso a los archivos:

  • El control de acceso basado en rol (RBAC) permite asignar un rol a usuarios de Microsoft Entra en el inquilino en el que reside el almacenamiento. Un lector debe ser miembro del rol Lector de datos de Storage Blob, Colaborador de datos de Storage Blob o Propietario de datos de Storage Blob en la cuenta de almacenamiento. Un usuario que escriba datos en Azure Storage debe ser miembro del rol Colaborador de datos de Storage Blob o Propietario de datos de Storage Blob. El rol de propietario de datos no implica que un usuario también sea el propietario de los datos de almacenamiento.
  • Las listas de control de acceso (ACL) le permiten definir permisos de lectura(R), escritura(W) y ejecución(X) detallados sobre los archivos y directorios de almacenamiento de Azure. La ACL se puede asignar a los usuarios de Microsoft Entra. Si los lectores quieren leer un archivo de una ruta de acceso de Azure Storage, deben tener permiso para ejecutar(X) la ACL en cada carpeta de la ruta de acceso del archivo y para leer(R) la ACL en el archivo. Más información sobre cómo establecer permisos de ACL en la capa de almacenamiento.
  • La firma de acceso compartido (SAS) permite que un lector acceda a los archivos de Azure Data Lake Storage mediante el token de tiempo limitado. El lector ni siquiera necesita autenticarse como usuario de Microsoft Entra. El token de SAS contiene los permisos concedidos al lector, así como el período durante el cual es válido el token. El token de SAS es una buena opción para el acceso con restricciones de tiempo en el caso de los usuarios que ni siquiera tienen que estar en el mismo inquilino de Microsoft Entra. El token de SAS se puede definir en la cuenta de almacenamiento o en directorios específicos. Obtenga más información sobre el acceso limitado a recursos de Azure Storage mediante el uso de firmas de acceso compartido.

Como alternativa, puede hacer que los archivos estén disponibles públicamente permitiendo el acceso anónimo. Este enfoque NO debe usarse si tiene datos que no son públicos.

Tipos de autorización de almacenamiento admitidos

Un usuario que haya iniciado sesión en un grupo de SQL sin servidor debe estar autorizado para acceder y consultar los archivos de Azure Storage si los archivos no están disponibles públicamente. Puede utilizar cuatro tipos de autorización para acceder al almacenamiento no público: Identidad de usuario, Firma de acceso compartido, Entidad de servicio e Identidad administrada.

Nota:

Tránsito de Microsoft Entra es el comportamiento predeterminado cuando se crea un área de trabajo.

La Identidad de usuario, también conocida como "tránsito de Microsoft Entra" es un tipo de autorización en el que la identidad del usuario de Microsoft Entra que inició sesión en el grupo de SQL sin servidor se utiliza para autorizar el acceso a los datos. Antes de acceder a los datos, el administrador de Azure Storage debe conceder permisos al usuario de Microsoft Entra. Como se indica en la tabla Tipos de autorización admitidos para usuarios de base de datos, no se admite para el tipo de usuario de SQL.

Importante

Las aplicaciones cliente pueden almacenar en caché un token de autenticación de Microsoft Entra. Por ejemplo, Power BI almacena en caché tokens de Microsoft Entra y reutiliza el mismo token durante una hora. Puede producirse un error en las consultas de larga duración si el token expira en mitad de la ejecución de la consulta. Si experimenta errores de consulta causados por la expiración del token de acceso de Microsoft Entra en mitad de la consulta, cambie a la entidad de servicio, a la identidad administrada o a la firma de acceso compartido.

Debe ser miembro del rol Propietario de datos de Storage Blob, Colaborador de datos de Storage Blob o Lector de datos de Storage Blob para usar su identidad para acceder a los datos. Como alternativa, puede establecer reglas de ACL específicas para acceder a archivos y carpetas. Incluso si es propietario de una cuenta de almacenamiento, deberá agregarse a uno de los roles de datos del blob de almacenamiento. Para más información sobre el control de acceso en Azure Data Lake Store Gen2, revise el artículo Control de acceso en Azure Data Lake Storage Gen2.

Escenarios de varios inquilinos

En los casos en que Azure Storage se encuentra en un inquilino diferente del grupo de SQL sin servidor de Synapse, la autorización a través de la entidad de servicio es el método recomendado. También puede usar la autorización SAS, pero tenga en cuenta que la Identidad administrada no es compatible.

Tipo de autorización Almacenamiento protegido por firewall Almacenamiento protegido sin firewall
SAS Compatible Compatible
Entidad de seguridad de servicio No compatible Compatible

Nota

Si Azure Storage está protegido por un firewall de Azure Storage, la entidad de servicio no será admitida.

Tipos de autorización admitidos para usuarios de bases de datos

En la siguiente tabla se proporcionan los tipos de autorización de Azure Storage disponibles para diferentes métodos de inicio de sesión en un punto de conexión SQL sin servidor de Azure Synapse Analytics:

Tipo de autorización Usuario de SQL Usuario de Microsoft Entra Entidad de servicio
Identidad de usuario No compatible Compatible Compatible
SAS Compatible Compatible Compatible
Entidad de servicio Compatible Compatible Compatible
Identidad administrada Compatible Compatible Compatible

Tipos de almacenamiento y autorización admitidos

Puede usar las siguientes combinaciones de tipos de autorización y tipos de Azure Storage:

Tipo de autorización Blob Storage ADLS Gen1 ADLS Gen2
SAS Compatible No compatible Compatible
Entidad de servicio Compatible Compatible Compatible
Identidad administrada Compatible Compatible Compatible
Identidad de usuario Compatible Compatible Compatible

Escenarios de varios inquilinos

En los casos en que Azure Storage se encuentra en un inquilino diferente del grupo de SQL sin servidor de Azure Synapse Analytics, la autorización a través de la entidad de servicio es el método recomendado. También es posible la autorización de firma de acceso compartido. No se admite la identidad de servicio administrada.

Tipo de autorización Almacenamiento protegido por firewall Almacenamiento protegido sin firewall
SAS Compatible Compatible
Entidad de servicio No compatible Compatible

Nota

Si Azure Storage está protegido por un firewall de Azure Storage, y está en otro inquilino, la entidad de servicio no será admitida. En su lugar, use un token de firma de acceso compartido (SAS).

Almacenamiento protegido por firewall

Puede configurar cuentas de almacenamiento para permitir el acceso a un grupo de SQL sin servidor específico mediante la creación de una regla de instancia de recursos. Al acceder al almacenamiento protegido con firewall, solo use una Identidad de usuario o Identidad administrada.

Nota

La característica de firewall de Azure Storage está en versión preliminar pública y está disponible en todas las regiones de la nube pública.

En la siguiente tabla se proporcionan los tipos de autorización de Azure Storage protegidos por firewall disponibles para diferentes métodos de inicio de sesión en un punto de conexión SQL sin servidor de Azure Synapse Analytics:

Tipo de autorización Usuario de SQL Usuario de Microsoft Entra Entidad de servicio
Identidad de usuario No compatible Compatible Compatible
SAS No compatible No compatible No compatible
Entidad de servicio No compatible No compatible No compatible
Identidad administrada Compatible Compatible Compatible

Para acceder a un almacenamiento protegido por el firewall por medio de una identidad de usuario, puede usar Azure Portal o el módulo de PowerShell Az.Storage.

Configuración del firewall de Azure Storage a través de Azure Portal

  1. Busque su cuenta de almacenamiento en Azure Portal.
  2. En el menú de navegación principal, vaya a Redes en Configuración.
  3. En la sección Instancias de recursos, agregue una excepción para el área de trabajo de Azure Synapse.
  4. Seleccione Microsoft.Synapse/workspaces como un Tipo de recurso.
  5. Seleccione el nombre del área de trabajo como un Nombre de instancia.
  6. Seleccione Guardar.

Configuración del firewall de Azure Storage a través de PowerShell

Siga estos pasos para configurar la cuenta de almacenamiento y agregar una excepción para el área de trabajo de Azure Synapse.

  1. Abra o instale PowerShell.

  2. Instale las versiones más recientes de los módulos Az.Storage y Az.Synapse, por ejemplo en el siguiente script:

    Install-Module -Name Az.Storage -RequiredVersion 3.4.0
Install-Module -Name Az.Synapse -RequiredVersion 0.7.0

    Importante

    Asegúrese de usar como mínimo la versión 3.4.0. Puede comprobar la versión de Az.Storage con este comando:

    Get-Module -ListAvailable -Name Az.Storage | Select Version

  3. Conéctese a su inquilino de Azure:

    Connect-AzAccount

  4. Defina las variables en PowerShell:

    • Nombre del grupo de recursos: puede encontrarlo en Azure Portal, en la Información general de la cuenta de almacenamiento.
    • Nombre de cuenta: nombre de la cuenta de almacenamiento que está protegida por reglas de firewall.
    • Id.de inquilino: puede encontrarlo en Azure Portal en Microsoft Entra ID, bajo Propiedades, en Propiedades del inquilino.
    • Nombre del área de trabajo: nombre del área de trabajo de Azure Synapse.
        $resourceGroupName = "<resource group name>"
    $accountName = "<storage account name>"
    $tenantId = "<tenant id>"
    $workspaceName = "<Azure Synapse workspace name>"

    $workspace = Get-AzSynapseWorkspace -Name $workspaceName
    $resourceId = $workspace.Id
    $index = $resourceId.IndexOf("/resourceGroups/", 0)
    # Replace G with g - /resourceGroups/ to /resourcegroups/
    $resourceId = $resourceId.Substring(0,$index) + "/resourcegroups/" ` 
        + $resourceId.Substring($index + "/resourceGroups/".Length)

    $resourceId

    Importante

    El valor del $resourceid devuelto por el script de PowerShell debe coincidir con esta plantilla: /subscriptions/{subscription-id}/resourcegroups/{resource-group}/providers/Microsoft.Synapse/workspaces/{name-of-workspace} Es importante escribir resourcegroups en minúsculas.

  5. Agregue una regla de red de la cuenta de almacenamiento de Azure:

        $parameters = @{
        ResourceGroupName = $resourceGroupName
        Name = $accountName
        TenantId = $tenantId 
        ResourceId = $resourceId
    }

    Add-AzStorageAccountNetworkRule @parameters

  6. Compruebe que la regla de red de la cuenta de almacenamiento se aplicó en el firewall de la cuenta de almacenamiento. El siguiente script de PowerShell compara la variable $resourceid de los pasos anteriores con la salida de la regla de red de la cuenta de almacenamiento.

        $parameters = @{
        ResourceGroupName = $resourceGroupName
        Name = $accountName
    }

    $rule = Get-AzStorageAccountNetworkRuleSet @parameters
    $rule.ResourceAccessRules | ForEach-Object { 
        if ($_.ResourceId -cmatch "\/subscriptions\/(\w\-*)+\/resourcegroups\/(.)+") { 
            Write-Host "Storage account network rule is successfully configured." -ForegroundColor Green
            $rule.ResourceAccessRules
        } else {
            Write-Host "Storage account network rule is not configured correctly. Remove this rule and follow the steps in detail." -ForegroundColor Red
            $rule.ResourceAccessRules
        }
    }

Credenciales

Para consultar un archivo ubicado en Azure Storage, el punto de conexión del grupo de SQL sin servidor necesita una credencial que contenga la información de autenticación. Se usan dos tipos de credenciales:

  • La credencial de nivel de servidor se utiliza para las consultas ad hoc ejecutadas mediante la función OPENROWSET. El nombre de la credencial debe coincidir con la URL de almacenamiento.
  • Para las tablas externas se usa una credencial con ámbito en la base de datos. La tabla externa hace referencia a DATA SOURCE con la credencial que se debe usar para tener acceso al almacenamiento.

Concesión de permisos para administrar credenciales

Para conceder la capacidad de administrar las credenciales:

  • Para permitir a un usuario crear o anular una credencial a nivel de servidor, un administrador debe concederle el permiso ALTER ANY CREDENTIAL a su inicio de sesión en la base de datos maestra. Por ejemplo:

    GRANT ALTER ANY CREDENTIAL TO [login_name];

  • Para permitir a un usuario crear o anular una credencial con ámbito en la base de datos, un administrador debe concederle el permiso CONTROL en la base de datos al usuario de la base de datos en la base de datos del usuario. Por ejemplo:

    GRANT CONTROL ON DATABASE::[database_name] TO [user_name];

Concesión de permisos para usar credenciales

Los usuarios de bases de datos que tienen acceso a almacenamiento externo deben tener permiso para usar las credenciales. Para usar la credencial, un usuario debe tener el permiso REFERENCES en una credencial específica.

Para conceder el permiso REFERENCES en una credencial a nivel de servidor para el inicio de sesión, use la siguiente consulta T-SQL en la base de datos maestra:

GRANT REFERENCES ON CREDENTIAL::[server-level_credential] TO [login_name];

Para conceder un permiso REFERENCES en una credencial con ámbito en la base de datos para un usuario de base de datos, use la siguiente consulta T-SQL en la base de datos del usuario:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[database-scoped_credential] TO [user_name];

Credencial de nivel de servidor

Las credenciales de nivel de servidor se usan cuando un inicio de sesión de SQL llama a la función OPENROWSET sin DATA_SOURCE para leer archivos en una cuenta de almacenamiento.

El nombre de la credencial de nivel de servidor debe coincidir con la dirección URL base de Azure Storage. seguida opcionalmente por un nombre de contenedor. Para agregar una credencial, ejecute CREATE CREDENTIAL. Debe proporcionar el argumento CREDENTIAL NAME.

Nota

El argumento FOR CRYPTOGRAPHIC PROVIDER no se admite.

El nombre de la CREDENCIAL de nivel de servidor debe coincidir con el siguiente formato: <prefix>://<storage_account_path>[/<container_name>]. Las rutas de acceso de la cuenta de almacenamiento se describen en la tabla siguiente:

Origen de datos externo Prefijo Ruta de acceso a la cuenta de almacenamiento
Azure Blob Storage https <storage_account>.blob.core.windows.net
Azure Data Lake Storage Gen1 https <storage_account>.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Storage Gen2 https <storage_account>.dfs.core.windows.net

Las credenciales de nivel de servidor permiten entonces el acceso a Azure Storage mediante los siguientes tipos de autenticación:

Los usuarios de Microsoft Entra pueden acceder a cualquier archivo de Azure Storage si son miembros del rol Propietario de datos de Storage Blob, Colaborador de datos de Storage Blob o Lector de datos de Storage Blob. Los usuarios de Microsoft Entra no necesitan credenciales para acceder al almacenamiento.

Los usuarios de SQL autenticados no pueden utilizar la autenticación de Microsoft Entra para acceder al almacenamiento. Pueden acceder al almacenamiento con una credencial de base de datos mediante identidad administrada, clave de SAS, entidad de servicio o si hay acceso público al almacenamiento.

Credencial con ámbito en la base de datos

Las credenciales con ámbito en la base de datos se usan cuando cualquier entidad de seguridad llama a la función OPENROWSET con DATA_SOURCE o selecciona datos de tabla externa que no tienen acceso a archivos públicos. No es necesario que la credencial con ámbito en la base de datos coincida con el nombre de la cuenta de almacenamiento, está referenciada en el ORIGEN DE DATOS que define la ubicación del almacenamiento.

Las credenciales con ámbito en la base de datos permiten el acceso a Azure Storage mediante los siguientes tipos de autenticación:

Los usuarios de Microsoft Entra pueden acceder a cualquier archivo de Azure Storage si son miembros del rol Propietario de datos de Storage Blob, Colaborador de datos de Storage Blob o Lector de datos de Storage Blob. Los usuarios de Microsoft Entra no necesitan credenciales para acceder al almacenamiento.

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
)

Los usuarios de SQL autenticados no pueden utilizar la autenticación de Microsoft Entra para acceder al almacenamiento. Pueden acceder al almacenamiento con una credencial de base de datos mediante identidad administrada, clave de SAS, entidad de servicio o si hay acceso público al almacenamiento.

Las credenciales con ámbito en la base de datos se usan en orígenes de datos externos para especificar el método de autenticación que se utilizará para tener acceso a este almacenamiento:

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>',
          CREDENTIAL = <name of database scoped credential> 
)

Ejemplos

Acceso a un origen de datos disponible públicamente

Use el siguiente script para crear una tabla que tenga acceso al origen de datos disponible públicamente.

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]
       WITH ( FORMAT_TYPE = PARQUET)
GO
CREATE EXTERNAL DATA SOURCE publicData
WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<public_container>/<path>' )
GO

CREATE EXTERNAL TABLE dbo.userPublicData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [publicData],
       FILE_FORMAT = [SynapseParquetFormat] )

El usuario de la base de datos puede leer el contenido de los archivos desde el origen de datos mediante la tabla externa o la función OPENROWSET que hace referencia al origen de datos:

SELECT TOP 10 * FROM dbo.userPublicData;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet',
                                DATA_SOURCE = 'mysample',
                                FORMAT='PARQUET') as rows;
GO

Acceso a un origen de datos mediante credenciales

Modifique el script siguiente para crear una tabla externa que tenga acceso a Azure Storage mediante el token de SAS, la identidad de Microsoft Entra del usuario o la identidad administrada del área de trabajo.

-- Create master key in databases with some password (one-off per database)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>'
GO

-- Create databases scoped credential that use Managed Identity, SAS token or service principal. User needs to create only database-scoped credentials that should be used to access data source:

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity'
GO
CREATE DATABASE SCOPED CREDENTIAL SasCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-10-1********ZVsTOL0ltEGhf54N8KhDCRfLRI%3D'
GO
CREATE DATABASE SCOPED CREDENTIAL SPNCredential WITH
IDENTITY = '**44e*****8f6-ag44-1890-34u4-22r23r771098@https://login.microsoftonline.com/**do99dd-87f3-33da-33gf-3d3rh133ee33/oauth2/token' 
, SECRET = '.7OaaU_454azar9WWzLL.Ea9ePPZWzQee~'
GO
-- Create data source that one of the credentials above, external file format, and external tables that reference this data source and file format:

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] WITH ( FORMAT_TYPE = PARQUET)
GO

CREATE EXTERNAL DATA SOURCE mysample
WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
-- Uncomment one of these options depending on authentication method that you want to use to access data source:
--,CREDENTIAL = WorkspaceIdentity 
--,CREDENTIAL = SasCredential 
--,CREDENTIAL = SPNCredential
)

CREATE EXTERNAL TABLE dbo.userData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [mysample],
       FILE_FORMAT = [SynapseParquetFormat] );

El usuario de la base de datos puede leer el contenido de los archivos desde el origen de datos mediante la tabla externa o la función OPENROWSET que hace referencia al origen de datos:

SELECT TOP 10 * FROM dbo.userdata;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet', DATA_SOURCE = 'mysample', FORMAT='PARQUET') as rows;
GO

Pasos siguientes

Estos artículos le ayudarán a comprender cómo consultar diferentes tipos de carpeta y de archivo, además de cómo crear y usar vistas: