在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屋！