2

Recently created a function app running. The function app hosts a C# and PowerShell function which works as expected with MSI enabled

PowerShell code below, full code in Github

Write-Output "PowerShell Timer trigger function executed at:$(get-date)";

# Get MSI AUTH
$endpoint = $env:MSI_ENDPOINT
$secret = $env:MSI_SECRET
$sqlTokenURI = "https://database.windows.net&api-version=2017-09-01"
$header = @{'Secret' = $secret}
$authenticationResult = Invoke-RestMethod -Method Get -Headers $header -Uri ($endpoint +'?resource=' +$sqlTokenURI)

# CONNECT TO SQL
$SqlServer = $env:SQL_SERVER_NAME
$SqlServerPort = 1433
$Database = "azuredwmonitordb"
$Conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=tcp:$($SqlServer),1433; Initial Catalog=$($Database);")
$Conn.AccessToken = $authenticationResult.access_token

# Open the SQL connection 
$Conn.Open() 

$Cmd=new-object system.Data.SqlClient.SqlCommand("SELECT @@SERVERNAME", $Conn) 
$Cmd.CommandTimeout=120 

# Execute the SQL command 
$Ds=New-Object system.Data.DataSet 
$Da=New-Object system.Data.SqlClient.SqlDataAdapter($Cmd) 
[void]$Da.fill($Ds) 

# Output the count 
$Ds.Tables.Column1 

# Close the SQL connection 
$Conn.Close()

Both functions implement the same logic:

  1. Retrieve Auth token from the provider
  2. Connect to the Azure SQL server using the token

However when using the PowerShell function, the first step step one works but on attempt to establish a connection in the second step, I'm getting the following error:

Exception while executing function: Functions.dm_pdw_exec_sessions. Microsoft.Azure.WebJobs.Script: PowerShell script error. System.Management.Automation: Exception calling "Open" with "0" argument(s): "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". .Net SqlClient Data Provider: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

I have seen this in the past where AAD auth is not enabled properly for the Azure SQL server (user not in master) but this is not the case here.

Piroinno
  • 115
  • 1
  • 9

3 Answers3

2

The problem is in the resource URI - it is missing a forward slash. Instead of:

https://database.windows.net

It should be

https://database.windows.net/

So change your $sqlTokenURI to this and it should work:

$sqlTokenURI = "https://database.windows.net/&api-version=2017-09-01"

M. Williamson
  • 36
  • 3
1

This authentication scenario is currently not supported.

FAQs and known issues with Managed Service Identity (MSI) for Azure Active Directory

Does MSI work with the Active Directory Authentication Library (ADAL) or the Microsoft Authentication Library (MSAL)?

No, MSI is not yet integrated with ADAL or MSAL. For details on acquiring an MSI token using the MSI REST endpoint, see How to use an Azure VM Managed Service Identity (MSI) for token acquisition.

Web Apps User Voice feedback

Mike Ubezzi
  • 1,007
  • 6
  • 8
0

If we want to use AAD token to access Azure SQL, we need to Provision an Azure Active Directory administrator for your Azure SQL Database server. And create a contained database user representing an application that connects using an Azure AD token. 

CREATE USER [appName] FROM EXTERNAL PROVIDER;

My Azure account is not global admin, I find that I can't create the user. If you are the global admin azure account ,you could have a try. I am going to get some help from microsoft azure team, if any response, I will update here.

You also could give your feedback to Azure team.

Following is my test steps.

1.After enable the Azure Function, we could find it create the AD Application but it isnot under my registried App, more detail please refer to the screenshot.

enter image description here

2.Provision an Azure Active Directory administrator for your Azure SQL Database server

enter image description here

3.Connect to Azure Sql and create a contained database user representing an application

Creating [tomtestmsi]... (62,1): SQL72014: .Net SqlClient Data Provider: Msg 33130, Level 16, State 1, Line 1 Principal 'xxxx' could not be found or this principal type is not supported. (62,0): SQL72045: Script execution error. The executed script: CREATE USER [xxxx] FOR EXTERNAL PROVIDER; An error occurred while the batch was being executed.

enter image description here

Tom Sun - MSFT
  • 24,161
  • 3
  • 30
  • 47
  • 2
    You need to add the App spn to an AAD group using Azure CLI, then add the AAD group to SQL server. In addition the C sharp function works – Piroinno Apr 12 '18 at 11:05
  • @PeterIrojah - Were you able to resolve this issue? I am facing same issue even after adding AAD group (containing Service Principal) as contained user in database. – Rohit Vats Aug 06 '18 at 21:08