Skip to content
M↓ View as Markdown ↗

Microsoft SQL Server

Connect a Microsoft SQL Server (MS SQL) database to Actian AI Analyst so you can build Models on its tables and let your AI Analysts query them.

Availability

  • Who can do this: members with the Edit capability in the active Workspace. If your organization has turned on Restrict connection management, you also need the Manage organization capability.
  • Plan: Studio lists only the connectors that your organization's plan and your deployment include. If Microsoft SQL Server is missing from the list, ask your administrator.
  • Deployment: Actian SaaS and self-managed deployments.

To connect SQL Server, you:

  1. Create a login and user with read-only permissions in SQL Server.
  2. Allow AI Analyst's IP addresses through your firewall or network rules.
  3. Add the connection in Studio.

Read-only access depends on the login

SQL Server has no read-only session that AI Analyst can switch on, so AI Analyst relies on the permissions of the login you supply. The connection form shows: This Connector cannot enforce read-only access itself. Supply a warehouse user that is limited to read-only privileges. Use a login that is only a member of db_datareader (as below) and has no write or DDL rights. See Read-only access.

1. Create a login and user in SQL Server

This script creates a server-level login and a read-only database user for AI Analyst:

-- Replace the placeholder values with your database name and a secure password

-- Create a server-level login
CREATE LOGIN analyst_login WITH PASSWORD = '<your_secure_password>';

-- Switch to your target database
USE YourDatabaseName;

-- Create a database-level user linked to the login
CREATE USER analyst_user FOR LOGIN analyst_login;

-- Grant read-only access to all tables
ALTER ROLE db_datareader ADD MEMBER analyst_user;

The db_datareader role gives read access to all current and future tables in the database. Don't add the user to any role that can write or change objects, such as db_datawriter, db_ddladmin or db_owner.

2. Allow AI Analyst to reach your server

If your SQL Server is behind a firewall or network rules, allow inbound connections on its port (1433 by default) from AI Analyst's outbound IP addresses:

  • Actian SaaS: when your deployment publishes its outbound addresses, the Setup help panel next to the connection form lists them under Which IPs to allow. If the panel doesn't list any addresses, contact Actian support for them.
  • Self-managed deployment: allow the outbound addresses of your own AI Analyst installation. If the database has a private network address, your operator must also allow that private range for warehouse connections.

See Network access for details.

3. Add the connection in Studio

  1. In Studio, open Connections, click + (Add connection) and choose Microsoft SQL Server under Warehouses.
  2. Under Connection details, enter a Name. Names must be unique among the connections you can see. See Connections and Workspaces. Optionally add a Description.
  3. Optionally, tick Private to this Workspace so that only the active Workspace can see and use the connection. See Who can see the connection.

  4. Under Credentials, fill in the fields:

    Field Value
    Host Your server's hostname, without a protocol (no https://)
    Port 1433 by default
    Database The database where analyst_user has access
    Username analyst_login (the login, not the database user)
    Password The password you set in step 1
    SSL mode Require (no verify) by default. See SSL mode.

    Instead of typing each field, you can click Paste a connection string, paste an ADO.NET, JDBC or URI string such as Server=tcp:host,1433;Database=database;User ID=user;Password=password;Encrypt=True, and click Fill the form. Nothing is sent until you test the connection.

  5. Click Test connection. When the test succeeds, Schemas appears and lists the schemas the user can see. dbo is selected if it exists.

  6. The preselected schema (dbo) is exposed by default. To change it, select the schemas to expose for browsing and model authoring. Changing the selection resets the test, so click Test connection again afterwards. Your selection is kept. If nothing is preselected because dbo doesn't exist, select at least one schema before you save.
  7. Click Save connection. The button stays disabled until a test succeeds and the connection has a name.

Studio opens the new connection. Credentials are encrypted at rest and are never shown again after saving. To browse tables, re-test the connection or update credentials later, see Manage connections.

SSL mode

The SSL mode options behave as follows for SQL Server:

SSL mode Encrypts the connection Verifies the server certificate
Disable No No
Require Yes Yes
Require (no verify) (default) Yes No. The server certificate is trusted as presented.
Verify CA Yes No. It behaves like Require (no verify).
Verify full Yes Yes

Choose Verify full (or Require) to validate the server certificate. Keep Require (no verify) if your server uses a self-signed certificate. Uploading a CA certificate is not supported.

Who can see the connection

A new connection is attached to the Workspace you are working in.

  • Organization connection (default): an organization admin can attach it to other Workspaces later.
  • Private to this Workspace: only this Workspace can see or use it. An organization admin can later share it with the whole organization. A shared connection can't be made private again.

See Connections and Workspaces.

Local and private addresses

If you enter localhost, a private IP address or a hostname without a domain, the form shows This looks like a local address. The form doesn't block you, but you can only save after a successful test, and the test only succeeds if AI Analyst's servers can reach the address and your deployment allows it. AI Analyst refuses private addresses unless the deployment allows that range. On a self-managed deployment, ask your operator to allow it. See Network access. localhost points to the server AI Analyst connects from, not to your computer.

Next steps

Before people can ask questions about this data:

  1. Create Models on the tables you want to analyse.
  2. Add the Models to an AI Analyst.

You can also monitor the connection's health.

Troubleshooting

Message after Test connection or Save connection Likely cause and fix
Fill in … before testing the connection A required field is empty. Fill in the fields it names.
Authentication failed (if shown) Check that Username is the login name (analyst_login) and that the Password is correct.
Could not connect Studio shows this when it can't classify the failure. Click the message to expand the driver's detail. A "Login failed for user" message means the login or password is wrong, or the user has no access to the Database.
Could not reach the warehouse Check the Host and Port, and that your firewall allows AI Analyst's IP addresses (step 2).
Could not establish a secure connection The SSL settings don't match the server. See SSL mode.
This warehouse user cannot see those objects (if shown) Check that the user exists in the Database you entered and is a member of db_datareader.
The test succeeds but Schemas doesn't appear The user can't see any schema in this database. Check the grants in step 1. The request to list schemas can also fail after the test (a brief error appears). Click Test connection again.
Warehouse host is not allowed The host is a private or blocked address, doesn't resolve, or includes a protocol, path or port. Use a publicly reachable host name, or on a self-managed deployment ask your operator to allow the private range. See Network access.
A connection with this name already exists Choose a different Name. This message appears after you click Save connection.