Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Connect Knowledge Base

Definitive answers from Connect experts.

Deploy Alteryx Connect on MS SQL Database

The purpose of this article is to describe steps how to deploy Connect on the MS SQL database.
This article is valid for Alteryx Connect versions 2020.1 and higher.

  1. Database should be case insensitive with UTF8 support (LATIN1_GENERAL_100_CI_AS_SC_UTF8).
  2. Empty database “std_xforms” exists.
  3. Schema “std_xforms” exists.
  4. User “std_xforms” with the default database “std_xforms” exists, user has assigned role “db_owner”. 
  5. Test database connection with your SQL client to make sure your database is accepting TCP/IP connection and your username and password is working. 
Installation Steps
  1. Run installer and make sure to uncheck "Start the Alteryx Connect service now".  If start-up happened, stop instance, remove .done files in ac_init folder and delete default h2 database files.
idea Skyscrapers
  1. Copy the driver mssql-jdbc-7.4.1.jre11.jar ( ) to C:\Program Files\AlteryxConnect\lib\ .
  2. Update the connection string in as follows:
NOTE: “localhost” will be correct only in case you are running MS SQL locally. Otherwise you need to enter hostname/IP address of your MS SQL server.
Here is the full properties file:
  1. Start the windows service “Alteryx Connect” and wait for the initialization of the database (this can take up to 10 minutes).
  2. After the database is initialized, you should be able to connect to UI (such as http://localhost/). 
If you are not able to connect to UI, follow these steps:  
  1. Check logs (../connect_catalina.log).
  2. Check tables are created in the selected schema.
idea Skyscrapers

Check your connection string is OK.
Appendix: Installing and Setting Up Microsoft SQL Server Step by Step Guide
  1. Mssql basic install (180 days evaluation version) (SQL2019-SSEI-Eval).  
  2. Basic installation is sufficient.
  3. After mssql server is installed, set port to fixed and enable TCP/IP:
    Open the SQL Server Configuration Manager.
    In SQL Server Network Configuration select Protocols for [yourServerInstance].
    In the right-hand pane, make sure that TCP/IP is Enabled.
    Open the 1433 port in your firewall.
    In the SQL Server Configuration Manager, right-click TCP/IP and select Properties.
    Select the IP Addresses tab and make sure the TCP Port for IP1 is 1433.
    Make sure that’s not configured to DYNAMIC (on old SQL server versions).
  4. Download and install SQL Server Management Studio (SSMS):
    Login with windows authentication
    or when using advanced settings configure password for sa user.
  5. Configure and change the authentication mode to use also sql server creds:
    Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MS SQL is set up to allow both Windows and SQL Authentication.
  6. Recommended step: Unlock user sa in Security/Logins/select sa/properties/tab Status/login enabled and and set up password in the General tab.
  7. Create database "std_xforms", select owner "default", collation needs to be UTF8. So, select some local collation ending with _UTF (tested with unicode characters with LATIN1_GENERAL_100_CI_AS_SC_UTF8).  The collation must be _CI (case insentitive) and _UTF8.
    See MSFT whitepaper .  
    idea Skyscrapers
  8. Create login server/Security/Logins new login /set default database std_xforms Security/Logins/create new user "std_xforms". Select SQL Server authentication and Default database.idea Skyscrapers
  9. Create database user “std_xforms” server/Databases/std_xforms/Secutity/users.idea Skyscrapers
  10. Create schema std_xforms. server/Databases/std_xforms/Security/schemas/create new.idea Skyscrapers
  11. Create user mappings on login properties and assign role membership server/Security/Logins/std_xforms properties.idea Skyscrapers

Known Errors Troubleshooting:
  • The user should not be member of Sysadmin server role: Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default. So tables are not created in std_xforms schema but in dbo schema
  • Wrong database collection: Instead of case insensitive, case sensitive was selected while creating a new database (it can be checked if you right-click on the database and select Properties). idea Skyscrapers
 Collation can be selected while creating a database in New Database > Options > Collation: idea Skyscrapers
Or on an existing database in Database Properties:
idea Skyscrapers
No ratings

Hi Mattt


This is great. And thanks for putting this KB out. Quick question. How easy/feasible it will be to upgrade Connect instance to 2020.1 and migrate from H2 to SQL Server back-end?


Hi @Nikul-TheFirstMile ,

An actual migration of preexisting content from H2 to MSSQL is not yet supported, though the development team is looking into this as a possibility for an upcoming release.