Using Microsoft Entra service principal to run PowerShell script on Azure SQL Database

Microsoft Entra service principals (or App regs.) is nice secure way to connect to Azure SQL Database and then perform queries using PowerShell. This is an ideal alternative to using a local Service Account. The upside to this is that you can authenticate with a secret or with a certificate that you create for yourself or the machine you are running your script(s) from.

Creating and setting up the Azure service principal

You need the Application Administrator role in Microsoft Entra ID to be able to create the service principal and the PowerShell module
Create an Azure app identity (PowerShell)

Creating and setting up the Microsoft Entra service principal – Using the GUI
Create an Azure AD app and service principal in the portal

Once the service principal been created, you dont need to assign API permissions to it. The next step is to add the service principal as user on the Azure SQL Database.

Access the SQL server using SQL Server Management Studio with a Microsoft Entra ID user account and open up a query on the database you wish service principal has access to and run the following query:

Now the Microsoft Entra service principal has owner access to the database of your choosing, you can change the permissions as you see fit.

Before you can access the database you need to create a secret for the Microsoft Entra service principal. You can do that by accessing your Azure App reg. using the Microsoft Entra ID Portal. When you have located your App reg. under “App registrations” navigate to the menu “Certificates & secrets” and then click on “New client secret” and type in a description for the secret. When you are done, click on “add” and copy value to notepad. You will not be able to see the secret again after you leave the page.

Now that we have a client secret the last two things we need is the “Client ID” & “Tenant ID”, both can be found on the App reg. page when you select “Overview”. Now we are ready to use PowerShell

In order for you to access the database using PowerShell and the Microsoft Entra service principal, you need the following code in PowerShell:

Copy the code from above to PowerShell ISE or Visual Studio Code (dealers’ choice) and then fill out the variables with the information we have gathered above and the information of your Azure SQL Database server and database.