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:
- Retrieve Auth token from the provider
- 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.
$Conn.Open()? Perhaps you need to add some more info to your connection string to stop it trying to use windows authentication (which is the detault) - Nick.McDermaid