Skip to main content

Create a Microsoft SQL Server connection in Airflow

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. Integrating SQL Server with Airflow allows you to interact with the database or export the data from a SQL server to an external system using an Airflow DAG

This guide provides the basic setup for creating a Microsoft SQL Server connection.

Prerequisites​

  • The Astro CLI.
  • A locally running Astro project.
  • A Microsoft SQL Server database hosted in cloud or on-premises.
  • Permissions to access Microsoft SQL Server from your local Airflow environment.

Get connection details​

A connection from Airflow to Microsoft SQL Server requires the following information:

  • Host (also known as the endpoint URL, server name, or instance ID depending on your cloud provider)
  • Port (default is 1433)
  • Username
  • Password
  • Schema (default is dbo)

The method to retrieve these values will vary based which cloud provider you use to host Microsoft SQL Server. Refer to the following documents to for more information about retrieveing these values.

For example, if you are running Microsoft SQL Server in a Relational Data Store (RDS) in AWS, complete the following steps to retrieve these values:

  1. In your AWS console, select your region, then go to the RDS service and select your SQL Server database.
  2. Open the Connectivity & security tab and copy the Endpoint and Port.
  3. Follow the documentation for Microsoft SQL Server to create a new database user. Copy the username and password.
  4. (Optional) To use a specific schema, copy the name of the schema. If you skip this, the default schema dbo will be used.

Create your connection​

  1. Open your Astro project and add the following line to your requirements.txt file:

    apache-airflow-providers-microsoft-mssql

    This will install the Microsoft provider package, which makes the MS SQL Server connection type available in Airflow.

info

To install apache-airflow-providers-microsoft-mssql to Airflow 2.6+, you must also add the following lines to packages.txt and restart your Astro project.

build-essential
freetds-dev
libkrb5-dev
default-libmysqlclient-dev

  1. Run astro dev restart to restart your local Airflow environment and apply your changes in requirements.txt.

  2. In the Airflow UI for your local Airflow environment, go to Admin > Connections. Click + to add a new connection, then choose Microsoft SQL Server as the connection type.

  3. Fill out the following connection fields using the information you retrieved from Get connection details:

    • Connection Id: Enter a name for the connection.
    • Host: Enter your host/ endpoint URL/ server name/ instance ID.
    • Schema: Enter your schema name.
    • Login: Enter your username.
    • Password: Enter your password.
    • Port: Enter your Port.

  4. Click Test. After the connection test succeeds, click Save.

    connection-mssqlserver

How it works​

Airflow uses PyMSSQL to connect to Microsoft SQL Server through the MsSqlhook. You can also directly use the MsSqlhook to create your own custom operators.

See also​

Was this page helpful?