Direct SQL Access
  • 09 May 2024
  • 4 minute read
  • Dark
    Light
  • PDF

Direct SQL Access

  • Dark
    Light
  • PDF

Article Summary

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

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, all queries should and can be written using the standard query tool using the flexibility afforded by configurable joins. 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 for Direct SQL connections are:

  • Connections are automatically disconnected after 60 minutes of inactivity

  • Connections in excess of three concurrent connections may be terminated

  • Connections causing 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 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 3 hours for any changes, including the provisioning of new accounts, to take effect as the accounts need to be synchronized across all servers in a cluster.

Outbound Access

Access is provided via the standard SQL Server port TCP/1433. Some networks may block outbound access to port TCP/1433 for security reasons. You can determine if this is the case by attempting to access the following site from the same network as your database client: http://portquiz.net:1433/ If this page does not load, this likely indicates that your network is blocking access to TCP/1433, and you’ll need to submit a request to your IT team to unblock your access from your source addresses to the Technolutions addresses listed below, as appropriate.

If a connection attempt closes immediately during the pre-login handshake, this likely indicates that the IP address from which you are connecting has not been added to the allow list. If a connection attempt fails after >10 seconds, this likely indicates that your network is blocking outbound access to TCP/1433.

The following IP addresses are associated with these SQL endpoints:

  • United States / Global (sql.technolutions.net): 34.200.21.44, 44.223.214.145

  • Canada (sql.ca-central-1.technolutions.net): 35.182.71.83, 15.222.222.243

  • Europe (sql.eu-west-1.technolutions.net): 34.248.134.148, 34.251.248.142

  • Asia Pacific (sql.ap-northeast-1.technolutions.net): 18.179.70.72, 3.113.193.167

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 the appropriate options in your client to enable an encrypted connection. 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?