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 - 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 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. 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:
...
Server type: Database Engine
Server name: your unique SQL server address usually sql.hic-tre.dundee.ac.uk
Authentication: SQL Server Authentication (not Windows Authentication)
Login: user
Password: provided, but likely blank
...
| Code Block | ||
|---|---|---|
| ||
> 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="userproject-XXXX", 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="userproject-XXXX", 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 |
...
| Code Block |
|---|
* Setup the string which defines our connection
local connString "Driver={SQL Server};Database=ProjectXXXX;Server=projectXXX-sql.hic-tre.dundee.ac.uk;UID=userproject-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'") |
...
| Code Block | ||
|---|---|---|
| ||
import pyodbc import os # Replace these with the details provided to you sqlServer = 'tcp:projectXXXX-sql.hic-tre.dundee.ac.uk' sqlUsername = 'userproject-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;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() |
...
| Code Block | ||
|---|---|---|
| ||
Add-OdbcDsn -Name "ProjectXXXX" -DriverName "SQL Server" -DsnType "System" -SetPropertyValue @("Server=projectXXXX-sql.hic-tre.dundee.ac.uk", "Trusted_Connection=Yes", "Database=ProjectXXXX") |
...
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.
...