Connect to a SQL Server Database
This procedure describes the easiest method for connecting to a SQL Server database. If you do not have the appropriate SQL Server driver installed, Designer prompts you to install it.
1. Drag an Input Data tool or Output Data tool onto the canvas and select it.
2. In the Configuration window, select the dropdown arrow, select Data Sources and locate Microsoft SQL Server in the list. Select Quick Connect.
3. Designer checks your system for a SQL Server driver.
- If Designer detects that a driver for connecting to SQL Server exists, the SQL Server Connection window displays. Proceed to the next step.
- If Designer can't find a driver for connecting to SQL Server, Designer provides you with a link to install it. For driver information, visit Microsoft SQL Server 2008, 2012, 2014, 2016.
4. In Connection Name, designate a new connection name or select one that exists. New connections are stored in the Manage Data Connections.
Select an Existing Connection
- You can view and select any System connection or any User connection created by you.
- Data Source Name (DSN) connections configured in the ODBC driver are not listed. To configure ODBC and OLEDB connections, visit ODBC and OLEDB database connections.
Modify an Existing Connection
- When you select an existing connection, you can modify all connection properties except Type.
- If you are an administrator user, you can modify any System connection or any User connection created by you.
- If you are a non-administrator user, you can only modify User connections created by you.
- If you modify a connection property (for example, Host), you will need to re-enter your password.
5. Select a connection Type (new connections only):
- User: Creates a connection that only you can use. User connections are visible only by the user who created them.
- System: Selectable only by admin users. Creates a connection for any user on a machine. System connections are visible to all users.
6. In Host, either search the network for SQL Server hosts or enter a host name.
A network search for SQL Server hosts takes time to execute and may not return a complete list of hosts. If you know the name of the host, Alteryx recommends specifying it manually.
7. Under Authentication Type, select either Windows Authentication or SQL Server Authentication, depending on how the SQL Server is configured.
8. Select Test to test the connection.
9. If the connection test is successful, a list of databases appears in the Default Database dropdown.
- To use the default database as configured by your server admin, leave the dropdown unselected.
- To override the default, select a database from the list.
10. Select OK.
Alternate SQL Server Connection Methods
For ODBC and OLEDB connection instructions, visit ODBC and OLEDB Database Connections.
If you experience performance issues writing to SQL Server, you can use the bulk loader. In the Output Data tool Configuration window, select the dropdown and select Data sources > Microsoft SQL Server > Bulk.
This option requires a DSN connection with ODBC. Visit ODBC and OLEDB Database Connections.