Database connection error

[helendduncan]

✅ Checklist

• I have searched open and closed issues for duplicates.
• This is a problem observed when managing a Data Safe Haven.
• I can reproduce this with the latest version.
• I have read through the documentation.
• This isn't an open-ended question (open a discussion if it is).

💻 System information

• Operating System:
• Data Safe Haven version: 5.2.0

📦 Packages

List of packages

Paste list of packages here

🚫 Describe the problem

Having built an SRE with MSSQL - it is not possible to connect via beekeeper studio and the smoke tests fail.

🚂 Workarounds or solutions

[JimMadge]

From the messages and nslookup failing, my best guess is that the host of the database is not what we expect.

[JimMadge]

Just noticed, the nslookup argument is incorrect mssql.dsgdsgscotrailjan25.turingsafehaven.ac.uk rather than mssql.dsgscotrailjan25.turingsafehaven.ac.uk

[helendduncan]

Still no joy :(

[helendduncan]

First problem was due to absence of hyphens in username

[helendduncan]

Found correct settings:
Originally hyphens in SRE name were omitted in the username.
If the SRE name has hyphens in it they should be included in the username.
Presumably smoke tests are failing as they are constructing username without hyphens

hostname = mssql.
user = databaseadmin@shm--sre-db-server-mssql
password = password-database-service-admin secret for the SRE rg (note there are two database passwords in the key vault)

• Update system manager docs to reflect this

[JimMadge]

On the domain name, this is constructed for the smoke tests here,

data-safe-haven/data_safe_haven/resources/workspace/ansible/files/usr/local/smoke_tests/test_databases.sh

Lines 24 to 39 in af7d7e9

	sre_fqdn="$(grep trusted /etc/pip.conf | cut -d "." -f 2-99)"
	sre_prefix="$(hostname | cut -d "-" -f 1-4)"
	if [ "$db_type" == "mssql" ]; then
	db_name="master"
	port="1433"
	server_name="mssql.${sre_fqdn}"
	hostname="${sre_prefix}-db-server-mssql"
	elif [ "$db_type" == "postgresql" ]; then
	db_name="postgres"
	port="5432"
	server_name="postgresql.${sre_fqdn}"
	hostname="${sre_prefix}-db-server-postgresql"
	else
	echo "Did not recognise database type '$db_type'"
	exit 1
	fi

For MSSQL the hacky solution is to get the domain from the pip.conf file which is presumably wrong.
Not also a Postgres server was not deployed so it isn't clear whether that username is correct or not.

[jemrobinson]

note there are two database passwords in the key vault

What are the two passwords for and can we disambiguate them?

[JimMadge]

The passwords that confused us are,

• password_database_service_admin and,
• password_user_database_admin

Presumably the user database is for Guacamole? I think it confused us because user_database sounds like it could be "database for the users".

We can improve the names but should also improve the docs which currently just say "your sysadmin knows the password".