Skip to content

Latest commit

 

History

History
172 lines (116 loc) · 11.4 KB

CONNECTION.md

File metadata and controls

172 lines (116 loc) · 11.4 KB
Raw

sql-action connection guide

GitHub sql-action access is coupled with two concepts necessary for the action to connect to the database:

🔌 Network connectivity

Azure SQL Database

If you can use the option Allow Azure Services and resources to access this server, you are all set and you don't need to to anything else to allow GitHub Action to connect to your Azure SQL database.

If you cannot use the aforementioned option, the action can automatically add and remove a SQL server firewall rule specific to the GitHub Action runner's IP address. Without the firewall rule, the runner cannot communicate with Azure SQL Database. To enable the action to automatically add/remove a firewall rule, add an Azure/login step before the sql-action step. Also, the service principal used in the Azure login action needs to have elevated permissions, i.e. membership in SQL Security Manager RBAC role, or a similarly high permission in the database to create the firewall rule.

A benefit of using the firewall rule is that it is scoped to the IP address of the runner. This means that the firewall rule is only active for the duration of the action. This is a security best practice because it limits the exposure of the database to only the IP address of the runner.

Examples of creating the Azure Active Directory users for managing the firewall rules:

Azure government clouds

Azure SQL Action for GitHub is supported for the Azure public cloud as well as Azure government clouds ('AzureUSGovernment' or 'AzureChinaCloud'). Before running this action, login to the respective Azure Cloud using Azure Login by setting appropriate value for the environment parameter.

Potential errors

  • If the Azure/login action is not included, then the sql action would fail with a firewall exception and appropriate messaging.
  • Alternatively, if enough permissions are not granted on the service principal or login action is not included, then the firewall rules have to be explicitly managed by user using CLI/PS scripts.

Azure SQL Managed Instance and SQL Server

GitHub sql-action requires that prior to running the action against Azure SQL Managed Instance or SQL Server, the workflow must have network access to the SQL instance. For network architectures where public access is not available, self-hosted runners can be leveraged.

An overview of Azure SQL Managed Instance networking is available to assist in identifying the appropriate network access.

🔑 Authentication

The basic format of a connection string includes a series of keyword/value pairs separated by semicolons. The equal sign (=) connects each keyword and its value. (Ex: Key1=Val1;Key2=Val2) An example connection string template is: Server=<servername>; User ID=<user_id>; Password=<password>; Initial Catalog=<database>.

The following rules are to be followed while passing special characters in values:

  1. To include values that contain a semicolon, single-quote character, or double-quote character, the value must be enclosed in double quotation marks.
  2. If the value contains both a semicolon and a double-quote character, the value can be enclosed in single quotation marks.
  3. The single quotation mark is also useful if the value starts with a double-quote character. Conversely, the double quotation mark can be used if the value starts with a single quotation mark.
  4. If the value contains both single-quote and double-quote characters, the quotation mark character used to enclose the value must be doubled every time it occurs within the value.

For more information about connection strings, see https://aka.ms/sqlconnectionstring

SQL authentication

The most basic method of authentication is to use SQL authentication, requiring a username and password. The connection string for SQL authentication is:

Server=<servername>;Initial Catalog=<database>; User ID=<user_id>; Password=<password>; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Azure Active Directory service principal authentication

To use Azure Active Directory service principal authentication with sql-action, you need to create a Service Principal, and store the related credentials into GitHub secrets so that it can be used by azure/login action to authenticate prior to running sql-action. In scenarios where the workflow adds a temporary firewall rule for Azure SQL Database, the same service principal can be used for the firewall rule and to connect to the SQL instance.

Create a service principal

Additional information on creating a service principal and connecting GitHub to Azure can be found at:

The below az cli command creates a service principal with the name sqldeployserviceprincipal and the contributor role scoped to the resource group of the Azure SQL Database.

az ad sp create-for-rbac --role contributor --sdk-auth --name "sqldeployserviceprincipal" \
  --scopes /subscriptions/{subscription-id}/resourceGroups/{resource-group}

Replace {subscription-id}, {resource-group} with the subscription ID and resource group of the Azure SQL Database.

The command should output a JSON object similar to this:

{
  "clientId": "<GUID>",
  "clientSecret": "<GUID>",
  "subscriptionId": "<GUID>",
  "tenantId": "<GUID>",
  // ...
}

The output of the command contains the fields clientId, clientSecret, subscriptionId, and tenantId, that can be used to authenticate with azure/login. Place each of those values into GitHub Secrets with the names CLIENT_ID, CLIENT_SECRET, SUBSCRIPTION_ID, and TENANT_ID respectively.

  - uses: Azure/login@v1
    with:
      creds: '{"clientId":"${{ secrets.CLIENT_ID }}","clientSecret":"${{ secrets.CLIENT_SECRET }}","subscriptionId":"${{ secrets.SUBSCRIPTION_ID }}","tenantId":"${{ secrets.TENANT_ID }}"}'

Add the service principal to Azure SQL

The service principal created in the previous section needs to be added to the Azure SQL as a user and granted the permissions necessary to carry out deployments. Authorizing database access varies depending on the type of Azure SQL instance, check the documentation for more information..

The following example demonstrates adding the service principal to Azure SQL Database and granted the db_ddladmin role, completed by running the following SQL commands against the Azure SQL Database from a tool such as Azure Data Studio while logged in as an Azure Active Directory user. Your deployments may require additional roles to be granted to the service principal.

CREATE USER [sqldeployserviceprincipal] FROM EXTERNAL PROVIDER;

ALTER ROLE [db_ddladmin]
ADD MEMBER [sqldeployserviceprincipal];

The connection string for Azure Active Directory service principal authentication is:

Server=<servername>;Initial Catalog=<database>;Authentication=Active Directory Service Principal; User ID=<user_id>; Password=<password>; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

where <user_id> is the clientId of the service principal and <password> is the clientSecret of the service principal.

Azure Active Directory managed identity authentication

To use Azure Active Directory managed identity authentication with sql-action, you need to create a managed identity which is a federated user assigned identity associated with a specific entity (e.g. branch, environment) of the GitHub repository. For information on creating a managed identity for use with GitHub actions, see:

In scenarios where the workflow adds a temporary firewall rule for Azure SQL Database, the same managed identity can be used for the firewall rule and to connect to the SQL instance.

Create a managed identity

In following the documentation on identity federation, you will establish an App Registration in Azure Active Directory and create a federated credential (managed identity) for a specific entity in GitHub. The federated identity and the app registration will be used to authenticate with Azure.

The following values are required for the azure/login action to authenticate with Azure using the managed identity:

  • AZURE_CLIENT_ID the Application (client) ID
  • AZURE_TENANT_ID the Directory (tenant) ID
  • AZURE_SUBSCRIPTION_ID the subscription ID
on:
  push:
    branches: [ main ]

permissions:
      id-token: write
      contents: read

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
    - uses: actions/checkout@v3
    - uses: azure/login@v1
      with:
        client-id: ${{ secrets.AZURE_CLIENT_ID }} 
        tenant-id: ${{ secrets.AZURE_TENANT_ID }} 
        subscription-id: ${{ secrets.AZURE_SUBSCRIPTION_ID }}

Add the managed identity to Azure SQL

The managed identity created in the previous section needs to be added to the Azure SQL as a user and granted the permissions necessary to carry out deployments. Authorizing database access varies depending on the type of Azure SQL instance, check the documentation for more information..

The following example demonstrates adding the managed identity to Azure SQL Database and granted the db_ddladmin role, where the managed identity was created in an App Registration named sample-app-registration. The example can be completed by running the following SQL commands against the Azure SQL Database from a tool such as Azure Data Studio while logged in as an Azure Active Directory user. Your deployments may require additional roles to be granted to the managed identity.

CREATE USER [sample-app-registration] FROM EXTERNAL PROVIDER;

ALTER ROLE [db_ddladmin]
ADD MEMBER [sample-app-registration];

The connection string for Azure Active Directory managed identity authentication is:

Server=<servername>;Initial Catalog=<database>;Authentication=Active Directory Default; Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Note: no userid or password is required for managed identity authentication.