Azure Function Connect to SQL Database | 5 Steps to Follow

You can connect your Azure Function to your SQL Database without any integration programs. The process includes declaring the input and output bindings within the definition of the Azure function you want to connect. In this article, we shall discuss how to use VS Code to achieve just that.

How to Connect Azure Function to SQL Database

Connecting your Azure function to your SQL Database consists of the following steps.

1. Create an Azure SQL Database

Create a serverless Azure SQL Database that has the same resource group as your Azure function app. Enter the names (preferably unique) of your database and server. For the authentication method, choose SQL Server authentication.

Enter the login information and set a password for the server admin. Now, select Yes for the prompt that says Allow Azure services and resources to access this server.

After you provided the information for the prompts and completed the setup, go to the database blade from the Azure portal. Then, head over to the Settings, and choose Connection strings. Use the copy button on the right of the ADO.NET (SQL authentication) connection string.

For storing the HTTP request data, select the Query editor from the database blade and use the query below.

Check the firewall settings of your server and make sure your Azure function can access the Azure SQL Database. From the Azure portal, go to Server Blade> Security, and choose Networking. There, under the Exceptions setting, check the box next to Allow Azure services and resources to access this server.

2. Change the Azure Function Settings

Changing the function settings is necessary to make sure it can write data to the SQL database. Here’s what to change.

Paste the connection string you copied earlier to your notepad. Use the password you set for your SQL database to replace the password value. Now, copy the new connection string.

Open the command palette and run the “Azure Functions: Add New Setting. . .” command.

Select your Azure function and follow the prompts to update the information. Type in SqlConnectionString when prompted for a new setting name and paste the connection string you just copied. Doing so will create a new setting that you can download to a local.settings.json file.

Open the command palette again and run the following command.

“Azure Functions: Download Remote Settings…”

Choose your desired Azure function and click Yes to all for downloading the settings to your local project.

3. Add SqlClient Package to Your Project

The SqlClient library helps you connect to your SQL Database. You can add it in the following manner.

1. Open your local function project in VS Code.
2. Right-click on the function app project and select Manage NuGet Packages.
3. Search for Microsoft.Data.SqlClient and click on it. Select the version and start the installation process.
4. After installing, click OK and select I Accept is a license window arrives.

Now, you can add the function code.

4. Add Azure SQL Output Binding

Before you add the Azure SQL output binding to your project, make sure to install the corresponding extension. In the terminal window, run the following command.

dotnet add package Microsoft.Azure.WebJobs.Extensions.Sql

Open your local project file and add the class as shown here to define the object written to your SQL DB. 

C#:

Next, open the project file again and include the below parameter inside the definition of your method.

[Sql(commandText: "dbo.ToDo", connectionStringSetting: "SqlConnectionString")] IAsyncCollector<ToDoItem> toDoItems)

JSON:

5. Use the Output Binding in Your Code

Add the following code above the return statement of your method.

C#:

Python:

import azure.functions as func
import logging
import uuid
def main(req: func.HttpRequest, toDoItems: func.Out[func.SqlRow]) -> func.HttpResponse:
    name = req.params.get('name')
    if not name:
        try:
            req_body = req.get_json()
        except ValueError:
            pass
        else:
            name = req_body.get('name')
    if name:
        toDoItems.set(func.SqlRow({"id": uuid.uuid4(), "title": name, "completed": false, url: ""}))
        return func.HttpResponse(f"Hello {name}!")
    else:
        return func.HttpResponse(
            "Please pass a name on the query string or in the request body",
            status_code=400
        )

Conclusion

Each kind of output binding needs a different value of direction, type, and name for its definition in the function.json file. Defining these attributes, however, changes depending on the language you use to define your Azure Function, whether C#, Python, or Javascript.