Microsoft SQL Server

Connect Microsoft SQL Server or Azure SQL Database to Formula Bot. Covers read-only user creation, network setup, and authentication options.

Connect your Microsoft SQL Server database to analyze data using natural language and create visualizations.

Prerequisites

  • SQL Server 2016 or higher
  • Database credentials
  • Network access from Formula Bot servers
  • A user with SELECT permissions

Connection Details

FieldDescriptionExample
HostServer hostname or IPsql.example.com
PortSQL Server port (default 1433)1433
DatabaseDatabase nameanalytics
UsernameSQL Server loginreadonly_user
PasswordUser password********

Setup Steps

1. Create a Read-Only User

-- Create a login at server level
CREATE LOGIN formulabot WITH PASSWORD = 'your-secure-password';

-- Switch to your database
USE your_database;

-- Create a user mapped to the login
CREATE USER formulabot FOR LOGIN formulabot;

-- Grant read access
ALTER ROLE db_datareader ADD MEMBER formulabot;

2. Configure Network Access

For on-premises SQL Server:

  • Enable TCP/IP in SQL Server Configuration Manager
  • Configure Windows Firewall to allow port 1433
  • Add Formula Bot IPs to allowed connections

For Azure SQL:

  • Add Formula Bot IPs to server firewall rules
  • Enable "Allow Azure services" if applicable

3. Connect in Formula Bot

  1. Go to Connectors > Add Connector
  2. Select Microsoft SQL Server
  3. Enter your connection details
  4. Click Test Connection
  5. Save when successful

Azure SQL Database

For Azure SQL Database connections:

  1. Find connection details in Azure Portal
  2. Add Formula Bot IPs to firewall rules:
    • Go to SQL Server > Networking
    • Add client IP or Formula Bot IPs
  3. Use the full server name (e.g., yourserver.database.windows.net)

Azure SQL uses port 1433 by default. Connection strings from Azure Portal include all required details.

Authentication Options

SQL Server Authentication

Standard username/password authentication. Works with both on-premises and Azure.

Azure Active Directory

For Azure SQL, you can use AAD authentication:

  1. Configure AAD admin in Azure Portal
  2. Contact Formula Bot support for AAD integration details

Example Questions

  • "What are total sales by region this quarter?"
  • "Show the top 50 customers by order count"
  • "Calculate average transaction value by day"
  • "List all products with inventory below 100 units"

Working with Schemas

SQL Server databases often use multiple schemas. Reference them in your questions:

  • "Show data from dbo.Orders"
  • "Query the sales.Transactions table"

Grant schema access:

GRANT SELECT ON SCHEMA::sales TO formulabot;

Troubleshooting

Connection Refused

  • Verify TCP/IP is enabled in SQL Server Configuration Manager
  • Check port 1433 is open in firewall
  • Ensure SQL Server Browser service is running if using named instances

Login Failed

  • Verify username and password
  • Confirm SQL Server authentication is enabled (mixed mode)
  • Check the user exists in the target database

Timeout Errors

  • Check network connectivity
  • Verify firewall rules
  • For large queries, ask for aggregated data

Named Instance

For named instances (e.g., server\instance):

  • Use port number instead of instance name
  • Find port in SQL Server Configuration Manager

Best Practices

Use Read-Only Access

Only grant db_datareader role to prevent modifications.

Enable Encryption

Configure SSL/TLS encryption in connection settings for secure data transfer.

Limit Table Access

Use Data Access Controls to restrict which tables Formula Bot can query.

Next Steps