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

4 thoughts on “Query Azure SQL Database with PowerShell and Azure App Registration”

  1. Thanks for you article, i am trying to use a PowerShell to run a query in a Synapse Serveless Pool (that is a SQL Server under the covers) and i would need to run this command programatically as well

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

    GRANT SELECT TO [My App to query SQL]
    GO

    so create and grante to this user, do you know how to do this

  2. are you running the powershell in VS Code or in an Azure function?

    I’m trying to run invoke-sqlcmd inside a function and no matter what I do I cannot import the module and it says it’s not a valid cmdlet

Leave a Comment

Your email address will not be published.

*

%d bloggers like this: