SQL Server

To create a SQL Server database that you can use as a Destination in Hevo, you must have an active SQL Server instance. The steps to set up an SQL Server instance depend on whether you are using an on-premise SQL Server or an Azure SQL Database.

Set up an on-premise SQL Server Instance

Set up an Azure SQL Database logical server

Once you have set up your SQL Server instance, create a database in it to use as a Destination. The steps to create a database depend on whether you are using an on-premise SQL Server or an Azure SQL Server.

Create a database in on-premise SQL Server

Create a database in Azure SQL Server

To allow Hevo to connect to your on-premise SQL Server, perform the following steps:

1. Enable the TCP/IP port

2. Verify the TCP/IP port

3. Restart the SQL Server instance

4. Create inbound rules

1. Enable the TCP/IP port

The TCP/IP protocol enables Hevo to communicate with your SQL Server instance over the network. To do this:

1. Open the SQL Server Configuration Manager.

2. In the left pane, click SQL Server Network Configuration, and then double-click Protocols for <Your SQL Server Instance Name>. The default instance names are MSSQLSERVER and MSSQLSERVER01.

   

3. In the right pane, if the Status field of the TCP/IP Protocol Name is Disabled, then perform this step. Else, skip to the Verify the TCP/IP port section.

   1. Right-click TCP/IP and select Enable.

      

   2. Click OK to acknowledge the dialog box that warns you to restart the services for the changes to take effect.

2. Verify the TCP/IP port

After enabling TCP/IP, verify the port on which your SQL Server instance is listening for connections. To do this:

1. Right-click the TCP/IP Protocol Name and select Properties.

   

2. In the TCP/IP Properties dialog, click the IP Addresses tab.

3. Scroll down to the IPAll section and note the TCP Port for your SQL Server instance.

   

4. Click OK.

3. Restart the SQL Server instance

Once you have verified the TCP/IP port, restart the SQL Server instance for the changes to take effect. To do this:

1. In the left pane, click SQL Server Services.

2. In the right pane, right-click the SQL Server (<Your SQL Server Instance Name>) and select Restart.

   

You have successfully enabled TCP/IP for your SQL Server instance.

4. Create inbound rules

After restarting the SQL Server instance, create an inbound rule to allowlist Hevo’s IP addresses for your region. To do this:

1. Press Windows + R to open the Run dialog box.

2. Type wf.msc in the Open field and click OK.

   

3. In the window that appears, click Inbound Rules in the left pane, and then click New Rule in the right pane.

   

4. In the Rule Type dialog, select Custom and click Next.

   

5. In the Program dialog, select This program path, specify the path to the sqlservr.exe file, and then click Next.

   Note: The sqlservr.exe file is generally found in the %ProgramFiles%\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\ folder.

   

6. In the Protocol and Ports dialog, do the following:

   

   1. Select TCP from the Protocol type drop-down.

   2. Select Specific Ports from the Local port drop-down.

   3. Specify the port on which your SQL Server listens for connections.

      Note: By default, SQL Server listens for connections on port 1433.

   4. Click Next.

7. In the Scope dialog, select These IP addresses and click Add.

   

8. In the IP Address window that appears, enter Hevo’s IP address for your region and click OK.

   

9. (Optional) Click Add and repeat step 8 to allowlist all the IP addresses for your region.

10. Click Next.

11. In the Action dialog, ensure that Allow the connection is selected and click Next.

    

12. In the Profile dialog, ensure that all the check boxes are selected and click Next.

    

13. In the Name dialog, specify a name for your inbound rule and click Finish.

    

Perform the following steps to create the firewall rules in your SQL database network settings to allowlist the Hevo IP addresses for your region:

1. Log in to the Azure Portal.

2. Under Azure services, select More services.

   

3. On the All services page, search for and select All resources.

   

4. On the All resources page, click the SQL database you want to connect to Hevo.

   

5. In the right pane of <Your Database Name> page, click Set server firewall.

   

6. On the Networking page, under the Public access tab, select the Selected networks option.

   

7. Under Firewall rules, click + Add a firewall rule.

   

8. In the Add a firewall rule dialog box, do the following to add your firewall rule:

   

   1. Specify the following:

      • Rule name: A name to identify the rule. For example, HevoIndia.

      • Start IP: The starting address of the IP range.

      • End IP: The ending address of the IP range.

      Note: As Hevo has specific IP addresses and not a range, the value in the Start IP and End IP fields is the same. For example, 13.235.131.126 for the India region.

   2. Click OK.

9. Repeat steps 7 and 8 to add all the IP addresses for your Hevo region.

10. Click Save to save the firewall rules.

    

Note: This section is applicable only if you want to configure your SQL Server Destination using Microsoft Entra ID authentication.

Microsoft Entra ID is a cloud-based identity and access management service that allows you to create and manage application identities called Service Principals, which can be granted access to your Azure resources. A Service Principal acts as a dedicated identity for an application, similar to how a user account represents a person.

To connect your SQL Server Destination to Hevo using Microsoft Entra ID authentication, perform the following steps:

1. Register a Service Principal in Microsoft Entra ID

2. Generate a Client Secret for the Service Principal

1. Register a Service Principal in Microsoft Entra ID

Perform the following steps to register a Service Principal in Microsoft Entra ID:

1. Log in to the Azure Portal.

2. In the top search bar, search for and select Microsoft Entra ID.

   

3. In the left navigation pane, click Manage, and then click App registrations.

   

4. On the App registrations page, click + New registration.

   

5. On the Register an application page, do the following:

   

   • Name: Specify a unique name for your app. For example, Hevo-SQL-Server-Connector.

   • Supported account types: Select the scope of accounts that can use this application. To know more about the available options, refer to Supported account types in Microsoft Entra ID.

6. Click Register to register the application in Microsoft Entra ID.

7. In the Essentials section, copy and save the Application (client) ID. Use this value as Client ID while configuring your SQL Server Destination in Hevo.

   

2. Generate a Client Secret for the Service Principal

Perform the following steps to generate a Client Secret for the Service Principal:

1. In the left navigation pane, under Manage, click Certificates & secrets.

   

2. In the Client secrets tab, click + New client secret.

   

3. In the Add a client secret slide-in page, do the following:

   

   • Description: Specify a description for your client secret.

   • Expires: Select an expiration period from the drop-down.

4. Click Add.

5. In the Client secrets tab, copy the Value of the secret and save it securely like any other password.

   

   Use this as Client Secret while configuring your SQL Server Destination in Hevo.

Note: Skip to the Grant privileges for Username and Password authentication section if you are using an existing database user.

Perform the following steps to create a database user in your SQL Server database:

1. Connect to your SQL Server database as an admin user with any SQL client tool, such as sqlcmd.

2. Run the following commands:

   -- Select the master database to create a login
   USE master;
   CREATE LOGIN <login_user> WITH PASSWORD = '<password>';
   
   -- Add a database user to your database for the login created above
   USE <database_name>;
   CREATE USER <database_user> FOR LOGIN <login_user>;
   

   Note: Replace the placeholder values in the commands above with your own. For example, <login_user> with hevouser.

To allow Hevo to connect to your Azure SQL database using Microsoft Entra ID authentication, you must create an external user for the Service Principal you registered in the Set up Microsoft Entra ID Authentication section. Perform the following steps to do this:

1. Connect to your Azure SQL database as an admin user with any SQL client tool, such as sqlcmd.

2. Run the following command to create a database user for the Service Principal:

   USE [<database_name>];
   GO
   
   CREATE USER [<service_principal_name>] FROM EXTERNAL PROVIDER;
   

Note: Replace the placeholder values in the commands above with your own. For example, <service_principal_name> with the name of the Service Principal you registered in the Set up Microsoft Entra ID Authentication section.

The following table lists the privileges that Hevo requires to connect to and load data into your SQL Server Destination:

Perform the following steps to grant the required privileges to the database user for creating the database objects needed to load and store your data:

1. Log in to your SQL Server instance as a login user using an SQL client tool, such as sqlcmd. For example, sqlcmd -U hevouser.

2. Enter the following command to access the database in which you created the database user:

   USE <database_name>
   GO
   

3. Enter the following commands to grant privileges to the database user:

   GRANT ALTER, SELECT, INSERT, UPDATE ON SCHEMA::<schema_name> TO <username>
   GO
   GRANT CREATE TABLE, INSERT, SELECT, UPDATE ON DATABASE::<database_name> to <username>
   GO
   

Note: Replace the placeholder values in all the commands above with your own.

To allow Hevo to read data from and write data to your Azure SQL database, you must grant the Service Principal the following privileges:

Perform the following steps to grant these privileges to the Service Principal:

1. Connect to your Azure SQL database as an admin user with any SQL client tool, such as sqlcmd.

2. Run the following commands:

   USE [<database_name>];
   GO
   
   ALTER ROLE db_datareader ADD MEMBER [<service_principal_name>];
   ALTER ROLE db_datawriter ADD MEMBER [<service_principal_name>];
   GRANT CREATE TABLE TO [<service_principal_name>];
   GRANT ALTER ON SCHEMA::<schema_name> TO [<service_principal_name>];
   

   Note: Replace the placeholder values in the commands above with your own. For example, <service_principal_name> with the name of the Service Principal you registered in the Set up Microsoft Entra ID Authentication section.