Query Azure SQL Database using Service Principal with PowerShell

Looking to query Azure SQL SQL Database using Service Principal with PowerShell? Have a look at this blog post!

Recently I had been asked to configure a script that can be used to query an Azure SQL Database table data using a service principal with PowerShell; in this blog post I am going to show how you can do this with test data being returned.

Before we can run the PowerShell script, there is some prerequisites that need to be completed before the script can be ran successfully.

Azure SQL Database Setup

For this blog post, I have a:-

Azure SQL Server: tamopsserver
Azure SQL Database: tamopsdb (tamopsserver/tamopsdb)

Create table Persons:-

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Test data entered:-

Prerequisites

• A service principle needs to be created within Azure Active Directory
• Assign identity to the Azure SQL server
• Assign Directory Readers permission to the Azure SQL server
• Set Azure SQL Server Active Directory admin as Azure Active Directory Group
• Create a service principal user in the Azure SQL database

A service principle needs to be created within Azure Active Directory

Select Azure Active Directory in Azure Portal

Select Manage -> App Registrations

Select New Registration

Click Register

Once registered note down:- Application (client) ID & Directory (tenant) ID found in Overview, then select Certificate & secrets

Select New client secret

Note down Client Secret prior to saving

Select API Permissions and add Azure SQL permissions same as below

Assign identity to the Azure SQL server & Assign Directory Readers permission to the Azure SQL server

To Assign identity to the Azure SQL server, follow guide found here

To Assign Directory Readers permission to the Azure SQL Server, follow here here

Set Azure SQL Server Active Directory admin as Azure Active Directory Group

Create a new Azure AD Group, Select Azure Active Directory -> Groups

Create Group as below, ensure members contains the app you created previously and another Active Directory user, that will be used to log into Azure SQL server to enable the configurations required before you can query Azure SQL database using Service principal.

Once Azure Active Directory User Group has been created, its time to set Azure SQL Server Active Directory admin to this Azure AD group.

Select Azure SQL Server -> Active Directory admin and assign the Azure AD Group

Create a service principal user in the Azure SQL database

Log into Azure SQL database using the user you added to above group (Not Azure Service Principal, you cannot use SQL Management studio to log into Azure SQL using service principal credentials.

You now need to create a user with same name as service principle that you created earlier & then grant permissions to this user; in this example I will be used db_owner.

Create a user with same name as service principle that you created earlier

CREATE USER [tamopssqlapp] FROM EXTERNAL PROVIDER
GO

Grant permissions to this user; in this example I will be used db_owner

EXEC sp_addrolemember 'db_owner', [tamopssqlapp]
GO

PowerShell to query Azure SQL database

Now that the Prerequisites have been completed, lets test the PowerShell script to query the Azure SQL database table.

SQL query I will be testing with:-

SELECT * FROM [dbo].[Persons]

Lets look at the PowerShell Script parameters required

$appId = "<Application (client) ID>"
$tenantId = "<Azure AD Tenant ID>"
$password = "<Application Password>"
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($appId, $secpasswd)
$ConnectionString="Data Source=tamopsserver.database.windows.net; Initial Catalog=tamopsdb;"
$Query="SELECT * FROM [dbo].[Persons]"

Line 1:- Application Client ID that we have saved earlier
Line 2:- Azure AD Tenant ID that we saved earlier
Line 3:- Application password that was saved during the Application creation
Line 4:- Converts password to Secure String
Line 5:- Combines required $mycreds
Line 6:- Connection string; I have entered in required Azure SQL Server & database I want to query
Line 7:- The SQL query I want to run

Full PowerShell Script:- (I built this script initially from the PowerShell script found here)

$appId = "<Application (client) ID>"
$tenantId = "<Azure AD Tenant ID>"
$password = "<Application Password>"
$secpasswd = ConvertTo-SecureString $password -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ($appId, $secpasswd)
$ConnectionString="Data Source=tamopsserver.database.windows.net; Initial Catalog=tamopsdb;"
$Query="SELECT * FROM [dbo].[Persons]"

Connect-AzAccount -ServicePrincipal -Credential $mycreds -Tenant $tenantId
    #get token
    $context =Get-AzContext
    $dexResourceUrl='https://database.windows.net/'
    $token = [Microsoft.Azure.Commands.Common.Authentication.AzureSession]::Instance.AuthenticationFactory.Authenticate($context.Account, 
                                    $context.Environment, 
                                    $context.Tenant.Id.ToString(),
                                     $null, 
                                     [Microsoft.Azure.Commands.Common.Authentication.ShowDialog]::Never, 
                                     $null, $dexResourceUrl).AccessToken
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection                
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    try 
    {
        $SqlConnection.ConnectionString = $ConnectionString
        if ($token)
        {
            $SqlConnection.AccessToken = $token
        }
        $SqlConnection.Open()
        
        $SqlCmd.Connection = $SqlConnection  
       
        $SqlCmd.CommandText = $Query
        $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
        $SqlAdapter.SelectCommand = $SqlCmd
        $DataSet = New-Object System.Data.DataSet
        $SqlAdapter.Fill($DataSet)
        #Outputs query
        $DataSet.Tables
    }
    finally 
    {
        $SqlAdapter.Dispose()
        $SqlCmd.Dispose()
        $SqlConnection.Dispose()
    }
Disconnect-AzAccount 

PowerShell output showing successful query of Azure SQL Database using Service Principal with PowerShell.