Skip to main content
In this guide, you’ll learn how to connect your Microsoft SQL Server database to Omni.

​
Requirements

To follow the steps in this guide, you’ll need:
  • Organization Admin permissions in Omni
  • Microsoft Azure Portal Admin permissions, which are required to allowlist Omni’s IP addresses

​
Setup

1

Retrieve connection details

  1. Navigate to your Microsoft Azure Portal.
  2. In the portal, navigate to the database you want to connect to Omni.
  3. On the database’s details page, locate the Connection strings field: Connection strings link
  4. Click the Show database connection strings link.
  5. On the page that displays, click the JDBC tab: Connection string
  6. You should see something like the following, which will contain all the information you need: 
    jdbc:sqlserver://omni-test-admin.database.windows.net:1433;
database=e-commerce;
user=omni-test-admin@omni-test-admin;
password={your_password_here};
encrypt=true;
trustServerCertificate=false;
hostNameInCertificate=*.database.windows.net;
loginTimeout=30;
2

Set up a table upload schema

This step is optional. However, we recommend completing it as part of the initial set up or you won’t be able to use CSV uploads in joins.
In this step, you’ll create a dedicated schema to use for table uploads. This schema can’t be used for other modeled tables.
  1. Create the schema in Microsoft SQL Server. This can be in its own database or the same database as other schemas to be used in modeling.
  2. Run the following commands to grant the Omni user the required privileges:
    Grand Omni user permissions to upload schema
    GRANT CREATE TABLE TO omni;
GRANT ALTER, INSERT, SELECT, UPDATE ON SCHEMA::upload_schema_name TO omni;
3

Allowlist Omni's IP addresses

If access to the SQL Server database is limited by IP address, you’ll need to add Omni’s IPs to the allowlist before you create the database connection.Omni’s IP addresses can be found on an individual connection’s page, accessed by navigating to Settings > Connections and clicking a connection.
  1. Navigate to your Microsoft Azure Portal.
  2. In the left navigation, click the Networking option under Security.
  3. In the Firewall rules section, click Add a firewall rule: Add firewall rule
  4. Create a rule for each of Omni’s IP addresses: Firewall rule input
4

Create the connection in Omni

  1. In Omni, click Settings > Connections.
  2. Click the Microsoft SQL Server option.
  3. On the connection setup page, fill in the connection details:
    NameRequired?Description
    Display Name YesA user-friendly name for the connection, which will be used throughout Omni
    Host YesThe hostname or IP address of the database server
    Port YesThe port number for the database connection. Defaults to 1433.
    Database YesThe database to connect to
    Include Schemas NoA comma-separated list of schemas to include in the connection
    Offloaded Schemas NoA comma-separated list of schemas to include in the connection, which will only load on demand. This setting is good for very large schemas or dbt dev schemas.
    Schema for Table Uploads NoThe name of the schema to use for table (CSV) uploads. If left blank, you can upload tables but they won’t be pushed to the database or be available for use in joins.
    Default Schema YesThe default schema to use for queries. Defaults to dbo.
    Username YesThe username for database authentication
    Password YesThe password for database authentication
    Trust Server Certificate NoWhether to trust the server’s SSL/TLS certificate without validation
    Database Timezone YesThe timezone used by the database
    Query Timezone YesThe timezone to use for queries
    Allow User-Specific Timezones NoAllow users to override the connection timezone with their own
    Query Timeout NoMaximum time in seconds before a query times out
  4. When finished, click Create connection.

​
What’s next?

Now that your SQL Server is set up, you can: