Query Azure SQL Database with PowerShell and Azure App Registration

As you already know, I love to use Azure App Registration to authenticate with Microsoft Cloud Services. Now I had a Use Case to Query a SQL Database stored in Azure with a PowerShell Script to Select Data and do some stuff.

So how to solve this without an Azure App Registration (Enterprise Applikation)? Let see the steps followed by the Details below.

• Create and configure Azure App Registration
• Allow Azure App to Access SQL Database
• Write your PowerShell Code.

Create and configure Azure App Registration

So, log in to Azur Portal and Navigate to “App registrations.”

---

Click on “New registration”

---

Enter a Name

and click “Register”

---

Note “Application (client) ID” and “Directory (tenant) ID” for later. We need that in our PowerShell Script.

---

Navigate to “Certificate & secrets”

and create a “New client secret” and write down the “Client Secret” for our PowerShell Scirpt.

---

So with this, we have created an Azure App Registration, which we can grant access to your SQL Database to run a select Query.

Allow Azure App to Access SQL Database

Now it’s time to talk about Permission.

For this, you need to Download “SQL Management Studio” (If not already done) and connect to your Azure SQL Database.

Open a new Query window and run the following command to create a new Login and grant Select Permissions to Database.

Make sure to replace “My App to query SQL” with your App Name and keep the “[]”

CREATE USER [My App to query SQL] FROM EXTERNAL PROVIDER
GO

GRANT SELECT TO [My App to query SQL]
GO

Now our App has Permissions to run a select Query against our Database.

Write your PowerShell Code

$clientID = "your Client ID"
$Clientsecret = "your Secret"
$tenantID = "your Tenant ID"


$DB="Database Name"
$Serverinstance="yourSQLserver.database.windows.net"
$Query="SELECT * FROM [dbo].[Table] order by Date desc"


$Modules = @("sqlServer") 

foreach ($Module in $Modules) {
    if (Get-Module -ListAvailable -Name $Module) {
        # "Module is already installed:  $Module"        
    }
    else {
        W# "Module is not installed, try simple method:  $Module"
        try {
            Install-Module $Module -Force -Confirm:$false
            # "Module was installed the simple way:  $Module"
        }
        catch {
            # "Module is not installed, try the advanced way:  $Module"
            try {
                [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
                Install-PackageProvider -Name NuGet  -MinimumVersion 2.8.5.201 -Force
                Install-Module $Module -Force -Confirm:$false
                # "Module was installed the advanced way:  $Module"
            }
            catch {
               # "could not install module:  $Module"

            }
        }
    }

    # "Import Module:  $Module"
    Import-module $Module
}


#Get Token for autentication
$request = Invoke-RestMethod -Method POST `
           -Uri "https://login.microsoftonline.com/$tenantid/oauth2/token"`
           -Body @{ resource="https://database.windows.net/"; grant_type="client_credentials"; client_id=$clientid; client_secret=$Clientsecret }`
           -ContentType "application/x-www-form-urlencoded"
$access_token = $request.access_token


#Run SQL Command with accessToken
Invoke-Sqlcmd -ServerInstance $Serverinstance -Database $DB -AccessToken $access_token -query $Query

The latest Script is in my GitHub Repo: GitHub – Seidlm/Microsoft-Graph-API-Examples

Michael Seidl aka Techguy
au2mate everything