Direct SQL Access

Slate supports direct, native SQL Server connections that can be used by any client that supports connections to a SQL Server. The connections provide a read-only interface to your Slate database. These credentials can be directly provisioned in Slate and are restricted to a specific set of external IP addresses or subnets. Some examples of SQL Server clients are listed below.

Platforms for data analysis and reporting:  Platforms for data warehousing: 
  • Tableau 
  • Power BI 
  • SQL Server Management Studio (SSMS) 
  • Crystal Reports 
  • Toad 

  • Snowflake 
  • HelioCampus 

Terms of Service

The direct SQL interfaces are provided on an "as is" basis and should be used for ad-hoc reporting only. Because SQL queries built and run outside of Slate cannot be tracked or managed, even minor changes to database schemas can break some of those scripts. For this reason, with Configurable Joins, all queries should and can be written using the standard query tool. This makes the query far more supportable by non-SQL users and has the added benefit of using standardized exports and filters. These standard exports and filters are routinely verified against schema changes and are much less fragile.

Direct SQL access for an organization may be discontinued at any time at the sole discretion of Technolutions if it impacts database or server performance. For example, disruptive activities include copying all rows in a table or querying indiscriminately or too frequently. Direct SQL access is provided for ad-hoc reporting, management, data visualization, data warehousing, and data lake purposes.

Direct SQL access is not appropriate for automated, scheduled, or batch jobs, as these should be maintained within Slate as data exports or web services. Connections are automatically disconnected after 60 minutes of inactivity, and connections in excess of 3 concurrent connections may be terminated.

The performance of queries executed via direct SQL access will be entirely dependent upon the SQL being executed. The principal rules we have for Direct SQL connections are: 

  • They are automatically disconnected after 60 minutes of inactivity 
  • If you have more than three concurrent connections, additional connections may be terminated 
  • Anything that causes excessive load on the database may be terminated 

The security, integrity, reliability, and performance of Slate are our top priorities, which is why there are limits in place such as automatically terminating direct SQL connections. Technolutions does not provide service guarantees that SQL connections will always be available and/or that we will be able to troubleshoot every failed connection. For processes that are crucial, we encourage your team to take the best practice route of scheduled query exports of flat files via SFTP, or web service. 

Data Warehouse Integrations

Building custom SQL queries or using a direct SQL connection are not necessary to send Slate data to a data warehouse. Using the Query tool within Slate provides a layer of abstraction for the underlying database. The standard functionality has built-in safeguards, is tested and optimized to perform even under heavy load, and incorporates new updates seamlessly. 

Here are some Knowledge Base articles that can be helpful for background information on exporting data and integrating a data warehouse with Slate: 

Request SQL Access (User Permissions)

  1. Select database on the top navigation bar and choose User Permissions.
  2. Choose the user who will be granted access.
  3. Select Edit User.
  4. Select the Roles tab.
  5. Under the Exclusive Permissions section, select Direct SQL Access. A new section, named Direct SQL Access, appears.
  6. Select Reset Password to set the password for the first time, and record this password for future use. The password will not be displayed again after selecting Update. Record the cluster, database, and username, and use the table below to identify the appropriate port. 
  7. Enter any IP addresses that require this connectivity. You may also use CIDR notation to authorize larger IP subnets.

Important

For security purposes, limit the Allowed Networks to individual IP addresses and small subnets where possible.

 Tip

Wait up to 60 minutes for any changes, including the provisioning of new accounts, to take effect since the accounts and firewall rules are refreshed once per hour.

Host Name

The hostname follows the syntax of "sql.technolutions.net,1440," where the ",1440" indicates the custom port number. This enables routing to the appropriate database cluster and is necessary when using a non-standard port (the standard port is 1433). Although many protocols follow the standard ":port" syntax, SQL Server requires the ", port" syntax instead.

  • ACIS: sql.ca-central-1.technolutions.net,1440
  • ARES: sql.technolutions.net,1444
  • EROS: sql.technolutions.net,1445
  • LARA: sql.technolutions.net,1447
  • LIMA: sql.technolutions.net,1448
  • LUNA: sql.technolutions.net,1442
  • MARS: sql.technolutions.net,1443
  • MENA: sql.eu-west-1.technolutions.net,1440
  • NONA: sql.technolutions.net,1440
  • OPUS: sql.technolutions.net,1446
  • ROMA: sql.technolutions.net,1441

If restricting the outbound traffic from your institution to Internet hosts, allow-list the following IP addresses based upon the assigned hostname:

  • sql.technolutions.net:
    • 34.200.21.44
    • 35.164.56.34
  • sql.ca-central-1.technolutions.net
    • 35.182.71.83
  • sql.eu-west-1.technolutions.net
    • 34.248.134.148

SQL Server Management Studio

If you are using SQL Server Management Studio, connect using the following settings, updating the values as needed for your particular database cluster, login name, and password. Other clients will provide similar options.

For added security, select Options and then choose Encrypt connection, as shown below. If your client does not provide a user interface to enable encryption, you can pass a parameter of "Encrypt=yes" in the connection string to instruct the ODBC client to use an encrypted connection.

If you experience issues with the "Encrypt connection" option and you have checked the "Trust server certificate" option in the user interface, you may also need to add "TrustServerCertificate=True" to the connection parameters. 

If you cannot add the connection parameters as described, you may need to forego the use of an encrypted connection.

Was this article helpful?
12 out of 17 found this helpful