使用 Azure 函数在 Azure SQL 数据库中创建用户?

我想在 Azure SQL 数据库中创建用户.我正在通过 Azure Functions 运行这个 PowerShell.

我已将自己的名称设置为服务器管理员.我也配置了防火墙.

但我无法连接到 SQL 数据库.

<块引用>

它给了我错误:无法打开服务器请求的domain.com"登录.登录失败.

这是我的脚本:

$serverName = "servername.database.windows.net"$databaseName = "数据库名"$adminLogin = 'user@domain.com'$PWord = "密码"$query = "从外部提供商创建用户 [user1@domain.com]；";调用-Sqlcmd -ServerInstance $serverName -Database $databaseName -U $adminLogin -Password $PWord -Query $query

我也试过这个脚本:

虽然下面的脚本在 Windows PowerShell 中有效，但在 Azure 函数 PowerShell 中无效.

$Creds = Get-Credential -Credential 'username@domain.com'$Username = $($Creds.GetNetworkCredential().UserName)$Password = $($Creds.GetNetworkCredential().Password)$数据库 = "testg"$Server = 'test.database.windows.net'$端口 = 1433$cxnString = "服务器=tcp:$Server,$Port;Database=$Database;Authentication=Active Directory 密码;UID=$UserName;PWD=$Password;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"$query = "使用密码创建用户 YourUser = 'JRxuerCc(q'"$cxn = 新对象 System.Data.SqlClient.SqlConnection($cxnString)$cxn.Open()$cmd = 新对象 System.Data.SqlClient.SqlCommand($query, $cxn)$cmd.CommandTimeout = 120$cmd.ExecuteNonQuery()$cxn.Close()

<块引用>

但这在 Azure 函数中不起作用.它说关键字不是支持:'身份验证'

我也尝试过使用 .NET 方法的类似方法.但它给出了同样的错误

我怎样才能做到这一点?

解决方案

终于找到了答案.如果还有人在这里寻找答案，那就是

这在 Azure 函数中目前无法使用 PowerShell.

我必须用 .net core 来做这个.

这里的关键是使用 Microsoft.Data.SqlClient 包.System.Data.SqlClient 不支持活动目录密码认证.

使用这个 Microsoft.Data.SqlClient 包和宾果游戏！

代码在这里:

使用 Microsoft.Data.SqlClient;SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();字符串用户名 = "用户名@domain.com";字符串密码 = "密码";builder.ConnectionString = "Server=tcp:" + server + ",1433;Database=" + database + ";Authentication=Active Directory 密码;UID=" + userName + ";PWD=" + password + ";Trusted_Connection=False;加密=真；连接超时=30；"；使用(SqlConnection 连接 = 新 SqlConnection(builder.ConnectionString)){连接.打开()；字符串查询 =使用密码创建用户 YourUser = 'JRxuerCc(q'"SqlCommand cmd = new SqlCommand(查询, 连接);cmd.CommandTimeout = 120;cmd.ExecuteNonQuery();连接.关闭()；}

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 = 'user@domain.com'

$PWord = "password"

$query = "CREATE USER [user1@domain.com] 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 'username@domain.com'
    $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?

解决方案

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 = "username@domain.com";
            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();
            }