Document Details

$file = "path\body_sa.json"

$templateFile = "path\template_sa.json"

# Sign in to your Azure subscription $sub = Get-AzSubscription -ErrorAction SilentlyContinue if(-not($sub)) { Connect-AzAccount } # If you have multiple subscriptions, set the one to use # Select-AzSubscription -SubscriptionId "<SUBSCRIPTIONID>"

$subscriptionID = "subscriptionID" $resourceGroup = "resourceGroupName" $automationAccount = "automationAccountName"

$output = Set-AzAutomationAccount ` -ResourceGroupName $resourceGroup ` -Name $automationAccount ` -AssignSystemIdentity $output

# build URI $URI = "https://management.azure.com/subscriptions/$subscriptionID/resourceGroups/$resourceGroup/providers/Microsoft.Automation/automationAccounts/$automationAccount`?api-version=2020-01-13-preview" # build body $body = Get-Content $file # obtain access token $azContext = Get-AzContext $azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile $profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($azProfile) $token = $profileClient.AcquireAccessToken($azContext.Subscription.TenantId) $authHeader = @{ 'Content-Type'='application/json' 'Authorization'='Bearer ' + $token.AccessToken } # Invoke the REST API $response = Invoke-RestMethod -Uri $URI -Method PATCH -Headers $authHeader -Body $body # Review output $response.identity | ConvertTo-Json

New-AzResourceGroupDeployment ` -Name "SystemAssignedDeployment" ` -ResourceGroupName $resourceGroup ` -TemplateFile $templateFile

(Get-AzAutomationAccount ` -ResourceGroupName $resourceGroup ` -Name $automationAccount).Identity | ConvertTo-Json

New-AzRoleAssignment ` -ObjectId <automation-Identity-object-id> ` -Scope "/subscriptions/<subscription-id>" ` -RoleDefinitionName "Contributor"

$queryParameter = "?resource=https://database.windows.net/" $url = $env:IDENTITY_ENDPOINT + $queryParameter $Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $Headers.Add("Metadata", "True") $content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest -UseBasicParsing -Uri $url -Method 'GET' -Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json $Token = $content.access_token echo "The managed identities for Azure resources access token is $Token" $SQLServerName = "<ServerName>" # Azure SQL logical server name $DatabaseName = "<DBname>" # Azure SQL database name Write-Host "Create SQL connection string" $conn = New-Object System.Data.SqlClient.SQLConnection $conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30" $conn.AccessToken = $Token Write-host "Connect to database and execute SQL script" $conn.Open() $ddlstmt = "CREATE TABLE Person( PersonId INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(128) NOT NULL)" Write-host " " Write-host "SQL DDL command" $ddlstmt $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($ddlstmt, $conn) Write-host "results" $command.ExecuteNonQuery() $conn.Close()

# Ensures you do not inherit an AzContext in your runbook Disable-AzContextAutosave -Scope Process # Connect to Azure with system-assigned managed identity $AzureContext = (Connect-AzAccount -Identity).context # Set and store context $AzureContext = Set-AzContext -SubscriptionName $AzureContext.Subscription -DefaultProfile $AzureContext

$resource= "?resource=https://management.azure.com/" $url = $env:IDENTITY_ENDPOINT + $resource $Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $Headers.Add("Metadata", "True") $accessToken = Invoke-RestMethod -Uri $url -Method 'GET' -Headers $Headers Write-Output $accessToken.access_token

$url = $env:IDENTITY_ENDPOINT $headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $headers.Add("Metadata", "True") $body = @{resource='https://management.azure.com/' } $accessToken = Invoke-RestMethod $url -Method 'POST' -Headers $headers -ContentType 'application/x-www-form-urlencoded' -Body $body Write-Output $accessToken.access_token

Write-Output "Connecting to azure via Connect-AzAccount -Identity" Connect-AzAccount -Identity Write-Output "Successfully connected with Automation account's Managed Identity" Write-Output "Trying to fetch value from key vault using MI. Make sure you have given correct access to Managed Identity" $secret = Get-AzKeyVaultSecret -VaultName '<KVname>' -Name '<KeyName>' $ssPtr = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($secret.SecretValue) try { $secretValueText = [System.Runtime.InteropServices.Marshal]::PtrToStringBSTR($ssPtr) Write-Output $secretValueText } finally { [System.Runtime.InteropServices.Marshal]::ZeroFreeBSTR($ssPtr) }