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
Connect to a host that has been permitted access to the database server (usually with SSH). The most common examples are cronus.uchicago.edu, acropolis.uchicago.edu, athens.uchicago.edu, or Midway. All of these hosts require you to be on the campus network or cVPN.
ssh <cnetid>@<host>
While still logged into the above host, run the following command to pull your certificate from the database server into your home directory:
ssh <cnetid>@<database-server.uchicago.edu> | grep -A 100 cat | bash -x
You will be prompted for a password. Use your CNetID password.
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 and you will need to try again.
The output to the terminal should include a line beginning with “To connect to the database, run the following command:”. Underneath that, look for the line beginning with “psql”. Make a note of this line; it is the command you will use to connect to the server.
Connecting to the Server
- First, connect to the host that you completed the above steps on.
- 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 pymysqlconnection = 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’}
)