Create user in Azure SQL database using Azure functions?

Question

I want to create users in Azure SQL database. I am running this PowerShell through Azure Functions.

I have set up my own name as server admin. Also I have configured the firewall.

But I am not able to connect to SQL database.

It gives me error : Cannot open server "domain.com" requested by the login. The login failed.

This is my script:

$serverName = "servername.database.windows.net"

$databaseName = "databasename"

$adminLogin = '[email protected]'

$PWord = "password"

$query = "CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;";

Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -U $adminLogin -Password $PWord -Query $query

I have tried this script as well:

Although below script works in Windows PowerShell, but does not work in Azure functions PowerShell.

$Creds = Get-Credential -Credential '[email protected]'
    $Username = $($Creds.GetNetworkCredential().UserName)
    $Password = $($Creds.GetNetworkCredential().Password)
    $Database = "testg"
    $Server = 'test.database.windows.net'
    $Port = 1433
    $cxnString = "Server=tcp:$Server,$Port;Database=$Database;Authentication=Active Directory Password;UID=$UserName;PWD=$Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"
    $query = "CREATE USER YourUser WITH PASSWORD = 'JRxuerCc(q'"
    $cxn = New-Object System.Data.SqlClient.SqlConnection($cxnString)
    $cxn.Open()
    $cmd = New-Object System.Data.SqlClient.SqlCommand($query, $cxn)
    $cmd.CommandTimeout = 120
    $cmd.ExecuteNonQuery()
    $cxn.Close()

But this does not work in Azure functions. It say keyword not supported: 'authentication'

Similar way I tried with .NET approach as well. But it gives the same error

How can I achieve this?

Bhushan Inamdar Bhushan Inamdar · Accepted Answer · 2020-04-17T07:10:16

Finally I found answer to this. If anybody still searching for answer here it is

This is currently not possible with PowerShell in azure functions.

I had to do this with .net core.

key here is to use Microsoft.Data.SqlClient package. System.Data.SqlClient does not support active directory password authentication.

Use this Microsoft.Data.SqlClient package and bingo!

code goes here :

using Microsoft.Data.SqlClient;

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

            string userName = "[email protected]";
            string password = "password";
            builder.ConnectionString = "Server=tcp:" + server + ",1433;Database=" + database + ";Authentication=Active Directory Password;UID=" + userName + ";PWD=" + password + ";Trusted_Connection=False;Encrypt=True;Connection Timeout=30;";

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                connection.Open();
                string query = "CREATE USER YourUser WITH PASSWORD = 'JRxuerCc(q'"
                SqlCommand cmd = new SqlCommand(query, connection);
                cmd.CommandTimeout = 120;
                cmd.ExecuteNonQuery();
                connection.Close();
            }

Create user in Azure SQL database using Azure functions?

1 Answers