DNA Connection error when upgrading to 9.0.x

Software product name		Skills Mangement
Version Number		8.5.x and below
Subject		DNA Connection error when upgrading to 9.0.x

Symptoms

9.0.x upgrade of Skills Management unexpectedly fails.

When checking the installation logs the following SqlClient.SqlException is caught:
CA : Getting Dna Connection Strings
CA : Publish DNA Database starting
CA : Building migration list
CA : Start publishing migrations
CA : APPLY : 2014-03-13 17:05:11 - DNAContext.InitialCA : ERROR : System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Resolution

Invalid DNA Database connection string

Open SQL management Studio and view the SystemesSettings table in the SkillsAssessor (PerformanceDNA)

Example script:

BEGIN TRANSACTION
Select * 
from SystemSettings
ROLLBACK

Check for SettingID 24 from the returned data.

If SettingID 24 is present then DNA was originally configured and the Connection settings are likely to be invalid or out of date. In order to correct this please login to the Tenant Admin site and check the DNA settings.

If you continue with this fix ALL historical DNA data could be lost.

Check for SettingID 25 from the returned data. If there is an encrypted SettingValue string. We will need to remove this entry from the database.

Example script:

BEGIN TRANSACTION
DELETE SystemSettings
where SettingID = 25 and TenantID=-1
ROLLBACK

TenantID will need to be updated to reflect the correct Tenant. Setting the incorrect TenantID will result in another Tenant's historical DNA data being lost

Other considerations to check

If working on a multi-tenanted environment this may need to be repeated for each Tenant as the install process will loop through each Tenant's System Settings and fail on the first incorrect connection string.