Connect to SQL Server from Azure Functions using Windows Authentication
Serverless-based architectures are becoming more and more prevalent. Microsoft offer Azure Functions (similar to AWS Lambda) for building event-based applications. Even though these are cloud-based technologies you might need to access data residing on an on-prem SQL Server. This can complicate the case more if the SQL Server uses Windows Authentication (Active Directory) for authentication. In this article we will guide you on how to achieve this. It is assumed that you are developing a Python application running as a Docker Container.
This article builds directly on top of Using SQL Server from a Linux Docker container using Windows Authentication.
Set up Virtual Network with VPN
As the Azure Functions need to be able to connect to the SQL Server they need to be on the same network. You can achieve this by setting up Virtual Network with a Site-to-Site VPN. This is outside the scope of this article but there exist many great guides.
Using Azure Function extensions
Extensions for Azure Functions can be used for getting extra functionality either at invocation-level or application-level. We want to create an extension at application-level such that the function authenticates with Active Directory before being triggered. Enabling extensions when working with Python in Azure Functions is straight forward. Simply add the variable PYTHON_ENABLE_WORKER_EXTENSIONS=1 to your configuration or in your local.settings.json file. You will also need to add ACTIVE_DIRECTORY_USER and ACTIVE_DIRECTORY_PW for later.
Building a custom extension
I recommend creating a separate folder in your Azure Function App called “kerberos” and a __init__.py giving you a structure like:
root/ ├── kerberos/ │ └── __init__.py └── Trigger/ └── __init__.py
In kerberos/__init__.py we create a class which relies heavily on the scripts built in the previous article. Using the AppExtensionBase base-class we can build an Application-level-extension. To use this, we simply need to import it in the function's __init__.py file and enable it: The Function App is now configured to use Active Directory.