Connect to Microsoft SQL Server
You can connect to your Microsoft SQL Server database and make it available as a resource in Retool. Once complete, your users can write queries that interact with Microsoft SQL Server data.
Requirements
All users for Retool organizations on Free or Team plans have global Edit permissions and can add, edit, and remove resources. If your organization manages user permissions for resources, you must be a member of a group with Edit all permissions.
To create a Microsoft SQL Server resource, you need:
- Authentication credentials for the Microsoft SQL Server database.
- Connection details for the Microsoft SQL Server host. This includes the hostname, port, and database name.
- Firewall rules that allow Retool to access your Microsoft SQL Server database host.
Microsoft SQL Server authentication
Retool authenticates with Microsoft SQL Server databases using a username and password.
SSL connections
Retool can connect to Microsoft SQL Server databases using SSL. If required, you can provide CA certificate, client key, and client certificate credentials that allow Retool to connect using SSL.
Allow Retool to access the Microsoft SQL Server database
If the Microsoft SQL Server host is behind a firewall, you must allow access from Retool's IP addresses. Add the IP addresses to your firewall's Allowlist before you create the resource.
1. Create a new resource
Sign in to your Retool organization and navigate to the Resources tab. Click Create new, then select Resource.
2. Select the resource type
Retool can connect to almost any API or database, and has built-in integrations for popular data sources. Select the Microsoft SQL Server resource type.
3. Configure the resource
Retool can automatically populate values from a Microsoft SQL Server connection string.
Specify a name and location for the Microsoft SQL Server resource. Retool displays the resource name and type in query editors to help users identify them. Next, configure the required settings.
General
Provide the following connection settings for your Microsoft SQL Server host and database.
| Setting | Description |
|---|---|
| Host | The URL or IP address. |
| Port | The port number. |
| Database name | The name of the database. |
| Database username | The name of the database user. |
| Database password | The password of the database user. |
| Connection options | Additional options for the Microsoft SQL Server connection. |
Advanced options
Retool organization administrators can configure the following settings for Microsoft SQL resources.
| Setting | Description |
|---|---|
| Use dynamic database names | Allow the database name to be overridden by a dynamically generated value. This allows using Retool with a database that has been sharded into several different databases. |
| Use dynamic database host | Enable this to allow the Database Host to be overridden by a dynamically generated value. This allows using Retool with several different databases. |
| Disable converting queries to prepared statements | This disables SQL injection protection to allow dynamically generated SQL using JavaScript. |
| Show write GUI mode only | This restricts queries to GUI mode only and prevents users from writing raw SQL queries. |
| SQL Server Proxy host | The proxy server host to use when connecting to your database. |
| TDS version | The TDS version to use. |
| Enable SSH tunnel | Enable SSH tunneling through bastion host. |
| Connect to a read-only replica | Azure SQL and SQL managed instances only. Connect to a read-only replica of the database for improved performance. This sets the ApplicationIntent option in the connection string to readOnly. |
Click Test Connection to verify that Retool can connect to Microsoft SQL Server. If the test fails, check the resource settings and try again. Testing a connection only checks whether Retool can successfully connect to the resource. It cannot check whether the provided credentials have sufficient privileges or can perform every supported action.
4. Save the resource
Click Create resource to complete the setup. You can then click either Create app to immediately start building a Retool app or Back to resources to return to the list of resources.
Wrap up
Your Microsoft SQL Server resource is now ready to use. To interact with Microsoft SQL Server data, select the resource in the query editor. You can read data using SQL queries, write data using GUI queries (if enabled), and use the schema browser to search tables or columns.