在Azure函数中查询本地SQL Server托管实例(C#连接字符串) [英] Querying On-Premise SQL Server Managed Instance within Azure Function (C# connection string)
问题描述
当前,我公司为我们的运营应用程序提供了一个内部SQL Server托管实例.我试图将其连接到Azure Function应用程序,该应用程序是为可访问我们内部应用程序的Alexa Skill的后端而构建的.问题 是,我不确定如何执行此操作.我已经能够向我们的Azure SQL Server添加连接字符串,并且一切正常,但是我不确定本地数据库的连接字符串应该是什么.我已经安装了本地数据源网关, 而且我已经能够从我们的本地数据库中提取到Microsoft Flow. Azure中是否还有另一步要让函数知道分配的连接字符串需要网关云服务?我可以找到Logic Apps的文档,但找不到 查找适用于Azure Function应用程序的任何应用程序.本地数据网关是否不支持Azure功能?下面是我正在使用的代码.
Currently, my company has an on-premise SQL Server managed instance for our operations application. I am trying to connect it to an Azure Function App, which I am building for a back-end for an Alexa Skill that can access our internal application. The problem is, I'm unsure how to do this. I've been able to add a connection string to our Azure SQL Server and everything worked out fine, but I am not sure what the connection string should be for an on-premise database. I've installed the On-premise data source gateway, and I've been able to pull from our on-premise database into Microsoft Flow. Is there another step in Azure to let the function know that the connection string assigned requires the gateway cloud service? I can find documentation for Logic Apps, but I can't find any for Azure Function Apps. Does the on-premise data gateway not support Azure Functions? Below is the code I am using.
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using Alexa.NET.Response;
using Alexa.NET.Request;
using Alexa.NET.Request.Type;
using Alexa.NET;
using System.Data.SqlClient;
using System.Data;
namespace MyAppNamespace
{
public static class MyFunctionClass
{
static string connectionString = "Server=VSQL1\\VSQL1;Database=myDatabase;User Id=username;Password=password";
static SqlConnection connection;
[FunctionName("FunctionCall")]
public static SkillResponse Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", "post", Route = null)][FromBody]SkillRequest req, TraceWriter log)
{
SkillResponse response;
PlainTextOutputSpeech outputSpeech = new PlainTextOutputSpeech();
string input = (req.Request as IntentRequest).Intent.Name;
DataTable dt = new DataTable();
using (connection = new SqlConnection(connectionString))
using (
SqlCommand cmd = new SqlCommand("StoredProcName", connection)
{
CommandType = CommandType.StoredProcedure
})
{
connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
dt.Load(dr);
switch (input)
{
case "Hello":
if (dt.Rows.Count != 0)
{
outputSpeech.Text = "Hello, world!";
break;
}
else
{
outputSpeech.Text = "Row count is zero.";
break;
}
default:
outputSpeech.Text = "Error.";
break;
}
}
response = ResponseBuilder.Tell(outputSpeech.Text);
return response;
}
}
}
推荐答案
除非您具有从Azure Function环境到本地网络的直接网络连接(VNet),否则您不能使用常规的SQL Client +连接字符串.
Unless you have a direct network connection (VNet) from the Azure Function environment to the on-prem network, you can't use the regular SQL Client + Connection String.
因此,您将需要与网络团队合作,因为这是他们必须明确设置的. (这是假定他们不只是打开可以工作的防火墙地址/端口,而且可能不想这样做.)
So, that, you will need to work out with you network team because it's something they would have to explicitly setup. (This assumes they're not just opening a firewall address/port which they can make work, but probably won't want to.)
您可以使用内部部署数据网关,但这需要使用Logic App.
You can use the On-Premise Data Gateway, but that would require a Logic App.
这篇关于在Azure函数中查询本地SQL Server托管实例(C#连接字符串)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!