Repository Database in Azure SQL Databaselink
Fivetran HVR allows you to create repository database in Azure SQL Database.
It is recommended to create a new database (schema) for HVR repository. If an existing database is to be used for HVR repository, then the HVR repository tables can be separated by creating a new database schema and associating it with HVR's user as follows:
create schema schemaname;
Grants for Repository Databaselink
The following grants are required for the repository database user in Azure SQL Database:
grant create table to username; grant create procedure to username; grant select, insert, delete, update on schema::schemaname to username; grant control on schema::schemaname to username; alter user username with default_schema=schemaname;
Repository Database Connectionlink
This section describes the details required for connecting to the repository database in Azure SQL Database:
Field | Description | Equivalent Hub Server Property |
---|---|---|
SERVER | Fully qualified domain name (FQDN) of the Azure SQL Database. Example: cbiz2nhmpv.database.windows.net | SqlServer_Server |
DATABASE | Name of the database in Azure SQL Database. Example: mytestdb | Database_Name |
AUTHENTICATION METHOD | Authentication method for connecting HVR to Azure SQL database. Available options are:
| SqlServer_Authentication_Method |
OAUTH2 ENDPOINT | URL used for obtaining the bearer token with credential token. This field is available only if the AUTHENTICATION METHOD is set to Access Token. | Azure_OAuth2_Endpoint |
CLIENT ID | Client ID used to obtain Microsoft Entra ID (formerly Azure Active Directory) access token. This field is available only if the AUTHENTICATION METHOD is set to Access Token. | Azure_OAuth2_Client_Id |
CLIENT SECRET KEY | Secret key of the CLIENT ID. This field is available only if the AUTHENTICATION METHOD is set to Access Token. | Azure_OAuth2_Client_Secret |
USER | User name and host name of the Azure SQL Database server. The format to be used is username@hostname. Example: user@cbiz2nhmpv This field is available only if the AUTHENTICATION METHOD is set to User and Password or Microsoft Entra ID. | Database_User |
PASSWORD | Password for the USER. This field is available only if the AUTHENTICATION METHOD is set to User and Password or Microsoft Entra ID. | Database_Password |
Advanced Settingslink
Field | Description | Equivalent Hub Server Property |
---|---|---|
LINUX / UNIX ODBC DRIVER MANAGER LIBRARY PATH | Directory path where the ODBC Driver Manager Library is installed. This field is applicable only for Linux/Unix operating system. For a default installation, the ODBC Driver Manager Library is available at /usr/lib64 and does not need to be specified. However, when UnixODBC is installed in for example /opt/unixodbc the value for this field would be /opt/unixodbc/lib. | ODBC_DM_Lib_Path |
ODBCSYSINI | Directory path where the odbc.ini and odbcinst.ini files are located. This field is applicable only for Linux/Unix operating system. For a default installation, these files are available at /etc directory and do not need to be specified using this field. However, when UnixODBC is installed in for example /opt/unixodbc the value for this field would be /opt/unixodbc/etc. The odbcinst.ini file should contain information about the Snowflake ODBC Driver under the heading [ODBC Driverversionfor SQL Server]. | ODBC_Sysini |
ODBC DRIVER | Name of the user defined (installed) ODBC driver used for connecting HVR to the Azure SQL Database. | ODBC_Driver |