How to connect to Microsoft SQL Server

This guide provides specific instructions for preparing a Microsoft SQL Server database for connection to elvex. For general principles and best practices, please refer to the Preparing Your Database for Connection to elvex guide.

Before You Begin

Before connecting your SQL Server database to elvex, you should:

Have administrator access to your SQL Server database or work with someone who does
Understand which tables and data you want to make accessible
Identify which users or groups will need access to this data through elvex
Consider any data security or compliance requirements that apply to your data

Step 1: Create a Dedicated Service Account

For optimal security, create a dedicated service account that elvex will use to connect to your SQL Server database.

Step 2: Set Appropriate Permissions

Restrict the service account to only the permissions it needs:

-- Grant read-only access to specific tables 
GRANT SELECT ON dbo.table_name TO CREATED_ELVEX_USER; 
GRANT SELECT ON dbo.another_table TO CREATED_ELVEX_USER;  

-- Grant read-only access to all tables in a specific schema 
GRANT SELECT ON SCHEMA::dbo TO CREATED_ELVEX_USER;  

-- If you need to grant access to a different schema 
GRANT SELECT ON SCHEMA::schema_name TO CREATED_ELVEX_USER;

Step 3: Create Views for Complex Data Models (Optional)

If your data model is complex or you want to simplify access, consider creating views:

-- Create a view to summarize customer information 
CREATE VIEW dbo.customer_summary AS
SELECT
    c.customer_id,
    c.name,
    c.email,
    SUM(o.total_amount) AS total_spent,
    COUNT(o.order_id) AS order_count
FROM
    customers c
JOIN
    orders o ON c.customer_id = o.customer_id
GROUP BY
    c.customer_id,
    c.name,
    c.email;

-- Grant read-only access to the view 
GRANT SELECT ON dbo.customer_summary TO CREATED_ELVEX_USER;

Step 4: Add Descriptions to Tables and Columns (Recommended)

Adding descriptions to your tables and columns will help elvex's AI better understand your data:

-- Add extended properties to describe the 'customers' table
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Customer information including contact details',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'customers';

-- Add descriptions to specific columns in the 'customers' table
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Unique identifier for each customer',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'customers',
    @level2type = N'COLUMN',
    @level2name = N'customer_id';
EXEC sp_addextendedproperty
    @name = N'MS_Description',
    @value = N'Customer full name',
    @level0type = N'SCHEMA',
    @level0name = N'dbo',
    @level1type = N'TABLE',
    @level1name = N'customers',
    @level2type = N'COLUMN',
    @level2name = N'name';

Step 5: Prepare Connection Information

Gather the following information which you'll need when setting up the connection in elvex:

Host name or IP address (e.g., sqlserver.example.com)
Port (default is 1433)
Database name
Schema (default is dbo, but you can specify any schema you have access to)
Username (service account login name)
Password

Note: For Azure SQL Database, your hostname will be in the format: yourserver.database.windows.net

Step 6: Configure the Connection in elvex

Once you've completed the preparation steps, you're ready to configure the SQL Server connection in elvex:

In elvex, navigate to the Datasources section
Click "Create New Datasource"
Select "Database Connection" as the datasource type
Choose "Microsoft SQL Server" as the database type
Enter the connection details you gathered in Step 5:
- Host: Your SQL Server hostname or IP
- Port: 1433 (or your custom port)
- Database: Your database name
- Schema: The schema to use (defaults to dbo)
- Username: Your service account username
- Password: Your service account password
Select the tables you want to include in your datasource
Review the schema and add any additional descriptions if needed
Configure access permissions for the datasource
Save the datasource

SQL Server-Specific Considerations

Indexed Views

SQL Server supports indexed views, which can improve query performance. elvex can query indexed views just like regular tables:

-- Create a view
CREATE VIEW dbo.sales_summary
WITH SCHEMABINDING AS
SELECT
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(price * quantity) AS total_revenue,
    COUNT_BIG(*) AS count_big
FROM
    dbo.sales
GROUP BY
    product_id;

-- Create a unique clustered index on the view
CREATE UNIQUE CLUSTERED INDEX IX_sales_summary
ON dbo.sales_summary(product_id);

-- Grant read-only access to the indexed view
GRANT SELECT ON dbo.sales_summary TO CREATED_ELVEX_USER;

JSON Data

If your SQL Server database contains JSON data (SQL Server 2016+), elvex can query it using SQL Server's JSON functions. Make sure to add clear descriptions to these columns to help the AI understand the structure of the JSON data.

-- Example JSON query
SELECT
    customer_id,
    JSON_VALUE(customer_data, '$.email') AS email,
    JSON_VALUE(customer_data, '$.phone') AS phone
FROM
    customers
WHERE
    JSON_VALUE(customer_data, '$.active') = 'true';

Connection Encryption

SQL Server supports encrypted connections using TLS/SSL. elvex automatically uses encrypted connections when available. For production environments, ensure your SQL Server is configured with a valid certificate.

Security Best Practices for SQL Server

In addition to the general security best practices, consider these SQL Server-specific recommendations:

Enable encryption: Ensure "Encrypt connection" is enabled (Force Protocol Encryption)
Use strong passwords: SQL Server enforces password complexity policies by default
Limit login attempts: Consider implementing login trigger to prevent brute force attacks
Regular auditing: Enable SQL Server Audit to track database access and queries
Principle of least privilege: Only grant SELECT permissions, never db_owner or higher roles
Network security: Use firewall rules to restrict access to trusted IP addresses only elvex Public IP addresses

Troubleshooting SQL Server Connections

Common Issues

Connection refused errors: Check that your firewall allows connections from elvex's IP addresses
Authentication failures: Verify the service account username and password. Verify the database name is correct
Permission errors: Ensure the service account has SELECT permissions on all required tables and schemas
Schema not found: Verify the schema name is correct and that your service account has USAGE permissions on that schema

Next Steps

After successfully connecting your SQL Server database to elvex, you can:

Create assistants that use this datasource
Test natural language queries against your database
Share the assistants with appropriate users in your organization
Monitor query performance and optimize indexes as needed

Additional Resources

Need Help? If you encounter issues not covered in this guide, please contact elvex support with:

Your SQL Server version
Connection error messages
Results of the permission check queries above