SQL Server

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

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:

-- 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