Jorge's Quest For Knowledge!

All You Need To Know About Identity And Security On-Premises And In The Cloud. It's Just Like An Addiction, The More You Have, The More You Want To Have!

(2015-02-24) Migrating ADFS Databases From SQL To WID

Posted by Jorge on 2015-02-24


For whatever reason you may be interested in migrating your ADFS databases, currently hosted on WID, to SQL server. Information regarding that can be read through the following links:

Now some of you might think: "is it possible to migrate the ADFS databases, currently hosted on SQL server, to WID?"

Now why would you want to do that?

  • SQL features related to ADFS are not being used (Token Replay Prevention and Artifact Resolution)
  • Saving costs on SQL server licenses
  • Simplifying DR and high availability for ADFS

Now to answer the questions…NO, it is not possible to migrate ADFS databases from SQL server to WID!

I tried this myself. I did a backup of the ADFS databases on SQL server and then tried to restore those same databases on WID.

image

Figure 1: SQL Management Studio Connected To WID (Local) and SQL Server (Remote)

As soon as you try to do the restore, you will see an error similar to the following

image

Figure 2: Failing To Restore A Database Previously Hosted On SQL Server To WID

Now why is this? The simple answer is: WID basically uses an older version of SQL than SQL server itself. You can restore a database from a lower version of SQL to a higher version of SQL, but you CANNOT restore a database from a higher version of SQL to a lower version of SQL!

You can read more about this here.

The only way to go from SQL to WID is to export all the settings/configurations from ADFS on SQL and import that again into ADFS on WID. Also see: https://jorgequestforknowledge.wordpress.com/2014/03/12/additional-powershell-scripts-for-migrating-adfs-v2-x-to-adfs-v3-0/

Cheers,
Jorge
———————————————————————————————
* This posting is provided "AS IS" with no warranties and confers no rights!
* Always evaluate/test yourself before using/implementing this!
* DISCLAIMER:
https://jorgequestforknowledge.wordpress.com/disclaimer/
———————————————————————————————
############### Jorge’s Quest For Knowledge #############
#########
http://JorgeQuestForKnowledge.wordpress.com/ ########
———————————————————————————————

5 Responses to “(2015-02-24) Migrating ADFS Databases From SQL To WID”

  1. pipnz said

    Can the configuration be exported using pwoershell somehow and then imported using powershell commands to apply the same configuration to a new WID database?

    • Jorge said

      The only way to go from SQL to WID is to export all the settings/configurations from ADFS on SQL and import that again into ADFS on WID

  2. Thomas said

    We want to move from ADFS 3.0 SQL to WID, so we have to setup a new farm in parallel with same name and service account. No problem up to there. But how can we export and import all the configuration (relying party trusts, claim rules and stuff). I thought it would be easiest to use the export-federationconfiguration.ps1 on the existing ADFS 3.0 server but it throws an error: Unable to find type [Microsoft.IdentityServer.PowerShell.Resources.RelyingPartyTrust]. Make sure that the assembly that contains this type is loaded.

    Is this a supported method to export from ADFS 3.0 and import to a new ADFS 3.0 farm with this cmdlet?

  3. […] you may have read in this blog post you need to export and import the configuration when moving from SQL-based ADFS to WID-based […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: