Microsoft SQL Server

Microsoft SQL Server Integration

Connect Querri to your Microsoft SQL Server database to run queries, analyze data, and create visualizations.

Overview

The SQL Server connector supports:

• SQL Server 2016+: All modern SQL Server versions
• SQL Server 2019: Full support including new features
• SQL Server 2022: Latest version support
• Azure SQL Database: Managed SQL Server on Azure
• Azure SQL Managed Instance: Enterprise features on Azure
• Amazon RDS for SQL Server: Managed SQL Server on AWS

Setup

Prerequisites

• SQL Server database instance
• Database credentials with appropriate permissions
• Network access to the database

IP Whitelisting

Important: For cloud-hosted databases or databases behind firewalls, whitelist the following IP address:

18.189.33.77

This is Querri’s outbound IP address. Add this to your database firewall rules or security group.

Creating the Connector

1. Navigate to Connectors

   • Go to Settings → Connectors
   • Click “Add Connector”

2. Select Microsoft SQL Server

   • Choose “Microsoft SQL Server” from the database connectors

3. Configure Connection

Host: your-server.database.windows.net
Port: 1433 (default SQL Server port)
Database: your_database_name
Username: your_username
Password: your_password

Optional Settings:

• Authentication: SQL Server Authentication or Windows Authentication
• Encrypt: Enable for encrypted connections (recommended)
• Trust Server Certificate: For self-signed certificates
• Application Name: Identify Querri connections

4. Test Connection

   • Click “Test Connection” to verify
   • If successful, click “Save”

5. Use in Projects

   • Create a new step → “Database Query”
   • Select your SQL Server connector
   • Write your T-SQL query

Network Configuration

Azure SQL Database

Add firewall rule:

Rule Name: Querri
Start IP: 18.189.33.77
End IP: 18.189.33.77

Via Azure Portal:

1. Go to your SQL Database
2. Click “Set server firewall”
3. Add the IP address
4. Click “Save”

AWS RDS SQL Server

Add inbound rule to security group:

Type: MS SQL
Protocol: TCP
Port: 1433
Source: 18.189.33.77/32
Description: Querri Access

On-Premises SQL Server

Configure Windows Firewall:

New-NetFirewallRule -DisplayName "SQL Server" `
  -Direction Inbound `
  -Protocol TCP `
  -LocalPort 1433 `
  -RemoteAddress 18.189.33.77 `
  -Action Allow

Enable TCP/IP in SQL Server Configuration Manager:

1. Open SQL Server Configuration Manager
2. SQL Server Network Configuration → Protocols
3. Enable TCP/IP
4. Restart SQL Server service

Database User Setup

Read-Only User (Recommended)

Create a dedicated read-only user for Querri:

-- Create login
CREATE LOGIN querri_readonly
WITH PASSWORD = 'SecurePassword123!';

-- Switch to your database
USE your_database;

-- Create user
CREATE USER querri_readonly
FOR LOGIN querri_readonly;

-- Grant read permissions
ALTER ROLE db_datareader ADD MEMBER querri_readonly;

-- Allow view of database metadata
GRANT VIEW DEFINITION TO querri_readonly;

Read-Write User (If Needed)

-- Create login
CREATE LOGIN querri_readwrite
WITH PASSWORD = 'SecurePassword123!';

-- Switch to your database
USE your_database;

-- Create user
CREATE USER querri_readwrite
FOR LOGIN querri_readwrite;

-- Grant read-write permissions
ALTER ROLE db_datareader ADD MEMBER querri_readwrite;
ALTER ROLE db_datawriter ADD MEMBER querri_readwrite;

-- Grant execute permissions (for stored procedures)
GRANT EXECUTE TO querri_readwrite;

Verify Permissions

-- Check login
SELECT * FROM sys.server_principals
WHERE name = 'querri_readonly';

-- Check user permissions
SELECT
    dp.name as username,
    dp.type_desc,
    o.name as object_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE dp.name = 'querri_readonly';

Example Queries

Basic SELECT

SELECT
    customer_id,
    customer_name,
    email,
    created_at
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

Aggregate Query with CTE

WITH monthly_sales AS (
    SELECT
        FORMAT(order_date, 'yyyy-MM') as month,
        COUNT(*) as order_count,
        SUM(total_amount) as total_revenue,
        AVG(total_amount) as avg_order_value
    FROM orders
    WHERE order_date >= DATEADD(MONTH, -12, GETDATE())
    GROUP BY FORMAT(order_date, 'yyyy-MM')
)
SELECT *
FROM monthly_sales
ORDER BY month DESC;

JOIN with Window Functions

SELECT
    c.customer_name,
    c.email,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as lifetime_value,
    RANK() OVER (ORDER BY SUM(o.total_amount) DESC) as customer_rank
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id, c.customer_name, c.email
HAVING COUNT(o.order_id) > 0
ORDER BY lifetime_value DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

Dynamic Date Ranges

SELECT
    product_name,
    category,
    SUM(quantity) as units_sold,
    SUM(total_amount) as revenue
FROM sales
WHERE
    sale_date >= CAST('{{start_date}}' AS DATE)
    AND sale_date < CAST('{{end_date}}' AS DATE)
GROUP BY product_name, category
ORDER BY revenue DESC;

Performance Optimization

Use Indexes

Create indexes on frequently queried columns:

CREATE NONCLUSTERED INDEX IX_orders_customer
ON orders(customer_id)
INCLUDE (order_date, total_amount);

CREATE NONCLUSTERED INDEX IX_orders_date
ON orders(order_date)
INCLUDE (customer_id, total_amount);

Check Index Usage

-- View index usage statistics
SELECT
    OBJECT_NAME(s.object_id) as table_name,
    i.name as index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE OBJECT_NAME(s.object_id) = 'orders';

Query Execution Plan

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Your query here
SELECT * FROM orders WHERE customer_id = 123;

-- View execution plan
SET SHOWPLAN_TEXT ON;

Use NOLOCK for Read-Only Queries

SELECT *
FROM orders WITH (NOLOCK)
WHERE order_date >= '2024-01-01';

Note: Use NOLOCK carefully, as it can read uncommitted data.

Common Use Cases

Sales Dashboard

SELECT
    CAST(order_date AS DATE) as date,
    COUNT(*) as order_count,
    SUM(total_amount) as revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE order_date >= DATEADD(DAY, -30, GETDATE())
GROUP BY CAST(order_date AS DATE)
ORDER BY date DESC;

Customer Lifetime Value

WITH customer_metrics AS (
    SELECT
        c.customer_id,
        c.customer_name,
        c.email,
        MIN(o.order_date) as first_order_date,
        MAX(o.order_date) as last_order_date,
        COUNT(o.order_id) as total_orders,
        SUM(o.total_amount) as lifetime_value,
        AVG(o.total_amount) as avg_order_value
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE c.status = 'active'
    GROUP BY c.customer_id, c.customer_name, c.email
)
SELECT
    *,
    DATEDIFF(DAY, first_order_date, last_order_date) as customer_age_days,
    CASE
        WHEN total_orders >= 10 THEN 'VIP'
        WHEN total_orders >= 5 THEN 'Regular'
        WHEN total_orders >= 2 THEN 'Repeat'
        ELSE 'New'
    END as segment
FROM customer_metrics
WHERE total_orders > 0
ORDER BY lifetime_value DESC;

Inventory Analysis

SELECT
    p.product_name,
    p.category,
    i.current_stock,
    i.reorder_level,
    s.supplier_name,
    COALESCE(recent_sales.units_sold_30d, 0) as units_sold_30d,
    CASE
        WHEN i.current_stock = 0 THEN 'OUT OF STOCK'
        WHEN i.current_stock <= i.reorder_level THEN 'REORDER NOW'
        WHEN i.current_stock <= i.reorder_level * 1.5 THEN 'LOW STOCK'
        ELSE 'OK'
    END as status
FROM products p
JOIN inventory i ON p.product_id = i.product_id
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
LEFT JOIN (
    SELECT
        product_id,
        SUM(quantity) as units_sold_30d
    FROM sales
    WHERE sale_date >= DATEADD(DAY, -30, GETDATE())
    GROUP BY product_id
) recent_sales ON p.product_id = recent_sales.product_id
WHERE i.current_stock <= i.reorder_level * 2
ORDER BY i.current_stock ASC;

Troubleshooting

Cannot Connect to Server

Problem: A network-related or instance-specific error

Solutions:

1. Verify IP 18.189.33.77 is whitelisted
2. Check SQL Server is running
3. Verify TCP/IP is enabled
4. Check SQL Server Browser service is running (for named instances)
5. Verify firewall allows port 1433

Login Failed

Problem: Login failed for user

Solutions:

1. Check username and password
2. Verify SQL Server authentication is enabled
3. Check user exists: SELECT * FROM sys.server_principals;
4. Verify database name is correct
5. For Azure SQL, ensure format: username@servername

Cannot Open Database

Problem: Cannot open database requested by login

Solutions:

1. Verify database name
2. Check user has access to database
3. Ensure database is online
4. Grant permissions to database

SSL/TLS Errors

Problem: Certificate validation failed

Solutions:

1. Enable “Trust Server Certificate” option
2. Install proper SSL certificate on server
3. Update connection string: Encrypt=true;TrustServerCertificate=true;

Security Best Practices

Enable Encryption

Encrypt: Yes
Trust Server Certificate: No (use valid certificate)

Use Row-Level Security

-- Create security policy
CREATE FUNCTION dbo.fn_securitypredicate(@customer_id AS INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result
WHERE @customer_id = CAST(SESSION_CONTEXT(N'customer_id') AS INT);

CREATE SECURITY POLICY CustomerFilter
ADD FILTER PREDICATE dbo.fn_securitypredicate(customer_id)
ON dbo.orders
WITH (STATE = ON);

Audit User Activity

-- Enable database auditing
CREATE DATABASE AUDIT SPECIFICATION querri_audit
FOR SERVER AUDIT [Audit-Querri]
ADD (SELECT ON DATABASE::your_database BY querri_readonly)
WITH (STATE = ON);

Use Always Encrypted

For sensitive columns:

CREATE COLUMN MASTER KEY CMK1
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/my/cert_hash'
);

ALTER TABLE customers
ALTER COLUMN ssn VARCHAR(11) ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = CEK1,
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);

Connection Pooling

Querri uses connection pooling:

• Min Pool Size: 1
• Max Pool Size: 10
• Connection Lifetime: 300 seconds
• Connection Timeout: 30 seconds

SQL Server Versions

Tested and supported:

• SQL Server 2022 ✓
• SQL Server 2019 ✓
• SQL Server 2017 ✓
• SQL Server 2016 ✓
• Azure SQL Database ✓
• Azure SQL Managed Instance ✓

Support

SQL Server Resources

• SQL Server Documentation: docs.microsoft.com/sql
• Azure SQL Documentation: docs.microsoft.com/azure/sql-database
• SQL Server Forums: social.technet.microsoft.com/Forums/sqlserver

Querri Support

• Email: support@querri.com
• Documentation: /guides/connectors/

Next Steps

• Data Connectors Guide
• Working with Data
• Creating Projects