How can I connect to Azure Synapse Dedicated SQL pool ( SQL DW) from powershell using service principal authentication?

Question

I want to deploy my SQL scripts to synapse from powershell using Invoke-Sqlcmd utility

We have disable username/password authentication and the only way to do it is by using Managed Identity/AD Service Principal.

How can I leverage Powershell to perform this deployment using AD Service principal authentication ?

Answer 1

you can generate Access Token :

Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'

https://learn.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps

My blog can help : https://datasharkx.wordpress.com/2021/03/12/automated-deployment-of-azure-sql-database-azure-sql-data-warehouse-through-azure-devops-via-service-principal-part-2/