CODEWITHSUNDEEP
azureazure-functions
Phelipe Vieira
Phelipe Vieira

Reputation: 83

How can I connect my azure function with my azure sql

I developed a cron trigger azure fuction who needs to search for soe data in my database.

Localy i can connect whit sql server, so i change the connection string in loca.settings.json to connect in azure sql and published the function, but the function cant connect with database.

I need to do something more than configure the local.settings.json?

Upvotes: 5

Views: 25811

Answers (3)

ippi
ippi

Reputation: 10177

Having just dealt with this beast (using a custom handler with Linux), I believe the simple way is to upgrade your App to premium-plan, allowing you to access the "Networking" page from "App Service plans". This should allow you to put both sql-server and app in the same virtual network, which probably makes it easier. (but what do I know?)

Instead, if you don't have the extra cash laying around, you can try what I did, and set up a private endpoint, and use the proxy connection setting for your database:

  1. Create a virtual network

    • I used Address space: 10.1.0.0/16 (default I think)
    • Add subnet 10.1.0.0/24 with any name (adding a subnet is required)

  2. Go to "Private link center" and create a private endpoint.

    • any name, resource-group you fancy
    • use resource type "Microsoft.Sql/Server" and you should be able to select your sql-server (which I assume you have created already) and also set target sub-resource to "sqlServer" (the only option)
    • In the next step your virtual network and submask should be auto-selected
    • set Private DNS integration to yes (or suffer later).

  3. Update your firewall by going to Sql Databases, select your database and click "Set Server Firewall" from the overview tab.

    • Set Connection Policy to proxy. (You either do this, or upgrade to premium!)
    • Add existing virtual network (rule with any name)
    • Whitelist IPs

  4. Find your FQDN from Private link center > Private Endpoints > DNS Configuration. It's probably something like yourdb.privatelink.database.windows.net

    • Update your app to use this url. You just update your sql server connection string and replace the domain, for example as ADO string: Server=tcp:yourdb.privatelink.database.windows.net,1433;Initial Catalog=somedbname;Persist Security Info=False;User ID=someuser;Password=abc123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;

Also note that I at some point during all of this I switched to TrustServerCertificate=True and now I can't bother to figure out if it does a difference or not. So I left it as an exercise to the reader to find out.

So what we have done here...?

We have forced your function app to go outside the "azure-sphere" by connecting to the private endpoint. I think that if you bounce between azure-services directly, then you'll need some sort of authentication (like logging in to your DB using AD), and in my case, using custom handler and linux base for my app, I think that means you need some trust negotiation (kerberos perhaps?). I couldn't figure that out, so I came up with this instead.

Upvotes: 2

Meow
Meow

Reputation: 42

If you are using entity framework core to make a connection, Other Way of connection to SQL is by using dependency injection from .netcore library.

You can keep the connection string in Azure Key-vault or the config file from there you can read the same using azure function startup class. which need below code setup in your function app.

using Microsoft.Azure.Functions.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
[assembly: FunctionsStartup(typeof( TEST.Startup))]
namespace TEST
{
    internal class Startup : FunctionsStartup
    {
        public override void Configure(IFunctionsHostBuilder builder)
        {
            Contract.Requires(builder != null);
            builder.Services.AddHttpClient();

            var configBuilder = new ConfigurationBuilder()
               .SetBasePath(Directory.GetCurrentDirectory())
               .AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
               .AddAzureKeyVault($"https://XYZkv.vault.azure.net/"); 

            var configuration = configBuilder.Build();
            var conn = configuration["connectionString"];   

           builder.Services.AddDbContext<yourDBContext>(
            options => options.UseSqlServer(configuration["connectionString"]));     
       }
    }
}

after that where ever you are injecting this dbcontext, with context object you can do all CRUD operations by following microsoft's entity framework core library documentation.

Upvotes: 0

Carlos Alves Jorge
Carlos Alves Jorge

Reputation: 1995

The local.settings.json is only used for local testing. It's not even exported to azure.

You need to create a connection string in your application settings.

In Azure Functions - click Platform features and then Configuration.

Set the connection string

A function app hosts the execution of your functions in Azure. As a best security practice, store connection strings and other secrets in your function app settings. Using application settings prevents accidental disclosure of the connection string with your code. You can access app settings for your function app right from Visual Studio.

You must have previously published your app to Azure. If you haven't already done so, Publish your function app to Azure.

In Solution Explorer, right-click the function app project and choose Publish > Manage application settings.... Select Add setting, in New app setting name, type sqldb_connection, and select OK.

Application settings for the function app.

In the new sqldb_connection setting, paste the connection string you copied in the previous section into the Local field and replace {your_username} and {your_password} placeholders with real values. Select Insert value from local to copy the updated value into the Remote field, and then select OK.

Add SQL connection string setting.

The connection strings are stored encrypted in Azure (Remote). To prevent leaking secrets, the local.settings.json project file (Local) should be excluded from source control, such as by using a .gitignore file.

https://learn.microsoft.com/en-us/azure/azure-functions/functions-scenario-database-table-cleanup

Upvotes: 4

Related Questions