Microsoft SQL Server

Connect to your Microsoft SQL Server.

Connecting to Microsoft SQL Server in Omni

Gather Information from Microsoft Azure portal.

To see the values for the required parameters in the instructions below, we recommend…

1. Navigating to your Microsoft Azure portal.

   

2. Navigate to the SQL database you want to connect to.

   

3. Select Show database connection strings under the server name.

   

4. Switch to the JDBC tab.

   

5. And voilà, you should be able to see all of the parameters and values 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;

Step-by-Step Guide

1. In Omni, to add a new connection:

   
   1. Navigate to the Admin tab.
   2. Select Connections menu option on the left hand panel
   3. Select the Add Connection button in the top right
   4. Select the Microsoft SQL Server button
   

2. Follow these instructions to allowlist the Omni IPs for your MS SQL database

3. Use this image to follow along for the necessary inputs to connect your database to Omni:

   
   1. Display Name is a required field.
      1. This display name can be whatever name you desire, it will be the name that is displayed on the Connections list on the Omni Connections page.
   2. Host is a required field.
      1. This is listed as Server name in the connection string parameter.
   3. Port is a required field.
      1. The port value is the number after the server name in the connection string.
      2. In the connection string example above, the port number is 1433.
   4. Database is a required field.
      1. Find your database name after the “database name” parameter in the connection string.
      2. In the connection string example above, the database name value is e-commerce.
   5. Include Schemas is an optional field.
      1. If schemas are specified in this field, Omni will only generate schemas for the schemas listed.
   6. Default Schema is a required field.
      1. dbo is the default value for Microsoft SQL databases.
      2. You can navigate to Azure Data Studio to see if there are other schemas to select from.
   7. Username is a required field.
      1. You can find your username in the connection string.
   8. Password is a required field.
      1. This is the password chosen for the SQL user when setting up the database.
   9. Database Timezone is a required field.
      1. The default is UTC. If you modified the timezone settings on your database settings, change this value to match your database settings.
   10. Query Timezone is a required field.
       1. The default value is Do not convert. If any other value is specified, the data Omni outputs will be converted from the database timezone to the query timezone when querying.

Allowlist the IPs

Before you can create a connection to Microsoft SQL Server, if you choose to limit access to your database by IP address, you can find the list of addresses for your environment on the connection page within the application. Be sure to include all of the listed IP addresses in your allowlist.

1. You must have Microsoft Azure Portal admin permissions to add the Omni IP addresses to the allowlist. To allowlist… Navigate to your Microsoft Azure Portal.

   
   1. Select your SQL Database
   2. In the left-hand menu, navigate to the Networking menu option under Security
   3. Select the + Add a firewall rule button
   
   1. Input a desired rule name and the IPs shown in your Omni app in step 1 of Connection to the Microsoft SQL Server in Omni