Configure access to Azure Synapse configure-access-to-azure-synapse

First, install the ODBC Driver. You can find it in this page.

sudo su
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
# Uninstall if already installed Unix ODBC driver
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts

sudo ACCEPT_EULA=Y yum install msodbcsql

sudo ACCEPT_EULA=Y yum install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# the Microsoft driver expects unixODBC to be here /usr/lib64/libodbc.so.1, so add soft links to the '.so.2' files
cd /usr/lib64
sudo ln -s libodbccr.so.2   libodbccr.so.1
sudo ln -s libodbcinst.so.2 libodbcinst.so.1
sudo ln -s libodbc.so.2     libodbc.so.1

# Set the path for unixODBC
export ODBCINI=/usr/local/etc/odbc.ini
export ODBCSYSINI=/usr/local/etc
source ~/.bashrc

#Add a DSN information to /etc/odbc.ini
sudo vi /etc/odbc.ini

#Add the following:
[Azure Synapse Analytics]
Driver      = ODBC Driver 13 for SQL Server
Description = Azure Synapse Analytics DSN
Trace       = No
Server      = [insert your server here]

If needed, you can install unixODBC development headers by running the following command:

sudo yum install unixODBC-devel

After installing the drivers, you can test and verify your ODBC Driver and query your database if needed. Run the following command:

/opt/mssql-tools/bin/sqlcmd -S yourServer -U yourUserName -P yourPassword -q "your query" # for example -q "select 1"

In Campaign, you can then configure your Azure Synapse external account. For more on how to configure your external account, refer to this section.

Since Azure Synapse Analytics communicates through the TCP 1433 port, you need to open up this port on your firewall. Use the following command:

firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="[server_ip_here]/32" port port="1433" protocol="tcp" accept'
# you can ping your hostname and the ping command will translate the hostname to IP address which you can use here

In case of iptables, run the following command:

iptables -A OUTPUT -p tcp -d [server_hostname_here] --dport 1433 -j ACCEPT

First, install the Microsoft ODBC driver. You can find it in this page.

Choose the following files to install:

your_language\your_architecture\msodbcsql.msi (i.e: English\X64\msodbcsql.msi)

Once your ODBC driver is installed, you can test it if needed. For more on this, refer to this page.

In Campaign Classic, you can then configure your Azure Synapse external account. For more on how to configure your external account, refer to this section.

Since Azure Synapse Analytics communicates through the TCP 1433 port, you need to open up this port on the Windows Defender Firewall. For more on this, refer to Windows documentation.

First, install the Microsoft ODBC driver for SQL Server. Use the following commands to install the ODBC Driver 13.1 for SQL Server:

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/debian/8/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql

If you get the following an error “The method driver /usr/lib/apt/methods/https could not be found” when calling sudo apt-get update, you should run the command:

sudo apt-get install apt-transport-https ca-certificates

You now need to install mssql-tools with the following commands. Mssq-tools are needed to use the bulk copy program (or BCP) utility and to run queries.

sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

If needed, you can install unixODBC development headers by running the following command:

sudo yum install unixODBC-devel

After installing the drivers, you can test and verify your ODBC Driver and query your database if needed. Run the following command:

/opt/mssql-tools/bin/sqlcmd -S yourServer -U yourUserName -P yourPassword -q "your query" # for example -q "select 1"

In Campaign Classic, you can now configure your Azure Synapse external account. For more on how to configure your external account, refer to this section.

To configure iptables on Debian to ensure the connection with Azure Synapse Analytics, enable the outbound TCP 1433 port for your hostname with the following command:

iptables -A OUTPUT -p tcp -d [server_hostname_here] --dport 1433 -j ACCEPT

From Campaign Explorer, click Administration ‘>’ Platform ‘>’ External accounts.

Click New.

Select External database as your external account’s Type.

Under Configuration, select Azure Synapse Analytics from the Type drop-down.

Configure the Azure Synapse external account:

• For standard authentication, you must specify:

  • Server: URL of the Azure Synapse server

  • Account: Name of the user

  • Password: User account password

  • Database: Name of the database

  

• For System-assigned managed identity authentication, you must specify:

  • Server: URL of the Azure Synapse server

  • Database: Name of the database

  • Options: Add the following syntax Authentication=ActiveDirectoryMsi

  

Click Save.