First published on MSDN on Oct 30, 2018
How to create a native app registration
This guide assumes that you already own an Azure Subscription, an Azure Active Directory properly setup and your PowerShell environment properly set for running scripts.
You might want to refer to this article first: https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/10/26/azure-sql-database-token-base-authent...
Problem summary:
There might be a time when we you will want to create a PowerShell script that can perform some management operations against your Azure SQL Database, therefore you would want to have a trustworthy app that could connect to your Azure SQL DB instance and so it for you.
Explanation of this guide:
This guide will explain how to register your PowerShell script as a native application registration in AAD, so that you are able to run automated tasks from your PowerShell script.
Step 1: Create your native PowerShell application registration in Azure Active Directory
Go to your Azure portal and navigate all the way to your Azure Active Directory resource. 
Once you are there, you should proceed to App Registrations under the "Manage" section. 
Now proceed to click on +New application registration . 
Once you are here, you will be prompted for some configurations and settings. 
Under this configuration, please make sure you give your application a name, an application type and a redirect uri.
- Name: application name
- Application Type:
- WebApp: this is a web app application.
- Native: this is a native application, please choose this option.
- Redirect URI: since this is a local application, please redirect it to your localhost.
Once you have created the application, you should see a screen like this one below. 
After you see this screen, there is some additional configuring we need to do.
Step 2: Configuring the Native app in AAD to connect to an Azure SQL DB endpoint.
Now that we have our app registered and created in AAD, please proceed to click on the "Settings" button, as show in the image above.
You should be able to see a blade with these options: 
Now, we will need to configure the Redirect URIs, Owners and Required Permissions.
- Redirect URIs: this was previously configured when creating the app. Default URI: https://localhost/
- Owners: from your AAD domain, you could choose one or more people that own this application. For my case, I have identity as the owner.
- Required permissions: this requires a bit more explanation. What this Required permissions do is actually allow your application to connect to an Azure SQL Database endpoint or any other endpoint that you give it access to. This required permission pretty much lets you to connect to the resource URI you are requesting in your PowerShell application, which will be explained later.
- Click on +Add
- This will bring up another blade with the corresponding API for the endpoint.
- This might sound counter-intuitive, but you don't actually get a list of the available APIs we have in Azure, so you will need to type it in manually.
- In the search bar when you click on "Select an API", type in: Azure SQL Database
- Now select it and go back to point 2, where it tells you to "Select permissions".
- Hit on the check box where it says: "Delegated permissions" > "Access Azure SQL Database and Data Warehouse"
- Now hit Select and you should be good to go for now.
- Click on +Add
Step 3: Configuring your PowerShell script in your personal computer.
Fire up your PowerShell ISE window or Visual Studio code and make sure you add the following lines of code to your script.
Now, we must make sure to configure the following variables in our script:
- Tenant Name: this is found in the AAD Properties option under the Manage section. Please review the "Directory ID" field and copy that guid and place it in the script as the $tenantName value.
- Client ID: the client ID can be found in your app registration window. It's the guid called "Application ID".
- Redirect URI: we previously added this in the Redirect URI in the Settings of the Native app registration. The value should be: "https://localhost/"
- Resource URI: this URI should be pointing to the Azure SQL Database API. The value should be: "https://database.windows.net/"
- Authority URI: last but not least, the Authority URI. This is useful if you are connecting with AAD authentication. It's the STS link where the tenant will be authenticated against. The value should be: "https://login.microsoftonline.com/$tenantName"
Script
$tenantName = "{AAD DIRECTORY ID}" # Tenant Name this is found in AAD > Properties > Directory ID
$clientId = "{APPLICATION ID}" # Application ID is found in AAD > App Registrations > Application ID
$redirectUri = "{REDIRECT URI}" # Rediret URI is found in AAD > App Registrations > Settings > Redirect URIs
$resourceUri = "https://database.windows.net/" # Resource URI > Found in AAD > App Registrations > Settings > Required Permissions >
# Add > Search Bar *Type in* > Azure SQL DB > Add Rights > OK > OK > Grant permissions
$authorityUri = "https://login.microsoftonline.com/$tenantName" # Authority URI https://login.microsoftonline.com/{DirectoryId|TenantName}
function GetAuthToken {
# 64 bit make sure to change the version to the one available on your machine
$adalPath = "${env:ProgramFiles}\WindowsPowerShell\Modules\AzureRM.profile\5.5.1"
# 32 bit uncomment if this is your scenario
# adalPath = "${env:ProgramFiles(x86)}\WindowsPowerShell\Modules\AzureRM.profile\5.3.4"
$adal = "$adalPath\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
$adalforms = "$adalPath\Microsoft.IdentityModel.Clients.ActiveDirectory.WindowsForms.dll"
[System.Reflection.Assembly]::LoadFrom($adal) | Out-Null
[System.Reflection.Assembly]::LoadFrom($adalforms) | Out-Null
$authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authorityUri
$authResult = $authContext.AcquireToken($resourceUri, $clientId, $redirectUri, "Always") # Not sure when to use AcquireToken() or AcquireTokenAsync()
return $authResult
}
# Call GetAuthToken to acquire token based on credentials entered in prompt
$authResult = GetAuthToken
$authResult.AccessToken
# Server name, database name and the connection string that will be used to open connection
$sqlServerUrl = "fcobo.database.windows.net"
$database = "TESTDB"
$connectionString = "Server=tcp:$sqlServerUrl,1433;Initial Catalog=$database;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;"
# Create the connection object
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
# Set AAD generated token to SQL connection token
$connection.AccessToken = $authResult.AccessToken
$connection.AccessToken
# prints the connection information, it's still closed at this point, will open later on. The token is already attached.
$connection
# Query that will be sent when the connection is open. I had a 4,000 record table and I was able to truncate with this script
$query = "TRUNCATE TABLE TEST1"
# Opens connection to Azure SQL Database and executes a query
$connection.Open()
# After this, the token is no longer there, I believe this is because the authentication went through already, so it gets rid of it.
$connection
$command = New-Object -Type System.Data.SqlClient.SqlCommand($query, $connection)
$command.ExecuteNonQuery()
$connection.Close()
Now feel free to modify the script and adapt it to your particular needs! :)
Hope this guide helps you on your endeavors.