# SQL Server

Connect to Microsoft SQL Server for enterprise analytics

Microsoft SQL Server is a leading enterprise relational database management system, widely used in corporate environments. Scoop connects directly to SQL Server, enabling AI-powered analysis on your enterprise data.

## Connection Details

| Setting | Value |
|---------|-------|
| Default Port | 1433 |
| Connection Type | TCP/IP |
| Authentication | SQL or Windows |

## Prerequisites

Before connecting Scoop to SQL Server:

1. **SQL Server instance** (2016 or later recommended)
2. **SQL Server Authentication** enabled (or Azure AD for Azure SQL)
3. **Read-only user** with SELECT permissions
4. **Network access** from Scoop's IP addresses

## Enable SQL Server Authentication

If using Windows Authentication only, enable mixed mode:

1. Open **SQL Server Management Studio (SSMS)**
2. Right-click your server > **Properties**
3. Go to **Security**
4. Select **SQL Server and Windows Authentication mode**
5. Restart the SQL Server service

## Create a Read-Only User

Connect to your SQL Server and run:

```sql
-- Create a login
CREATE LOGIN scoop_user WITH PASSWORD = 'your_secure_password';

-- Create a user in your database
USE your_database;
CREATE USER scoop_user FOR LOGIN scoop_user;

-- Grant SELECT on all tables in the database
GRANT SELECT TO scoop_user;

-- Or grant SELECT on specific schemas only
GRANT SELECT ON SCHEMA::dbo TO scoop_user;
```

Replace `your_database` and the password with your actual values.

## Whitelist Scoop IP Addresses

### For Azure SQL Database
1. Go to your SQL server in Azure Portal
2. Navigate to **Security** > **Networking**
3. Add a firewall rule for Scoop's IP addresses

### For On-Premises SQL Server
Configure your firewall to allow incoming connections on port 1433 from Scoop's IP addresses.

Also ensure:
- SQL Server is configured for TCP/IP connections
- SQL Server Browser service is running (for named instances)

Contact Scoop support for the current list of IP addresses to whitelist.

## Connect in Scoop

1. Navigate to **Datasets** and click **New Dataset**
2. Select **Database** as your source
3. Choose **SQL Server** from the database type dropdown
4. Enter your connection details:
   - **Server Host**: Your server hostname (e.g., `myserver.database.windows.net` for Azure)
   - **Port**: 1433 (or your custom port)
   - **Database**: Your database name
   - **Username**: scoop_user
   - **Password**: The user's password
5. Click **Test Connection** to verify
6. Enter your SQL query and proceed

## Connection URL Format

If using a JDBC URL directly:

Standard connection:
```
jdbc:sqlserver://hostname:1433;databaseName=your_database
```

With encryption (recommended for Azure):
```
jdbc:sqlserver://hostname:1433;databaseName=your_database;encrypt=true;trustServerCertificate=false
```

For named instances:
```
jdbc:sqlserver://hostname\instance_name;databaseName=your_database
```

## Best Practices

- **Enable encryption** for connections, especially over the internet
- **Use contained database users** for easier migration
- **Create indexes** on frequently filtered columns
- **Use NOLOCK hints** sparingly and understand their implications

## Troubleshooting

### Cannot connect to server
- Verify SQL Server is configured for TCP/IP connections
- Check that port 1433 is open in your firewall
- For named instances, ensure SQL Server Browser is running
- Verify the server name is correct

### Login failed
- Verify SQL Server Authentication is enabled
- Check the username and password are correct
- Ensure the login is not disabled: `SELECT is_disabled FROM sys.sql_logins WHERE name = 'scoop_user'`

### Permission Denied
- Verify the user was created in the correct database
- Check current permissions: `SELECT * FROM fn_my_permissions(NULL, 'DATABASE')`
- Ensure GRANT SELECT was executed