Como administrador de SQL Server, puede haber momentos en los que necesite crear usuarios y asignar permisos de forma programática. Esto puede ser especialmente útil cuando se trabaja con bases de datos SQL de Azure en un grupo elástico. En este artículo, exploraremos cómo lograr esto utilizando Azure Service Principal y el módulo de administración de secretos en PowerShell.

¿Qué es un Azure Service Principal?

Un Azure Service Principal es una identidad creada para su uso con aplicaciones, servicios hospedados y herramientas automatizadas para acceder a recursos de Azure. Le permite administrar el acceso al servidor SQL, siempre y cuando la identidad de Azure AD del servidor SQL tenga privilegios de lector de directorio.

Uso de Azure Key Vault para almacenamiento seguro

De forma predeterminada, el módulo de administración de secretos utiliza el Key Vault predeterminado, que está limitado a su máquina local y al usuario que ejecuta el código. Sin embargo, se recomienda utilizar Azure Key Vault para almacenar información confidencial, como los detalles del Service Principal. Esto garantiza que la información se almacene de forma segura en la nube y pueda ser accedida por usuarios o aplicaciones autorizadas.

Para utilizar Azure Key Vault con el módulo de administración de secretos, primero debe registrarlo. Esto implica proporcionar el nombre local para el key vault, el nombre del módulo (Az.KeyVault) y el ID de suscripción de Azure. Una vez registrado, puede agregar los detalles del Service Principal al Azure Key Vault utilizando el módulo de administración de secretos.

Conexión a Azure SQL Database con un Azure Service Principal

Para conectarse a Azure SQL Database con un Azure Service Principal, puede utilizar el módulo de PowerShell dbatools. Este módulo de colaboración de la comunidad de código abierto proporciona una forma conveniente de administrar SQL Server. Al recuperar los detalles del Service Principal del Azure Key Vault, puede crear una conexión y ejecutar consultas como el Service Principal.

Código de ejemplo:

$SqlInstance = 'temp-beard-sqls.database.windows.net'
$databasename = 'master'
$appid = Get-Secret -Vault BeardKeyVault -Name service-principal-guid -AsPlainText
$Clientsecret = Get-Secret -Vault BeardKeyVault -Name service-principal-secret
$credential = New-Object System.Management.Automation.PSCredential ($appid,$Clientsecret)
$tenantid = Get-Secret -Vault BeardKeyVault -Name Sewells-Tenant-Id -AsPlainText
$AzureSQL = Connect-DbaInstance -SqlInstance $SqlInstance -Database $databasename -SqlCredential $credential -Tenant $tenantid -TrustServerCertificate
Invoke-DbaQuery -SqlInstance $AzureSql -Database master -SqlCredential $credential -Query "SELECT SUSER_NAME() AS 'username'"

Agregar un usuario a la base de datos de usuario

Para agregar un usuario a la base de datos de usuario, debe crear una nueva conexión a la base de datos de usuario. Desafortunadamente, no puede utilizar la instrucción “USE [NombreBaseDatos]” en este caso. Una vez conectado, puede ejecutar un script T-SQL para crear un usuario de base de datos contenido en la base de datos. También puede agregar membresía de roles utilizando el cmdlet Add-DbaDbRoleMember del módulo dbatools.

Código de ejemplo:

$Userdatabasename = 'temp-sql-db-beard'
$AzureSQL = Connect-DbaInstance -SqlInstance $SqlInstance -Database $Userdatabasename -SqlCredential $credential -Tenant $tenantid -TrustServerCertificate

$Query = @"
CREATE USER [rob@sewells-consulting.co.uk] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [rob@sewells-consulting.co.uk]
"@

Invoke-DbaQuery -SqlInstance $AzureSql -Database $Userdatabasename -SqlCredential $credential -Query $Query

Get-DbaDbUser -SqlInstance $AzureSql -Database $Userdatabasename | Out-GridView

Siguiendo estos pasos, puede crear usuarios y asignar permisos en SQL Server de forma programática utilizando Azure Service Principal y el módulo de administración de secretos. Esto permite una gestión eficiente del acceso a sus bases de datos SQL de Azure.

¡Feliz automatización!