SQL Server Connection

This is a step-by-step tutorial for creating a SQL Server connection.

Prerequisites

• You must first create a new user for Rivery in your database, which you can do by copying the following commands:

CREATE LOGIN [ < RiveryUsername>] WITH PASSWORD='< myPassword >'

Note: Set up your password following the review of the Microsoft Password Policy.

Use the database from which you want to pull data. Replace <database> with the name of your current database:

USE [< database >]

Allow the new user you created above to access the database:

CREATE USER [< RiveryUsername >] FOR LOGIN <database>

Now grant the table or schema you want to extract data from to the new user you just created. Change the <database>, <schema>, and <table> to your existingdatabse, schema, and table names:

GRANT SELECT to [< RiveryUsername >]  

Use the following syntax to grant a SELECT operation to a specific schema:

GRANT SELECT on SCHEMA :: [< schema >] TO [< RiveryUsername >]

Grant Rivery view permissions to the metadata of the tables in order to get the correct mapping of the tables in the schema or database:

GRANT VIEW ANY DEFINITION to  [< RiveryUsername >]

• If you want to connect using Azure Active Directory, make sure you configure and manage Azure AD authentication with Azure SQL.

Establishing a Connection

You can connect to SQL Server using one of three methods:

• SQL Server Authentication
• Azure Active Directory
• SSH Tunnel

SQL Server Authentication

1. Whitelist our IPs.
2. Choose your Connection Name.
3. Enter Host.
4. Fill in the Port Number.
5. Enter your Database.
6. Select SQL Server Authentication method.
7. Enter your Username and Password
8. Use the Test Connection function to see if your connection is up to the task.
   If the connection succeeded, you can now use this connection in Rivery.

Azure Active Directory

1. Whitelist our IPs.
2. Choose your Connection Name.
3. Enter Host.
4. Fill in the Port Number.
5. Enter your Database.
6. Select Azure Active Directory authentication method.
7. Enter your Username and Password
8. Use the Test Connection function to see if your connection is up to the task.
   If the connection succeeded, you can now use this connection in Rivery.

SSH Tunnel

To connect via SSH Tunnel, follow our step-by-step tutorial.