Skip to main content

Connect to Microsoft SQL Server

Connect Microsoft SQL Server to Retool to query data, manage records, and build apps and automations with T-SQL.

Microsoft SQL Server is a relational database management system. You can create a Microsoft SQL Server resource to securely connect to your databases and create apps and automations that use T-SQL queries to perform create, read, update, and delete operations.

What you can do with Microsoft SQL Server in Retool​

  • Query with T-SQL: Write T-SQL queries with joins, aggregations, subqueries, common table expressions (CTEs), and window functions using SQL mode for reading data.
  • Manage data: Insert, update, and delete records using GUI mode for write operations.
  • Execute transactions: Perform multi-statement transactions with automatic rollback on errors.
  • Call stored procedures: Execute SQL Server stored procedures with parameters and return values.
  • Connect to Azure: Query Azure SQL Database, Azure SQL Managed Instance, and other Azure-hosted SQL Server instances.

Before you begin​

To connect Microsoft SQL Server to Retool, you need the following:

  • SQL Server instance: An accessible Microsoft SQL Server instance (SQL Server 2008 or later recommended).
  • Network access: Your SQL Server must accept connections from Retool's IP addresses.
  • Database credentials: A SQL Server login with appropriate permissions for the databases your apps will query.
  • Retool permissions: Own permissions for resources in your organization.

Create a Microsoft SQL Server resource​

Follow these steps to create a Microsoft SQL Server resource in your Retool organization.

1. Create a new resource​

In your Retool organization, navigate to Resources in the main navigation and click Create new → Resource. Search for Microsoft SQL Server and click the Microsoft SQL Server tile to begin configuration.

Best practice

Use folders to organize your resources by team, environment, or data source type. This helps keep your resource list manageable as your organization grows.

2. Configure general settings​

Specify a name and description for the resource that indicates which database it connects to. The description provides more context to users about how to use the resource.

Example nameExample description
SQL Server (readonly)A read-only SQL Server database with customer and order information.
SQL Server ProdProduction Microsoft SQL Server instance. Use for incident tracking and operational data.

Connector version​

Microsoft SQL Server resources use connector version 3.0, which determines the underlying driver behavior.

Connector version 3.0 accepts array values in query parameters and converts them to comma-separated strings before passing them to SQL Server. Query parameters set to an object are not supported and cause the query to fail. Convert an object to a string before using it in a query, for example with JSON.stringify.

3. Configure resource credentials​

Configure the connection settings for your Microsoft SQL Server resource.

Best practice

You can autofill the configuration fields using a Microsoft SQL Server connection string or by importing an existing AWS resource for RDS SQL Server instances.

Connection string example
Server=myserver.database.windows.net,1433;Database=mydb;User Id=myuser;Password=mypassword;

Host​

The hostname or IP address of your SQL Server instance.

Examples
myserver.database.windows.net
sqlserver-instance.c.my-project.internal
sqlserver.example.com
192.168.1.100

Port​

The port number for your SQL Server instance. Default is 1433.

Database name​

The name of the database to connect to. Leave blank to connect to the default database for the login.

Connection options​

Additional connection parameters. Add custom options as key-value pairs.

Examples (username and password authentication)
encrypt=true
trustServerCertificate=false
connectionTimeout=30
requestTimeout=15000
Windows authentication

If you use Windows authentication, connection options are limited to an approved set of ODBC attributes. Retool sets Encrypt, Trusted_Connection, and other security-sensitive settings for you. Configure encryption and certificate verification under SSL/TLS. Do not add encrypt or trustServerCertificate to connection options.

Unsupported keys cause resource setup to fail with an error like Unsupported MSSQL ODBC connection string parameter "<key>". To check whether an existing resource relies on blocked parameters before upgrading, search deployment logs for MSSQL Windows Auth ODBC connection string parameter not on allowlist.

Refer to unsupported ODBC connection params for the allowlist and upgrade guidance.

TDS version​

The Tabular Data Stream (TDS) protocol version used to communicate with SQL Server. Select the version that matches your SQL Server version.

VersionSQL Server version
7.1SQL Server 2000
7.2SQL Server 2005
7.3aSQL Server 2008
7.3bSQL Server 2008 R2
7.4SQL Server 2012 and later (default)

Leave this at the default (7.4) unless you need to connect to an older SQL Server version.

SSH tunnel​

If your SQL Server is not publicly accessible, configure an SSH tunnel to connect through a bastion host. Provide SSH host, port, username, and authentication method (password or private key).

SQL Server proxy host​

If your network routes SQL Server connections through a proxy, specify the proxy server hostname here.

Connect to a read-only replica​

Enable this option to set the ApplicationIntent connection option to ReadOnly. Use this when connecting to Azure SQL Database with the Read Scale-Out feature enabled.

Advanced connection options​

OptionDescription
Use dynamic database namesOverride the database name at query time using a dynamically generated value. Useful for sharded databases where each shard is a separate database.
Use dynamic database credentialsOverride database credentials at query time with dynamically generated values.
Disable converting queries to prepared statementsDisable the default prepared statement behavior to allow dynamically constructed SQL. Use with caution—disabling this removes SQL injection protection.
Show write GUI mode onlyRestrict app users to GUI mode write operations only, preventing them from writing raw SQL queries.

4. Configure authentication​

Microsoft SQL Server supports SQL Server authentication and, for self-hosted instances, Windows authentication. Windows authentication is deprecated.

Authentication methodUse cases
Username and passwordStandard SQL Server authentication. Provide a SQL Server login username and password. Use configuration variables or Retool secrets to store credentials securely.

Select an authentication method and provide the required credentials. For sensitive values like passwords, use configuration variables or Retool secrets rather than hardcoding them.

5. Configure advanced options​

Configure optional settings available under the Advanced options section of the resource configuration form.

Outbound region​

If your organization uses outbound regions, select the region that should be used for requests to SQL Server. This controls which geographic region your requests originate from, which is useful for reducing latency or meeting data residency requirements.

SSL/TLS​

Configuring SSL/TLS is optional but highly recommended for encrypted connections to your SQL Server.

FieldDescription
SSL/TLSEnable SSL/TLS encryption for the connection.
Reject unauthorizedReject connections if the server's SSL certificate cannot be verified. Enabled by default for security.
CA certificateProvide your certificate authority (CA) certificate for SSL verification.
Client certificate and keyFor mutual TLS authentication, provide client certificate and private key.
Azure SQL Database

Azure SQL Database and Azure SQL Managed Instance require encrypted connections. Set encrypt=true in your connection options and ensure SSL/TLS is enabled.

6. Test the connection​

Click Test connection to verify Retool can connect to your SQL Server. If the test succeeds, you see a success message. If it fails, check the following:

  • Network access: Ensure your SQL Server accepts connections from Retool's IP addresses or your self-hosted instance.
  • Credentials: Verify username and password are correct.
  • Firewall rules: Check that firewall rules allow traffic on the SQL Server port (default 1433). For Azure SQL, verify the server-level firewall rules in the Azure portal.
  • SQL Server Browser: If using named instances, ensure SQL Server Browser service is running.
  • TDS version: If connecting to an older SQL Server, try a lower TDS version.
  • SSL configuration: If SSL is required, verify SSL settings match your server's configuration.
  • Windows authentication connection options: If setup fails with Unsupported MSSQL ODBC connection string parameter, remove unsupported keys from Connection options and configure encryption under SSL/TLS. Search deployment logs for MSSQL Windows Auth ODBC connection string parameter not on allowlist to find parameters that were previously logged but are now rejected. Refer to unsupported ODBC connection params for the allowlist.
Use Debug Tools for detailed connection information

After testing the connection, click View in console to open the Debug Tools console. The console displays detailed information about the test, including the test query executed and its response, execution time, and error details if the test fails. This information is helpful for troubleshooting connection issues.

7. Save the resource​

Click Create resource to save your Microsoft SQL Server resource. You can now use it in queries across your Retool apps and automations.

Query Microsoft SQL Server data​

Once you've created a Microsoft SQL Server resource, you can query it in your Retool apps and automations.

Create a query​

To create a Microsoft SQL Server query in a classic app:

  1. In the Retool app IDE, open the Code tab, then click + in the page or global scope.
  2. Select Resource query.
  3. Choose your Microsoft SQL Server resource.
  4. Write your T-SQL query to read data, or use GUI mode for write operations (insert, update, delete).

Action types​

Microsoft SQL Server queries support two modes: SQL mode for reading data with custom T-SQL queries and GUI mode for write operations. In GUI mode, you can select from multiple action types for different write operations.

Action typeDescriptionUse case
Insert a recordAdd a single new row to a table.Create records from form submissions.
Update an existing recordModify an existing row identified by primary key or filter.Edit individual records with inline editing.
Update a record, or create a new record if it doesn't existUpdate an existing record or create a new one if the record doesn't exist.Upsert operations based on filter criteria.
Delete a recordRemove rows based on filter criteria.Delete records with confirmation.
Bulk insert recordsAdd multiple new rows to a table in a single operation.Bulk create records from imported data.
Bulk update via a primary keyUpdate multiple records identified by primary key.Batch update operations.
Bulk upsert via a primary keyInsert or update multiple rows based on a primary key.Import data that may contain new or existing records.

Query configuration fields​

Configuration fields vary based on the mode and action type you select. Field values can be static (e.g., customer_123) or dynamic by using an embedded expression (e.g., {{ userSelect1.value }}).

SQL mode​

For reading data with custom T-SQL queries.

SQL query

The T-SQL statement to execute.

Basic SELECT query
SELECT TOP 100 id, name, email, status, created_at
FROM dbo.users
WHERE status = 'active'
ORDER BY created_at DESC

Use embedded expressions to make queries dynamic:

Query with embedded expressions
SELECT id, name, email, role
FROM dbo.users
WHERE email LIKE {{ '%' + searchInput.value + '%' }}
  AND role = {{ roleSelect.value }}
ORDER BY created_at DESC
OFFSET {{ table1.pagination.offset }} ROWS
FETCH NEXT {{ table1.pagination.pageSize }} ROWS ONLY

GUI mode​

For write operations using the graphical interface.

Action type

Select from the action types listed in the Action types table above.

Table name (all action types)

The database table to query.

Examples
// Static table name
users

// Dynamic table name
{{ dropdown1.value }}
Records

Applies to: Insert multiple records, Bulk upsert by key.

The data to insert or upsert.

Insert from array
{{ table1.data }}
Transform file upload
{{
  fileInput1.parsedValue.map((row) => ({
    name: row.Name,
    email: row.Email,
    status: "active",
  }))
}}
Filter by

Applies to: Update an existing record, Update/create if not exists, Delete a record.

The WHERE clause conditions for filtering rows.

Example
{{ { status: "active", role: roleSelect.value } }}
Primary key column

Applies to: Update a record, Delete a record, Bulk upsert by key.

The column used as the primary key for identifying records.

Example
// Primary key column name
id

// Composite key (for bulk upsert)
["user_id", "product_id"]
Changeset

Applies to: Update a record, Update multiple records.

The columns to update with new values.

Changeset from table inline editing
{{ table1.changesetObject }}
Changeset from form
{{ form1.data }}

Common use cases​

The following examples demonstrate typical Microsoft SQL Server operations in Retool apps.

read and display data in a table

Create a query named tableQuery and write a T-SQL statement to retrieve data.

Example query
SELECT TOP 100 id, name, email, status, created_at
FROM dbo.users
WHERE status = 'active'
ORDER BY created_at DESC

Add a Table component to the app and set its Data property to {{ tableQuery.data }}. The table dynamically updates to reflect the data structure provided by the query.

filter data with user input

Add input components for users to specify search filters:

  • A Text Input component (searchInput) for search terms.
  • A Select component (statusSelect) for status filtering.

Write a query that references the input component values to filter data.

Example query
SELECT id, name, email, status, created_at
FROM dbo.users
WHERE name LIKE {{ '%' + searchInput.value + '%' }}
  AND status = {{ statusSelect.value }}
ORDER BY created_at DESC
insert new rows from a form

Add a Form component (form1) with input fields for the data you want to collect (name, email, role).

Create a query in GUI mode to insert a new row using the form data:

FieldValue
Action typeInsert a record
Table nameusers
Records{{ { name: form1.data.name, email: form1.data.email, role: form1.data.role, status: 'active' } }}

Add an event handler to the form's Submit event that runs the insert query and displays a success notification.

update data with inline editing

Add a Table component and enable inline editing by setting Enable saving to true.

Create an update query in GUI mode that saves changes made in the table:

FieldValue
Action typeUpdate a record
Table nameusers
Primary key columnid
Changeset{{ table1.changesetObject }}

Add an event handler to the table's Save changes event that runs the update query and refreshes the data query.

delete rows with confirmation

Add a Table component that displays your data and a Button component in an action column for deleting rows.

Create a delete query in GUI mode:

FieldValue
Action typeDelete a record
Table nameusers
Primary key columnid

Add an event handler to the button's Click event:

  1. Set the action to Show confirmation modal.
  2. If confirmed, trigger the delete query.
  3. Then refresh the data query.
call a stored procedure

Call a SQL Server stored procedure using SQL mode.

Execute stored procedure
EXEC dbo.usp_GetUserOrders
  @UserId = {{ table1.selectedRow.data.id }},
  @StatusFilter = {{ statusSelect.value }}

For stored procedures that return multiple result sets, the query returns the first result set by default.

Best practices​

Follow these best practices to optimize performance, maintain data integrity, and secure your Microsoft SQL Server queries.

Performance​

  • Use TOP or pagination: Always use TOP or OFFSET...FETCH to limit result sets, especially for large tables displayed in tables or charts.
  • Use indexes: Add indexes on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY statements.
  • Use prepared statements: Retool automatically uses prepared statements, which improves performance and prevents SQL injection. Only disable this if you need to generate SQL dynamically.
  • Avoid SELECT *: Select only the columns you need to reduce data transfer and improve performance.
  • Use connection pooling: Retool automatically manages connection pooling for SQL Server resources.

Security​

  • Use configuration variables: Store database credentials in configuration variables or Retool secrets rather than hardcoding them in resources or queries.
  • Use resource environments: Organizations on an Enterprise plan can configure multiple resource environments to maintain separate database configurations for production, staging, and development.
  • Apply least privilege: Grant SQL Server logins only the minimum permissions needed. Use read-only logins for queries that only need to read data.
  • Validate user input: Always validate and sanitize user input before using it in queries.
  • Enable SSL/TLS: Use SSL/TLS encryption for connections to protect data in transit, especially for Azure SQL or internet-facing instances.
  • Keep connector version current: Use the latest connector version (3.0) to ensure you have the most recent security and bug fixes.

Data integrity​

  • Use transactions: Wrap related operations in transactions to ensure atomicity. SQL Server automatically rolls back the entire transaction if any statement fails.
  • Validate before delete: Always show confirmation dialogs before executing DELETE operations to prevent accidental data loss.
  • Handle errors gracefully: Configure error notifications and fallback behavior for failed queries to improve user experience.
  • Test with environments: Use separate development and staging databases to test queries before deploying to production. Configure different credentials for each environment using Retool environments.