We currently provision an SQL server for each project. This ensures total separation between each project. For each of these, we provide you with the following details:
Hostname
Database name(s)
Username
Password
The hostname will be something like project-<number>-sql.hic-tre.dundee.ac.uk and the username and password will be something simple such as "user" with a blank password. While this seems insecure, we are able to do this with confidence because there is no risk of a workspace from another workspace being able to connect to your project’s SQL server.
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:
\uD83D\uDCD8 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: your unique SQL server address
Authentication: SQL Server Authentication (not Windows Authentication)
Login: user
Password: provided, but likely blank
A screenshot is provided on the right showing an example of this. 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: your unique SQL server address
Authentication type: SQL Login
Username: user
Password: provided, but likely blank
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="projectXXXX-sql.hic-tre.dundee.ac.uk",
UID="user", PWD="", TrustServerCertificate="Yes")
# 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="projectXXXX-sql.hic-tre.dundee.ac.uk",
UID="user", PWD="", TrustServerCertificate="Yes")
> 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.
* Setup the string which defines our connection
local connString "Driver={SQL Server};Database=ProjectXXXX;Server=projectXXX-sql.hic-tre.dundee.ac.uk;UID=user"
* 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:projectXXXX-sql.hic-tre.dundee.ac.uk'
sqlUsername = 'user'
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;TrustServerCertificate=Yes' % (
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.
Add-OdbcDsn -Name "ProjectXXXX" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=projectXXXX-sql.hic-tre.dundee.ac.uk", "Trusted_Connection=Yes", "Database=ProjectXXXX")
Once this has been done, the data source will appear in SPSS as an ODBC datasource.
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. projectXXXX-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.