1

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 ?

azuresnowflake1
  • 135
  • 1
  • 10

1 Answers1

0

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

Below 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/

Nandan
  • 3,939
  • 2
  • 8
  • 21