使用Azure函数在Azure SQL数据库中创建用户吗? [英] Create user in Azure SQL database using Azure functions?

问题描述

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

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.

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

But I am not able to connect to SQL database.

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

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

这是我的脚本:

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

$databaseName = "databasename"

$adminLogin = '[e-mail]'

$PWord = "password"

$query = "CREATE USER [[e-mail]] FROM EXTERNAL PROVIDER;";

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

我也尝试过此脚本:

尽管以下脚本在Windows PowerShell中有效，但在Azure函数PowerShell中无效.

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

$Creds = Get-Credential -Credential '[e-mail]'
    $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()

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

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

我也尝试使用.NET方法.但它给出了相同的错误

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

我该如何实现?

推荐答案

最后，我找到了答案.如果有人还在这里寻找答案，那是

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

Azure函数中当前无法使用PowerShell进行此操作.

This is currently not possible with PowerShell in azure functions.

我必须使用.net核心.

I had to do this with .net core.

此处的关键是使用 Microsoft.Data.SqlClient 程序包. System.Data.SqlClient不支持活动目录密码身份验证.

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

使用此Microsoft.Data.SqlClient程序包和宾果游戏！

Use this Microsoft.Data.SqlClient package and bingo!

代码在这里:

using Microsoft.Data.SqlClient;

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

            string userName = "[e-mail]";
            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();
            }

这篇关于使用Azure函数在Azure SQL数据库中创建用户吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！