SQL DB Advanced Connection Strings
For Controller and Server UI persistence, use advanced connection strings to connect to SQL DB.
Refer to Connection String Syntax for connection string syntax tips from Microsoft.
Controller Persistence Strings
Go to Alteryx System Settings > Controller > Persistence.
For SQL Connection, use the following string and replace the [bold red text] with your own values from your SQL instance details – brackets are not necessary for these parts of the string:
If you’re using SQL Server authentication, use the following string:
Driver={ODBC Driver 17 for SQL Server};Server=[Fully qualified SQL host name];UId=[user];PWD=[user password*];Integrated Security=False;Database=[UserChosenDBName**];Nota
*The user password cannot contain the following characters: “ ' ; = { } ` (double quote, single quote, semi-colon, equals sign, left curly brace, right curly brace, and backtick).
**This is the AlteryxService database. We recommend using separate databases for AlteryxService and Alteryx Server UI, so this database name should be different than the one specified in Server UI > Persistence.
If you’re using Kerberos and WinAuth, use the following string:
DRIVER={ODBC Driver 17 for SQL Server};Database=AlteryxService;Server=[Fully qualified SQL host name];Trusted_Connection=yes
On-Premise example without SSL/TLS:
When using SQL Server Authentication:
Example:
Driver={ODBC Driver 17 for SQL Server};Server=sample.server.com;UId=sa;PWD=Pa55word;Integrated Security=False;Database=AlteryxService;When using Kerberos and WinAuth:
Example:
DRIVER={ODBC Driver 17 for SQL Server};Database=AlteryxService;Server=sample.server.com;Trusted_Connection=yes
On-Premise example with SSL/TLS:
When using SQL Server Authentication:
Example:
Driver={ODBC Driver 17 for SQL Server};Database=AlteryxService;Server=sample.server.com;Uid=sa;Pwd=Pa55word;TrustServerCertificate=no;Encrypt=yes;Integrated Security=False;When using Kerberos and WinAuth:
Example:
DRIVER={ODBC Driver 17 for SQL Server};Database=AlteryxService;Server=sample.server.com;Trusted_Connection=yes;TrustServerCertificate=no;Encrypt=yes;
Server UI Persistence Strings
Importante
The connection string for the Server UI is different than the connection string for the Controller.
Additionally, a MultipleActiveResultSets (MARS) flag will be automatically added to this connection string. Manually adding a MARS flag will result in an error. Alteryx Server needs this flag to perform complex querying. Without this flag, several operations would not be possible and Server would not be fully functional. For more information on this flag, please visit Multiple Active Result Sets (MARS).
Go to Alteryx System Settings > Server UI > Persistence.
For SQL Connection, use the following string and replace the [bold red text] with your own values from your SQL instance details – brackets are not necessary for these parts of the string:
Nota
The Server UI connection string should not specify the Driver.
If you’re using SQL Server authentication, use the following string:
Server=[Fully qualified SQL host name],1433;Database=[UserChosenDBName**];User Id=[user];Password=[user password*];Nota
*The user password cannot contain the following characters: “ ' ; = { } ` (double quote, single quote, semi-colon, equals sign, left curly brace, right curly brace, and backtick).
**This is the Server UI database. We recommend using separate databases for AlteryxService and Alteryx Server UI, so this database name should be different than the one specified in Controller > Persistence.
If you’re using Kerberos and WinAuth, use the following string:
Server=[Fully qualified SQL host name];Database=AlteryxGallery;Trusted_Connection=yes;MultipleActiveResultSets=true;If you’re setting up SSL/TLS, add TrustServerCertificate=no;Encrypt=yes; to the end of your connection string for SQL Server, Kerberos, and WinAuth Authentication.
On-Premise example without SSL/TLS:
When using SQL Server Authentication:
Example 1:
Server=127.0.0.1,1433;Database=AlteryxGallery;User Id=sa;Password=pa55word;Example 2:
Server=sample.server.com,1433;Database=AlteryxGallery;User Id=user123;Password=pa55word;When using Kerberos and WinAuth:
Example:
Server=sample.server.com;Database=AlteryxGallery;Integrated Security=true;MultipleActiveResultSets=true;
On-Premise example with SSL/TLS:
When using SQL Server Authentication:
Example 1:
Server=127.0.0.1,1433;Database=AlteryxGallery;User Id=sa;Password=pa55word;TrustServerCertificate=no;Encrypt=yes;Example 2:
Server=sample.server.com,1433;Database=AlteryxGallery;User Id=user123;Password=pa55word;TrustServerCertificate=no;Encrypt=yes;When using Kerberos and WinAuth:
Example:
Server=sample.server.com;Database=AlteryxGallery;Trusted_Connection=yes;MultipleActiveResultSets=true;TrustServerCertificate=no;Encrypt=yes;
Unique Connection Strings for Workers
Each worker can have a unique connection string that determines how it connects to the database. This allows remote worker nodes to use connections that differ from the controller to set credentials or options specific to a particular worker.
To set unique connection strings for a worker:
Sign in to the worker for which you want to set a different SQL DB connection string than the controller.
Open Command Prompt or PowerShell (as administrator).
From the prompt navigate to
C:\Program Files\Alteryx\bin.Enter this command:
AlteryxService.exe setsqlconnectionworker="{sqlConnectionString}"Replace
{sqlConnectionString}with the appropriate connection string for your environment. This connection string must connect to the same database as your controller. To see examples of various connection string formats, go to the Controller Persistence Examples section.To apply the setting, restart AlteryxService:
Navigate to the Windows Services application on your machine.
Find the service named AlteryxService.
Select the Restart the Service button.