Joel Greijer

  $ErrorActionPreference = "stop" $server = "your-server.database.windows.net" $database = "your-database" $query = "select 1 from dbo.yourtable" Connect-AzAccount $token = ( Get-AzAccessToken -ResourceUrl "https://database.windows.net" ).Token Invoke-Sqlcmd -server $server -Database $database -Query $query -as dataset -AccessToken $token write-output "Done!"

This is how to grant user from system assigned identity (e.g. automation account) to allow to connect and query azure sql database without any use of keys, secrets, vaults or hard coded passwords. The app does not have to be contributor in azure sql instance for this to work Enable System Assigned Identity Navigate to Automation Account / Identity Set System Assigned: On Copy Object (Principal) Id guid Navigate to Azure Active Directory Search for the guid in "Search your tenant" box Find the "enterprise application" popping up Copy the Name this is what you use below [App_Name] Grant access to Azure SQL Database CREATE USER [App_Name] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo ALTER ROLE db_datareader ADD MEMBER [App_Name] ALTER ROLE db_datawriter ADD MEMBER [App_Name] How to use from runbook (powershell) $server  =  "your-sql-server-name.database.windows.net" $database  =  "your-database-name" $query  =  "sel...