I stumbled upon this error when I was busy upgrading a ADFS v3.0 farm built on Windows 2012 R2 to a brand new ADFS v4.0 built on Windows 2016. I will write a full article how to do that yourself, but that is for something later.
 
This error is a very strange error 'Message: Unable to determine the current Farm Behavior. Error retrieving configuration from remote SQL Server instance...'  
Probably you Googled it and ended up with nothing or something that wasn't what you were looking for.
 
I got this error, like i said earlier, when i was busy with adding ADFS servers to the existing ADFS farm.
 
After searching, and eventually pulling my hair out, I opened up an TAC (Technical Assistance Center) case at Microsoft Support. After running the standard diagnostic tools, the Engineers over in Redmond started with their analysis.
 
In the meantime, I contacted a Microsoft consultant I worked with earlier and asked his advice what it could be.
He suggested to change back the Database and Catalog name to the original ones by renaming them; that turned out to be the golden answer :-)
 
As you notice in the figure 1 below, I have a PowerShell cmd-let that specifically targets an ADFScatalog and ADFSDatabase. The original syntax that I pulled out of the summary, just before you click Finish in the GUI adding a ADFS server to a farm, is different in comparison to mine.
 
 

So why were the database and catalog files renamed?

 
Well the engineer that designed this particular farm found a way to place two ADFS databases of two separate ADFS farms on one SQL Database server. The catch here… that it is on one instance… yes; two ADFS environments on 1 SQL instance. You'll probably thinking; that is not possible!. And yes, that was my immediate thought also. Because during the install of your first ADFS server, thus your ADFS Farm, you don't have a option to rename the ADFS database. So it is also not possible to place 2 ADFS environments on the same SQL instance. You can place them on the same SQL server, but only if you use two separate instances,, but that was not the case in my situation.
 
So how did this engineer accomplished this in the first place? We'll, I think he got his inspiration by following this article.
 
The article shows you how to migrate a WID (Windows Internal Database) to a SQL database, and in the process adjust some files to make the whole thing work.
 
I think that engineer in my case followed this article and did the following:
 
-  Take Offline and dismount SQL databases.
-  Renamed the files *.MDF and *. LDF to something else, rather than 'AdfsConfiguration' and 'AdfsArtifactStore'; in my case <NETBIOSname>Configuruation and <NETBIOSname>ArtififactStore
-  Executed the following sql script:
 

USE [master]
GO
CREATE DATABASE [<NETBIOSNAME>Configuration] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<NETBIOSNAME>Configuration.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<NETBIOSNAME>Configuration_log.ldf' )
FOR ATTACH
GO
 
CREATE DATABASE [<NETBIOSNAME>ArtifactStore] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<NETBIOSNAME>ArtifactStore.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\<NETBIOSNAME>ArtifactStore_log.ldf' )
FOR ATTACH
GO
ALTER DATABASE AdfsConfiguration set enable_broker with rollback immediate
GO

 
-  Set appropriate permissions like in the blog post on blog.loud.com.au.
-  Executed the custom PowerShell cmd-lets:
 

$temp= GEt-WmiObject -namespace root/ADFS -class SecurityTokenService
$temp.ConfigurationdatabaseConnectionstring="data source=[sqlserver\instance];initial catalog=<NETBIOSNAME>configuration;integrated security=true"
$temp.put()

Set-AdfsProperties -ArtifactDbConnection "Data Source=[sqlserver\instance];Initial Catalog=<NETBIOSNAME>ArtifactStore;Integrated Security=True"

 
-  Followed the rest of the blog post I presume.
 
By following the above, you can install two ADFS farms on one SQL server with one instance, as long if you make sure that the original name is not the same. But still, it was a assumption that I made. Unfortunatly for me, it was nowhere documented how the farm was built and where there was deviated from a standard ADFS implementation.
 
So what to do? I started by following the advice I got from my Microsoft Consultant contact.
 

 
 
I started by backing up the complete database; you can follow this TechNet article.
 
Then I took the databases offline and renamed the physical files. After that I started with the restore procedure in SQL Management studio, and suddenly (yes, that Eureka moment) it struck me. I noticed that the Logical  file names where different compared to the physical file names… so that meant something or somebody really altered the stuff to make it fit!
 

 
So I began the ' fix and repair' procedure by following the blog article I mentioned earlier and using the original names again. Afterwards ran some tests to make sure ADFS was still working, which wast the case.
 
Checked if now can add a ADFS node to the existing farm, and that succeeded.
 

Lessons learned

 
Keep in mind that when you alter the names of ADFS databases and its configuration like in the blog article, you can never add new ADFS servers to the existing ADFS Farm because you cannot point the powershell cmd-let to a specific database. Hence that my earlier attempts didn't succeed (figure 1)
 
So that's it, everything is honky dory now.
 
Thank you for reading and see you next time. Now take a cup of coffee first :)