¡Hola a tod@s!

Después de un tiempo sin escribir debido a motivos laborales y el máster de ciberseguridad a punto de finalizar, nos aventuraremos a realizar algo en lo que no encontré mucha documentación en la web, pero necesario para mejorar la seguridad en los mismos y centralizar la autenticación en AAD, exactamente explicaré cómo realizar una autenticación mediante un Service Principal en Azure SQL haciendo uso de nuestra gran arma: PowerShell.

Debemos de tener generado nuestro Service Principal, en caso de que así no sea, es tan sencillo como dirigirnos a Azure Active Directory y registrar una nueva aplicación de tipo Web App/API indicando un URI Endpoint cualquiera:


Apuntamos el Application Id que nos genera, además debemos de obtener un Secret, para ello nos vamos al apartado de Keys y generamos 1 con la fecha de expiración que deseemos apuntando ambos valores:

Vale, pero… ¿Cómo va a reconocer el acceso mi Azure SQL si no lo he indicado en ningún sitio?

¡Vamos con ello!

Entramos en nuestra DB con un usuario con autenticación básica que sea administrador de la DB y ejecutamos la siguiente query T-SQL para crear el usuario desde un proveedor externo, le ponemos el rol que nos interese, en nuestro caso lo dejamos como db_owner:

CREATE USER [ApplicationName] FROM EXTERNAL PROVIDER
GO
ALTER ROLE db_owner ADD MEMBER[ApplicationName]

Para llegar a nuestro objetivo, tenemos que estudiar las diferentes posibilidades:

  • Invoke-SQLCMD: No permite modificar en el ConnectionString la posibilidad de añadirle un token de acceso, además, el campo Authentication cuenta con:
    • Autenticación integrada (hace uso de SSO y nuestro cliente unido a dominio).
    • Autenticación mediante Azure AD con Usuario/Contraseña.
    • Autenticación básica SQL Server.
  • ADO.NET: Si estudiamos la conexión que se realiza desde C# (mucho más documentada, todo hay que decirlo), podemos observar que tenemos ya algo que nos puede servir dentro de las propiedades de la clase System.Data.SqlClient.SqlConnection ➡ AccessToken.

Por lo tanto, tenemos claro el camino que tenemos que seguir, ahora tenemos otras 2 opciones, ¿me compilo una librería en C# y realizo la llamada posteriormente o intentamos hacer uso de la clase de manera directa desde PowerShell?

Por temas de performance, vamos a intentar ir por la segunda opción, ¿pero cómo obtengo Access Token si además me vendrá cifrado y en JSON?

Para ello, en primer lugar autenticaríamos con nuestro Service Principal que podría obtener el Secret de nuestro Service Principal de un Azure Key Vault por ejemplo, evitando así tener passwords hardcoded:

$securePassword = ConvertTo-SecureString $appKey -AsPlainText -Force
$secureCredential = New-Object System.Management.Automation.PSCredential($appId, $securePassword)
Connect-AzureRmAccount -ServicePrincipal -Credential $secureCredential -TenantId $tenantId -SubscriptionId $subscriptionId

A continuación, procedemos a generar la función que nos devuelva nuestro Access Token solicitándolo al audience correspondiente (https://database.windows.net/“) mediante una petición de REST API diréctamente a Azure, aquí es necesario que le pasemos en las variables correspondientes el Application Id y Secret Key:

function Get-AccessToken{
    $TokenEndpoint = {https://login.windows.net/{0}/oauth2/token} -f $tenantId 
    $ARMResource = "https://database.windows.net/";

    $Body = @{
            'resource'= $ARMResource
            'client_id' = $appId
            'grant_type' = 'client_credentials'
            'client_secret' = $appKey
    }
    $params = @{
        ContentType = 'application/x-www-form-urlencoded'
        Headers = @{'accept'='application/json'}
        Body = $Body
        Method = 'Post'
        URI = $TokenEndpoint
    }
    $token = Invoke-RestMethod @params
    return $token.access_token
}

Una vez que tenemos nuestro token, podemos comprobar que es correcto decodificándolo con jwt.io, tenemos que obtener algo parecido a:

Por último, nos creamos la función de conexión que lanzará la query que le pasemos como parámetro contra Azure SQL y cerrará la conexión para evitar tener sockets abiertos, aquí es necesario que añadamos el nombre del servidor SQL y la DB a la cual queremos conectar:

function SQLConnection {
    param(
     [string]$query
    )
    $ConnectionString = 'Server=<YOUR SQL SERVER>.database.windows.net;Database=<YOUR DB>;Integrated Security=False;'
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($ConnectionString)
    $connection.AccessToken = Get-AccessToken
    $connection.Open()
    $cmd=new-object system.Data.SqlClient.SqlCommand($query,$connection)
    $cmd.CommandTimeout = 30
    $DataSet = New-Object system.Data.DataSet
    $Adapter = New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
    [void]$Adapter.fill($DataSet)
    $value = $DataSet.Tables[0]
    $connection.Close()
    return $value
}

 

¡Espero que hayáis aprendido tanto como yo al escribir esta entrada! 🙂

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.