Connect SQL Server to Rig

    Ingest tables from SQL Server or Azure SQL Database into your warehouse. Rig signs in with a SQL login, or with an Entra service principal, which Entra-only Azure SQL servers require. Connections are always encrypted.

    Before you start

    • Know the server host, such as myserver.database.windows.net, the port (1433 by default) and the database name.
    • Choose one sign-in method. Use a SQL login, or an Entra service principal if your Azure SQL server only allows Entra sign-in.
    • Create a user for Rig with read access only, and allow Rig to reach the server through your firewall.

    Step 1: Create a read-only user for Rig

    Pick the section that matches your sign-in method.

    1. SQL login: create a login and database user for Rig and give it read access to the tables you want to sync, for example the db_datareader role.
    2. Service principal: register an Entra app, create a client secret, and note the tenant ID and the application (client) ID.
    3. For a service principal, create a contained user for the app in the database with CREATE USER [app name] FROM EXTERNAL PROVIDER; and give it read access with ALTER ROLE db_datareader ADD MEMBER [app name];.
    4. For Azure SQL, Microsoft requires the logical server's identity to hold the Directory Readers role when a service principal creates the user.

    Step 2: Connect it in Rig

    Adding a source needs a workspace owner, or a role with the Manage connections permission, on a workspace where you manage your own connections. If the Connectors tab says “Data ingestion for this workspace is managed by Rig”, ask an owner or Rig support to add the source.

    1. In Rig, open Connections, choose the Connectors tab and go to Sources & actions.
    2. Search for Microsoft SQL Server / Azure SQL, open the card and click Connect.
    3. Dialect: leave it as it is, mssql+pyodbc.
    4. Host: your server address, such as myserver.database.windows.net.
    5. Port: leave it at 1433 unless your server uses another port.
    6. Database name: the database to read.
    7. Schema: leave it as dbo unless your tables live in another schema.
    8. SQL login username: for SQL login sign-in only. Leave it blank when you use a service principal.
    9. SQL login password: the password for that login.
    10. Entra tenant ID: for a service principal only: the directory (tenant) ID of the app registration.
    11. Entra application (client) ID: for a service principal only: the application (client) ID.
    12. Entra client secret: for a service principal only: the client secret value.
    13. Trust server certificate: leave it blank. Enter yes only for a self-hosted server with a self-signed certificate.
    14. Click Continue. Rig checks the credentials and lists the streams it can reach, so choose the ones you want and click Connect.
    15. The source then syncs on its schedule, which is nightly unless you change it. To load data straight away, click Sync on its row; Test pulls a small sample first if you want to check the connection.

    What Rig pulls in

    SQL Server lands in its own mssql schema in your warehouse:

    • Tables in the schema: the tables Rig can reach, which you choose from a list when you connect. The tables depend on your own database.

    Incrementally synced tables of up to 5,000,000 rows are re-read in full once a week so deletes are tracked.

    Good to know

    • That weekly re-read adds read load on your database. Larger tables, and tables whose size the database cannot estimate, are not re-read, so their deletes are not tracked.
    • Fill in one sign-in method only. Leave the SQL login fields blank for a service principal, and the Entra fields blank for a SQL login.
    • Connections are encrypted, and the trust setting exists only for self-signed certificates on servers you run.
    • To disconnect SQL Server, click the bin icon on the source's row under Sources & actions. Only whoever connected the source or a workspace owner can do this, and data already synced stays in the warehouse.

    What people use this for

    • Query SQL Server data without T-SQL
    • Replicate SQL Server into a cloud warehouse
    • Work with Azure SQL the same way
    • Ask SQL Server questions in Claude or ChatGPT

    More ideas for using Microsoft SQL Server

    Was this guide helpful?