can not connect Azure SQL with Azure AD user in Azure

Question

I have a powershell script used to query Azure SQL database with Azure AD user. I can run the poweshell script on my local machine. But when I host the powershell script on Azure function, I always get the error : keyword not supported: 'authentication'

My script

$Username = “”
$Password = “”
$Port = 1433

$cxnString = "Server=tcp:$serverName,$Port;Database=$databaseName;UID=$UserName;PWD=$Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;Authentication=Active Directory Password"

$query = ''

Invoke-Sqlcmd -ConnectionString $cxnString - Query $query

Answer 1

According to my test, now we can not implement it with PowerShell in Azure Function. Now we can implement it with .Net Core Function. We need to package Microsoft.Data.SqlClient

For example

 SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            string userName = "<>";
            string password = "<>";
            string server = "<>.database.windows.net";
            string database = "test";
            builder.ConnectionString = "Server=tcp:" + server + ",1433;Initial Catalog=" + database + ";Authentication=Active Directory Password;User ID=" + userName + ";Password=" + password + ";MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;";

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                connection.Open();
                string sql = "SELECT * FROM StarWars";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            log.LogInformation(reader.GetString(2));
                        }
                    }
                }
            }