connect-azure-sql-with-msal-token.ps1

connect-azure-sql-with-msal-token.ps1

$token = Get-MsalToken -ClientId '<your client id>' -ClientSecret (ConvertTo-SecureString '<your client secret>' -AsPlainText -Force) -Scopes 'https://database.windows.net/.default' -TenantId '<your tenant id>'
$sqlConnection = New-Object System.Data.SqlClient.SQLConnection
$sqlConnection.ConnectionString = "Data Source={0}.database.windows.net;Initial Catalog={1};Connect Timeout=30" -f "myAzureSqlServer", "myAzureSqlDatabase"
$sqlConnection.AccessToken = $token.AccessToken
$sqlConnection.Open()
$command = $sqlConnection.CreateCommand()
$command.CommandText = 'DROP USER IF EXISTS [myUser]; CREATE USER [myUser] FOR EXTERNAL PROVIDER; GRANT CONNECT, SELECT TO [myUser]'
$command.ExecuteNonQuery()
$sqlConnection.Close()