How do I connect to my study's SQL database?

We currently provision an SQL server for each project to ensure total separation between each TRE project. For each of these, we provide you with the following details:

Hostname - in almost all cases, this will be sql.hic-tre.dundee.ac.uk
Database name(s)
Username - your project ID
Password - usually left blank

Our TRE SQL server is reachable at sql.hic-tre.dundee.ac.uk. The username for this will be your project ID - this can be in the “Project” field under any workspace you have created in the TRE. When connecting, the password should be left blank - we verify access based on the source of the connection, which ensures we are able to constrain database access to the correct projects.

This article explains how you can connect to your SQL database using a variety of methods - whether that be from SQL Management Studio, Azure Data Studio, R, Python or Stata:

1 Windows (SQL Server Management Studio, SSMS)
2 Ubuntu Linux (Azure Data Studio)
3 R and R Studio (odbc and DBI)
4 Stata
5 Python
6 SPSS
7 💁 SAS Viya
8 Related articles

Windows (SQL Server Management Studio, SSMS)

Open Microsoft SQL Management Studio from the Windows application menu. Upon launching, it will prompt for some connection details.

Server type: Database Engine
Server name: sql.hic-tre.dundee.ac.uk
Authentication: SQL Server Authentication (not Windows Authentication)
Login: your project ID as it appears under “Project” for your workspace (e.g. project-XXXX)
Password: leave this emtpy

After pressing Connect, you will be able to see the server address on the left under Object Explorer. Using the database tree, you can navigate to your database and perform queries.

Right clicking on a table name can allow you to quickly prepare SELECT queries for quickly getting an idea of the structure of the database.

Ubuntu Linux (Azure Data Studio)

Azure Data Studio is provided as an alternative to Microsoft SQL Management Studio fro Ubuntu workspaces as SQL Management Studio is not supported on Linux-based platforms. Azure Data Studio can be found in the application menu drop down, top left. When you first open Azure Data Studio, it will take you to a welcome page. From here, click “Create a connection” and enter the appropriate connection details.

Connection type: Microsoft SQL Server
Server: sql.hic-tre.dundee.ac.uk
Authentication type: SQL Login
Username: your project ID as it appears under “Project” for your workspace (e.g. project-XXXX)
Password: leave this empty

The remaining fields can be left blank, followed by clicking Connect. An example of this is shown to the right. Once connected, your databases will be displayed under the Databases tree on the left side of the window.

R and R Studio (odbc and DBI)

You can access your SQL databases from R directly, saving the effort required to convert this into a flat file or other R-compatible format. The command transcript below provides an example on how to do this.

> install.packages('odbc')
> library('odbc')

# Windows uses the driver name "SQL Server"
> conn <- dbConnect(odbc(),
                   Driver="SQL Server",
                   Database="Your database name",
                   Server="sql.hic-tre.dundee.ac.uk",
                   UID="project-XXXX", PWD="")

# Under an Ubuntu environment, the driver name has a slightly longer name
> conn <- dbConnect(odbc(),
                   Driver="ODBC Driver 18 for SQL Server",
                   Database="Your database name",
                   Server="sql.hic-tre.dundee.ac.uk",
                   UID="project-XXXX", PWD="")
                  
> print(conn)
<OdbcConnection> user@sqlserver
  Database: ProjectXXXX
  Microsoft SQL Server Version: 15.00.4188

> print(dbGetQuery(conn, "SELECT name, database_id FROM sys.databases"))
                 name  database_id
1              master            1
2             temp_db            2
3               model            3
4                msdb            4
5         ProjectXXXX            5
6 ProjectXXXX_Working            6

The example above opens a connection to the SQL server, prints out the connection details to confirm that it’s connected, and then performs a simple SELECT query to obtain a list of database names.

From within R Studio, you will also be able to see a list of databases (and their tables) by navigating to the Connections tab on the right-side pane, once an SQL connection has been opened

Stata

If you have access to Stata within your TRE environment, you can also use this to connect to your project’s SQL server. Below is an example snippet of code to help you get started opening an SQL connection and running a query. Remember to set the database name and server with the values provided when your SQL database was set up.

Please note the first two commands are operating system specific. Older Ubuntu workspaces may not work, please contact HICSupport@dundee.ac.uk asking us to patch your Stata installation.

* (Windows) Setup the string which defines our connection 
local connString "Driver={SQL Server};Database=ProjectXXXX;Server=sql.hic-tre.dundee.ac.uk;UID=project-XXXX"

* (Ubuntu Linux) Setup the string which defines our connection
local connString "Driver={ODBC Driver 18 for SQL Server};Database=ProjectXXXX;Server=sql.hic-tre.dundee.ac.uk;UID=project-XXXX"

* Verify that we are able to connect by listing the tables
odbc query, conn("`connString'")

* Perform a simple query - this could be SELECT, UDPATE, INSERT, DELETE, etc.
odbc exec("SELECT TOP 10 * FROM Table"), conn("`connString'")

* Load an entire table into memory / Stata variables
odbc load, exec("SELECT * FROM Table") conn("`connString'")

Python

The pyodbc package needs to be installed on your workspace. Since this hasn’t been deployed to all workspaces yet, you may have to open a support ticket to have it added to your workspace.

import pyodbc
import os

# Replace these with the details provided to you
sqlServer = 'tcp:sql.hic-tre.dundee.ac.uk'
sqlUsername = 'project-XXXX'
sqlPassword = ''
sqlDatabase = 'ProjectXXXX'

# Chose the ODBC driver name based on your operating system
sqlDriver = 'ODBC Driver 18 for SQL Server'
if os.name == 'nt':
    sqlDriver = 'SQL Server'

# Define the connection string and create an ODBC object
connString = 'Driver={%s};Server=%s;UID=%s;PWD=%s;Database=%s' % (
    sqlDriver, sqlServer, sqlUsername, sqlPassword, sqlDatabase)
odbcConn = pyodbc.connect(connString)
odbcCursor = odbcConn.cursor()

# Run a simple query to display the version string
odbcCursor.execute("SELECT @@version;")
row = odbcCursor.fetchone()
while row:
    print(row[0])
    row = odbcCursor.fetchone()

SPSS

SPSS requires the creation of an ODBC data source. The simplest way to do this is to open a Windows PowerShell prompt and paste the following command, replacing ProjectXXXX with your database name and database server.

Once this has been done, the data source will appear in SPSS as an ODBC datasource.

ODBC Datasource manager.

Alternatively, if you would prefer to use the graphical ODBC manager, click Add ODBC Data Source from within the SPSS database wizard. Click Add and select “SQL Server”, then click Finish.

A new wizard will popup specific to SQL server. Enter a name for your data source (usually something like ProjectXXXX) and provide the server address, e.g. sql.hic-tre.dundee.ac.uk, then click Next.

This will then prompt you for some authentication details - select SQL Server authentication and put “user” in the username field, then click Next. Check the box “Change the default database to” and select your database from the dropdown list, then click Next. You can then click Finish and test the datasource. This should then appear as a datasource within SPSS.

💁 SAS Viya

The sample below is enough to get a connection open and visible within the Libraries section in SAS. Remember to update the username, password (blank) and database name within the connection string.