About SSCS Managed Databases

The database server is a remote server that you access with a client running on your local machine. This can be done with a native client or with an api in most programming languages.

Database servers are only accessible from campus IP address space and are frequently further restricted to hosts that were specified when the database was set up. If you’re unsure of where you can log in to the database from you can contact us for details.

Database servers are secured with SSL encryption and authentication. This ensures any data sent to or from the server is safe in transit and allows for programatic authentication to the server without saving a password. You must get SSL credentials from the server before you will be able to connect to the database. This has to be done the first time you connect and then every time your certificate expires (usually once every 365 days).

Getting the Credentials

  1. Connect to a host that has been permitted access to the database server (usually with SSH).
  2. Run the following command, replacing cnetid with your own and server-db with the hostname of your server:

    ssh cnetid@server-db.uchicago.edu | bash

  3. You will be prompted for a password, this is your CNetID password.
  4. There will be no prompt, but a DUO push notification will be sent to your device. You must approve this to proceed. If you don’t approve the push the next steps will not work but it is safe to try again.
  5. You should see some basic instructions on how to connect to the server displayed on your terminal.

Connecting to the Server

  1. First, connect to the host that you completed the above steps on.
  2. Next, run the command to connect to the database server, this should have been supplied when you got your credentials. Commands for both databases are listed below. Replace server-db, cnetid, and database (usually the same as the server part of server-db)

Postgres

Native Client Example

psql “host=server-db.uchicago.edu sslmode=require sslrootcert=server-db.uchicago.edu.ca sslcert=server-db.uchicago.edu-cert.pem sslkey=server-db.uchicago.edu-key.pem port=5432 user=cnetid dbname=database

Python Example
See more documentation on Psycopg2

import psycopg2

DB = psycopg2.connect(
host=’server-db.uchicago.edu‘,
database=’database‘,
user=’cnetid‘,
sslrootcert=server-db.uchicago.edu.ca,
sslcert=server-db.uchicago.edu-cert.pem,
sslkey=server-db.uchicago.edu-key.pem
)

MySQL

Native Client Example

mysql –ssl-ca=server-db.uchicago.edu.ca –ssl-key=server-db.uchicago.edu-key.pem –ssl-cert=server-db.uchicago.edu-cert.pem -h server-db.uchicago.edu –user=cnetid database

Python Example
See more documentation on PyMysql

import pymysql
connection = pymysql.connect(
    host=’server-db.uchicago.edu’,
    user=’cnetid‘,
    password= None,
    db=’database‘,
    charset=’utf8mb4′,
    cursorclass=pymysql.cursors.DictCursor,
    ssl={
        ‘ca’: ‘server-db.uchicago.edu.ca’,
        ‘key’: ‘server-db.uchicago.edu-key.pem’,
        ‘cert’: ‘server-db.uchicago.edu-cert.pem’}
)