Skip to main content

​
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:
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 uploaded files like CSVs 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:
    Grant Omni user permissions to upload schema
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 sidebar, 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:
    ​
    required
    A user-friendly name for the connection, which will be used throughout Omni.
    ​
    required
    The address of the database server.
    ​
    default:"1433"
    required
    The port number for the database connection.
    ​
    required
    The database to connect to.
    ​
    required
    The username for database authentication.
    ​
    required
    The password for database authentication.
    ​
    Whether to trust the server's SSL/TLS certificate without validation. Enable this option when connecting to a database that uses a self-signed certificate or a certificate signed by an internal certificate authority that Omni cannot otherwise verify.
    ​
    Sends queries to a read-only replica when the database has one. Table uploads still write to the primary. When you test the connection, Omni checks whether read-only routing is actually in effect.
    ​
    required
    The timezone used by the database.
    ​
    required
    The timezone to use for Omni queries. If a timezone is specified, data will be converted from the Database Timezone to the selected timezone.
    ​
    When enabled, users can override the connection timezone with their own when querying.
    ​
    default:"enabled"
    When enabled, Omni will automatically infer relationships between tables based on column name matching during schema refreshes. Defaults to enabled.
    ​
    default:"900"
    Maximum time in seconds before a query times out.
    ​
    required
    Select the minimum level of access users should have to models in the connection.
  4. When finished, click Create connection.

​
What’s next?

Now that your database is set up, you can: