Connecting to Databases

Warning: you must configure your database client before attempting to connect. Please find your preferred client from the list below (ex: Postico) and follow the instructions.

The Connection Process

  1. Connect to the datasource in your strongDM client by clicking on its name in the GUI or running sdm connect <name> at the command line.

  2. Ensure that there is a green lightning bolt next to the datasource name in the GUI or the datasource is listed as connected if you run sdm status at the CLI.

  3. Find your preferred database client from the tables below and use the specified parameters to configure the connection. Note that every client is different and if you do not use the correct parameters, the connection will fail! If your client is not listed below, please contact support@strongdm.com with details.

  4. Initiate the connection within your database client.

GUI Clients

The connection matrix below contains required connection parameters for many popular database GUI clients. Please note that if you are using port overrides or custom ports you may need to change the default database port to the specific port specified in your local GUI, in addition to the hostname field.

NOTE: If you are connecting to SQL Server, PostgreSQL, or a PostgreSQL derivative (e.g. Greenplum), please see the Database Overrides section below as you may need to specify a specific database to connect to.

Database SQL Client Hostname Username Password Initial DB* Additional Settings
MySQL, Aurora MySQL, Clustrix, MariaDB, MemSQL           Note: In some versions of MySQL, you may need to add useSSL=false or the equivalent to your connection parameters
  DataGrip localhost empty empty empty  
  DBeaver localhost empty empty existing database name  
  DbVisualizer localhost empty empty existing database name  
  HeidiSQL localhost empty empty empty  
  MySQL Workbench 127.0.0.1 any string empty empty  
  Navicat localhost empty empty empty  
  Sequel Pro 127.0.0.1 empty empty empty  
  SQLPro Studio localhost any string empty empty  
  SQLyog localhost any string empty empty  
  Tableau 127.0.0.1 empty empty empty  
  TeamSQL localhost any string empty empty  
  SQL Workbench/J localhost empty empty empty  
PostgreSQL, Aurora PostgreSQL, Greenplum DBeaver localhost empty empty empty  
  DataGrip localhost empty empty empty  
  DbVisualizer localhost empty empty empty  
  HeidiSQL localhost empty empty empty  
  Navicat localhost empty empty empty  
  pgAdmin localhost any string empty any string  
  Postico localhost empty empty empty  
  SQLPro Studio localhost any string empty empty  
  Tableau localhost any string empty any string  
  TeamSQL localhost any string empty any string  
  SQL Workbench/J localhost empty empty empty  
Microsoft SQL Server Access 127.0.0.1 any string empty empty  
  Alteryx Designer 127.0.0.1 any string any string empty Use system ODBC connection and SQL Server 10.x driver
  DataGrip localhost empty empty empty  
  DBeaver localhost empty empty empty  
  DBVisualizer localhost empty empty empty  
  Excel 127.0.0.1 any string empty empty  
  Navicat localhost any string empty empty  
  SQL Server Management Studio 127.0.0.1 any string empty empty  
  Tableau 127.0.0.1 any string empty empty  
  SQL Workbench/J localhost empty empty empty  
MongoDB dbKoda localhost empty empty empty  
  MongoDB Compass localhost empty empty empty  
  NoSQLBooster localhost empty empty empty  
  Robo 3T (Robomongo) localhost empty empty empty  
Redshift (using Postgres driver**) DBeaver localhost empty empty empty  
  DataGrip localhost empty empty empty  
  DbVisualizer localhost empty empty any string  
  HeidiSQL localhost empty empty empty  
  Navicat localhost empty empty empty  
  Postico localhost empty empty empty  
  SQLPro Studio localhost any string empty empty  
  Tableau localhost any string any string any string  
  SQL Workbench/J localhost empty empty empty  
Snowflake DBeaver localhost empty empty empty any string as account property and false as SSL property under Driver Properties
Sybase DBArtisan localhost any string any string empty  
  DataGrip localhost any string any string empty  
  DBVisualizer localhost any string any string empty  
  ERStudio localhost any string any string empty  
  RapidSQL 127.0.0.1 any string any string empty  
  SQL Workbench/J localhost any string any string empty  

*See Database Overrides below for more information on the Database field

**See JDBC Drivers, just below, for using the Redshift JDBC driver

JDBC Drivers

Some JDBC drivers have very specific connection string requirements. When using the below JDBC drivers, we recommend entering the connection string directly. Replace <port> with the configured port. Where it says any you can replace with any string, but a string must be there for proper functionality.

Driver Connection String
Athena jdbc:awsathena://UseResultsetStreaming=0;ProxyHost=localhost;ProxyPort=<port>;Protocol=http;AwsRegion=any;UID=any;PWD=any;S3OutputLocation=s3://any/
jTDS jdbc:jtds:sqlserver://localhost:<port>/any;user=any;password=any;ssl=request
Redshift jdbc:redshift://localhost:<port>/any?ssl=false&UID=any&PWD=any
Snowflake jdbc:snowflake://localhost:<port>/?account=any&SSL=FALSE

Command-line Clients

Database type Sample connection string without port Sample connection string with port Sample connection string with database*
Cassandra cqlsh localhost cqlsh localhost <port>  
DynamoDB dynamodb dynamodb --endpoint-url http://localhost:<port>  
Microsoft SQL Server sqlcmd -s 127.0.0.1 sqlcmd -s 127.0.0.1,<port> sqlcmd -s 127.0.0.1 -d <database>
  mssql-cli -S 127.0.0.1 -U sdm -P sdm mssql-cli -S 127.0.0.1,<port> -U sdm -P sdm mssql-cli -S 127.0.0.1 -U sdm -P sdm -d <database>
MongoDB mongo --host localhost mongo --host localhost --port <port>  
MySQL, Aurora MySQL, MariaDB, MemSQL mysql -h 127.0.0.1 mysql -h 127.0.0.1 -P <port>  
PostgreSQL, Aurora PostgreSQL, Greenplum, Redshift psql -h localhost psql -h localhost -p <port> psql -h localhost -d <database>
Redis redis-cli -h localhost redis-cli -h localhost -p <port>  
Snowflake     SNOWSQL_PWD=none snowsql -u sdm -a sdm -d <database> -s <schema> -w <warehouse> -h localhost -p <port> -D protocol=http

*See the following section for why you may need to specify a database with these database types

REST API Connections

Some database types allow REST API access—Druid in fact requires it. To connect to these databases, replace the host and port in the URL with localhost and the configured port in strongDM.

Example for Druid: http://localhost:18090/druid/indexer/v1/task

Database Overrides

The SQL Server and PostgreSQL datasource types, as well as PostgreSQL derivatives like Greenplum and Redshift, have the option of database overrides. However, this option works differently between SQL Server and PostgreSQL.

Database override enabled

PostgreSQL

SQL Server

Database override disabled (both PostgreSQL and SQL Server)