The Story:

You might have got a request to upgrade from ADFS 2012 R2 to Windows ADFS 2016.

This process can be complicated especially if you’ll have to migrate the Database as well and it would be more of an issue when the Database is WID (Windows Internal Database) since there’s no much documentation about troubleshooting issues involving WID on ADFS.

I have got a request from a client whom have done a migration with another consultant and obviously it was not done right.

Symptoms

On Windows 2016 ADFS when trying to update the ADFS SSL certificate I get the following error:

Set-AdfsSslCertificate -ThumbPrint A7etc : PS0159 : The Operation is not supported at the current Farm Behavior Level ‘1’. Raise the farm to at least version ‘2’ before retrying.

At line:1 char:1

Trying to update the database from 1 to 2,3 will also fail with the following error:

Invoke-AdfsFarmBehaviorLevelRaise

Error:

Database upgrade cannot be performed on AdfsServer.domain.com. Error: A database for the target behavior level already exists.

Troubleshooting:

If you’re installing ADFS on WID (Windows Internal Database) you should run the following to get the database name/Connect String

On ADFS Server

Open Windows PowerShell

1. Enter the following:
   $adfs = gwmi -Namespace root/ADFS -Class SecurityTokenService

   and hit Enter

2. Enter the following:
   $adfs.ConfigurationDatabaseConnectionString

   and hit enter.

3. You should see the connect string information.

Go to Service Console and stop ADFS Service or from Powershell type Net stop adfssrv

Run SQL Server 2017 Database Engine Tuning Advisor as an administrator

Use the Server name as this

\\.\pipe\MICROSOFT##WID\tsql\query

As for Authentication, Use the Windows Authentication with the user you’re logged into if you know that’s a privileged user and can authenticate, If not try with a user which you’ve done the upgrade of ADFS with.

After authenticating, You will be able to see AdfsConfiguration , AdfsConfigurationV3 and AdfsArtifactStore. What we need to see is that AdfsConfigurationV3 has data in it and is not totally empty.

After checking and comparing the size between V1 and V3, It appeared that V3 database is empty. So what next?

Solution

Deleting the AdfsConfigurationV3 was the first thought that hit my mind however, before deleting anything I always take a snapshot of the VM since backing up the WID is more painful and takes more time than simply backing up the VM (Checkpoint, Snapshot).

So the steps to fix this issue is

• Taking a VM Snapshot/Checkpoint/Backup.
• Download Microsoft SQL Server Management Studio from this link https://go.microsoft.com/fwlink/?linkid=864329
• Install Microsoft SQL Server Management Studio on ADFS Server
• Run MS SQL Server Management Studio as Administrator
• In the Server Name type :

\\.\pipe\MICROSOFT##WID\tsql\query

Leave the Authentication as it is and logon.

• From the SQL Object Explorer right click and Delete the AdfsConfigurationV3 and leave AdfsConfiguration Database only.

• After deleting the Database, Start ADFS Service to make sure that it can load the old database without an issue.
• Then run the cmdlet Invoke-AdfsFarmBehaviorLevelRaise and Accept by typing Y and Enter.

This might take about 5 minutes to finish.

When this process is done, You should see the following message indicating the success of the Database Upgrade.

To double check, We will run the cmdlet Get-AdfsFarmInformation

Updating Certificate

After this success, I am going to run the cmdlet below to replace the current certificate with the new one

Set-AdfsSslCertificate -Thumbprint 9b19426e17180c0b9c5d4atye53dda3bce9dbff

And here we go. It works perfectly fine

References:

https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/troubleshooting/ad-fs-tshoot-sql

https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/design/federation-server-farm-using-sql-server