Access to Azure SQL Database and Azure Key Vault using VM's system managed identity

Read about System managed Identity (SMI):

https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/overview
https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-arm
https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql
https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-nonaad

Using SMI with App service to access Azure SQL databases
https://azure.microsoft.com/en-us/blog/securing-azure-sql-databases-with-managed-identities-just-got-easier/



<#

VM access configuration:

1. Azure Resources - NOT required if only accessing SQL DBs and not Azure resources
Navigate to the tab for Resource Groups.
    Select the specific Resource Group you created for your Windows VM.
    Go to Access control (IAM) in the left panel.
    Then Add role assignment a new role assignment for your Windows VM. Choose Role as Reader.
    In the next drop-down, ***Assign access*** to the resource ****Virtual Machine****.
    Next, ensure the proper subscription is listed in the Subscription drop down. And for Resource Group, select All resource groups.
Finally, in Select choose your Windows VM in the drop down and click Save.

2. SQL users
Start SQL Server Management Studio.
    In the Connect to Server dialog, Enter your SQL server name in the Server name field.
    In the Authentication field, select Active Directory - Universal with MFA support.
    In the User name field, enter the name of the Azure AD account that you set as the server administrator, for example, helen@woodgroveonline.com
    Click Options.
    In the Connect to database field, enter the name of the non-system database you want to configure.
    Click Connect. Complete the sign-in process.
    In the Object Explorer, expand the Databases folder.
    Right-click on a user database and click New query.
In the query window, enter the following line, and click Execute in the toolbar:

CREATE USER [smi-vm] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [smi-vm]

#>

#
# Please notice that 
#                resource=https://database.windows.net/ 
#     *** ends *** with a forward slash !!!
#

$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://database.windows.net/' -Method GET -Headers @{Metadata="true"}
$content = $response.Content | ConvertFrom-Json
$AccessToken = $content.access_token

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source = a1257.database.windows.net; Initial Catalog = a1"
$SqlConnection.AccessToken = $AccessToken
$SqlConnection.Open()

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT * from dbo.tt1;"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$DataSet.Tables[0]

# if in the "resource=https://database.windows.net/" the ending forward slash is missing then the following errors is thrown

# Exception calling "Open" with "0" argument(s): "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'."

#
#
# ===================== Key Vault ==========
#  Add Access Policy to the AKV for the VM
#

#
# Please notice that 
#                resource=https://vault.azure.net
#     *** has NO *** forward slash at the end !!!
#

$response = Invoke-WebRequest -Uri 'http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https://vault.azure.net' -Method GET -Headers @{Metadata="true"}
$content = $response.Content | ConvertFrom-Json
$KeyVaultToken = $content.access_token

$result = Invoke-WebRequest -Uri https://cm-kv1.vault.azure.net/secrets/pwd?api-version=2016-10-01 -Method GET -Headers @{Authorization="Bearer $KeyVaultToken"}
$result.content


# if in the "resource=https://vault.azure.net" a forward slash added then the following errors is thrown

Invoke-WebRequest : The remote server returned an error: (401) Unauthorized.