Articles in this section

See more

Microsoft SQL Server + Smart Ingest

Smart Ingest lets you pull data stored in your Microsoft SQL Server data warehouse and import to Iterable. Most of the setup occurs in Iterable, but your Azure adminstrator needs to allow Smart Ingest IP addresses in your Azure account, and provide connection details that you can enter in Iterable.

NOTE

Smart Ingest can only import data from Microsoft SQL Server.

In this article

Getting started

There are different ways to connect to Microsoft SQL Server, depending on your organization's configuration and needs. Work with your Azure administrator to determine the following:

  • Authentication credentials - There are two ways to authenticate with Azure: with a username and password, or with a connection string.

    • Username and password - A user with access to the SQL database. At minimum, this user must have read access to the data you wish to sync.

    • Connection string - A connection string provided by Azure. This string contains all the information Smart Ingest needs to connect to your Microsoft SQL Server account and can be fully customized to your configuration needs. Note that this string contains a username and password, so the authentication methods are the same as with the username and password, however the connection string is fully customizable.

  • Connection type - Smart Ingest connects to Microsoft SQL Server using a direct connection or an SSH tunnel. To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.

    Both authentication methods support a direct connection from Smart Ingest to Microsoft SQL Server via URL or IP address.

    When you need to connect using an SSH tunnel, select the password authentication type. SSH tunnels cannot be set up when using a connection string.

Allowing Smart Ingest IPs in Azure

Azure protects your data by preventing unknown external IP addresses from connecting to your SQL Server. Regardless of how you're authenticating, Azure administrators should add Smart Ingest IP addresses as allowed IPs in Azure firewall settings.

Below are standard instructions for updating firewall rules for a SQL database in Azure. Your organization's firewall setup may be different.

To allow Smart Ingest IPs in Azure:

  1. Log in to your Azure Console Dashboard.

  2. Under Services, find SQL databases.

  3. Select the SQL database you want to connect.

  4. Select Set Server Firewall.

  5. Under Public Network Access, click on Selected Networks.

  6. Scroll to Firewall Rules and click Add a Firewall Rule.

  7. Allowlist the Smart Ingest IPs for your region.

  8. Click Save.

Password authentication

When you authenticate to Microsoft SQL Server with a username and password, you can connect directly to your Microsoft SQL Server account's server by providing a URL or IP address, or you can use an SSH tunnel to connect.

Requirements for connecting with a username and password

To connect to Microsoft SQL Server with a username and password, you need the following information from your Azure administrator:

  • Host – The hostname or IP address of your Microsoft SQL Server.

  • Port – The port number of your Microsoft SQL Server. The default is 1433, but yours may be different.

  • Database – The name of the database in your Microsoft SQL Server workspace.

  • Username – A user with access to the SQL database. At minimum, this user must have read access to the data you wish to sync.

  • Password – The password for the user specified above.

You can optionally enter a Request timeout duration and whether to Trust server certification.

Connecting Microsoft SQL Server with a password

To connect Smart Ingest to Microsoft SQL Server with a username and password:

  1. Log in to Iterable as a user with the Manage Integrations project permission and open the project you’re working on.

  2. Go to Integrations > Smart Ingest.

  3. Click Connect a New Source.

  4. Select Microsoft SQL Server and click Continue.

  5. In Step 1, select the Password authentication type.

  6. In Step 2, select the connection type your sync should use to connect:

    • Connect directly to SQL Server. This creates a direct connection with no additional setup needed.

    • Connect via SSH Tunnel. This connects using SSH. Add a standard or reverse tunnel, or select an existing tunnel if you’ve already set one up.

      To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.

  7. In Step 3, enter your SQL Server account details: Host, Port, and Database.

  8. Next, enter your user credentials for SQL Server: Username and Password.

  9. Click Continue. Smart Ingest automatically tests the connection.

  10. When the connection test is successful, click Continue. (If there are problems connecting, click Back and review the connection details for accuracy.)

  11. Add a name for your data source to display in Iterable.

  12. Click Finish.

Connection string authentication

You can connect to Microsoft SQL Server quickly by entering a connection string that Azure provides. This string contains all the information Smart Ingest needs to connect to your Microsoft SQL Server account and can be fully customized to your configuration needs.

To learn more about connection strings for Microsoft SQL Server, read Connection Strings in ADO.NET (Microsoft).

Step 1: Find your connection string in Azure

To find your connection string, an Azure administrator must:

  1. Go to the Azure portal dashboard and select SQL Databases.

  2. Find the SQL database you want to connect to and open its settings.

  3. From the left side menu, go to Settings > Connection Strings.

  4. Find the string for ADO.NET (SQL Authentication).

    Example connection string:

    Server=tcp:<YOUR-SERVER-NAME>.database.windows.net,1433;Initial Catalog=my-sql-database;Persist Security Info=False;User ID=sqladminuser;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

  5. Copy the connection string.

Note that this string contains a placeholder for a password ({your_password}) that you must replace with the SQL user's actual password when connecting.

Step 2: Connect Microsoft SQL Server with a connection string

Once you have a connection string from Azure, you can connect Smart Ingest to Microsoft SQL Server.

To connect Smart Ingest with Microsoft SQL Server using a connection string:

  1. Log in to Iterable as a user with the Manage Integrations project permission and open the project you’re working on.

  2. Go to Integrations > Smart Ingest.

  3. Click Connect a New Source.

  4. Select Microsoft SQL Server and click Continue.

  5. In Step 1, select the Connection String authentication type.

  6. In Step 2, enter your connection string. Make sure to replace the placeholder for the password with the user's actual password.

    NOTE

    Because the connection string contains sensitive information, keep it secure and don't share it with others. Once you enter your connection string and click Continue, Smart Ingest obscures the string so it no longer appears to users.

    If you need to update the connection string, you must re-enter it. The existing string is not visible in the UI for security reasons.

  7. Click Continue. Smart Ingest automatically tests the connection.

  8. When the connection test is successful, click Continue.

    If there are problems connecting, first try again. If you still can't connect, click Back and re-enter the connection string.

  9. Add a name for your data source to display in Iterable.

  10. Click Finish.

Next steps

You've now connected your data warehouse to Smart Ingest. The next thing you can do is create a sync.

Troubleshooting

Cannot open server

If you see the following error message, you need to update your firewall settings in your Azure Console:

Cannot open server 'hightouch-test' request by login.
Client with IP address '54.196.30.169' is not allowed to access the server.
To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule
on the master database to create a firewall rule for this IP address or address range.
It may up take up to five minutes for this change to take effect.

Related articles