(2016-07-31) Configuring ADFS To Use A Custom SQL Port

If for whatever reason you need to change the default SQL port and you need to tell ADFS to use it, or if you need to move the DBs to another SQL server, you can use the following procedure:

–

Retrieving the current connection string for the configuration DB:

Execute the following commands on any ADFS Server:

$fedSvcSTS = Get-WmiObject -namespace root/ADFS -class SecurityTokenService

$fedSvcSTSConfigDBConnectionString = $fedSvcSTS.ConfigurationdatabaseConnectionstring

$fedSvcSTSConfigDBConnectionString

–

Setting a new connection string for the configuration DB:

Execute the following commands ON EVERY ADFS SERVER!:

$fedSvcSTSConfigDBNewConnectionString = "Data Source=SQL1.DOMAIN.COM\SQLINSTANCE,10001;Failover Partner=SQL2.DOMAIN.COM\SQLINSTANCE,10001;Initial Catalog=ADFSConfiguration;Integrated Security=True" <—example values!!!

$fedSvcSTS = Get-WmiObject -namespace root/ADFS -class SecurityTokenService

$fedSvcSTS.ConfigurationdatabaseConnectionstring = $fedSvcSTSConfigDBNewConnectionString

$fedSvcSTS.put()

Restart-Service ADFSSRV

–

Retrieving the current connection string for the artifact DB:

Execute the following commands on any SQL Based ADFS Server, or the primary WID based ADFS Server:

$fedSvcSTSArtifactDBConnectionString = (Get-ADFSProperties).ArtifactDbConnection

–

Setting a new connection string for the artifact DB:

Execute the following commands on any SQL Based ADFS Server, or the primary WID based ADFS Server:

$fedSvcSTSArtifactDBNewConnectionString = "Data Source=SQL1.DOMAIN.COM\SQLINSTANCE,10001;Failover Partner=SQL2.DOMAIN.COM\SQLINSTANCE,10001;Initial Catalog=ADFSArtifactStore;Integrated Security=True" <—example values!!!

Set-ADFSProperties -ArtifactDbConnection $fedSvcSTSArtifactDBNewConnectionString

Restart-Service ADFSSRV

–

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/ ########
———————————————————————————————

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

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:

• AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server
• Windows Server 2012 R2 – AD FS: Migrate Your AD FS Configuration Database from WID to SQL Server
• Migrating the ADFS 2.0 Configuration Database to MS SQL

–

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.

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

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/ ########
———————————————————————————————

(2014-03-17) Gathering Architectural Details From Your ADFS Infrastructure – ADFS Config DB On WID Or SQL

If ADFS was installed in the past by someone else and there is little to no documentation, how do you know if you are using WID or SQL for the ADFS config database? Keep reading to find out how to determine that!

–

ADFS Configuration Database On WID Or SQL?

This is quite easy to determine by running the following PowerShell commands:

$ADFSSTS = Get-WmiObject -Namespace root/ADFS -Class SecurityTokenService
$ADFSSTS

–

When you see the following connection string, your ADFS installation is using WID. In this case the screen dump was taken from a W2K8R2 server with ADFS v2.0.

Figure 1: Leveraging WID For ADFS v2.0 On W2K8R2

–

When you see the following connection string, your ADFS installation is using SQL. In this case the screen dump was taken from a W2K8R2 server with ADFS v2.0. It is also using the default SQL instance. If a custom SQL instance was used, you would see something like <Server Name>\<Instance Name>.

Figure 2: Leveraging SQL For ADFS v2.0 On W2K8R2

–

When you see the following connection string, your ADFS installation is using WID. In this case the screen dump was taken from a W2K12R2 server with ADFS v3.0.

Figure 3: Leveraging WID For ADFS v3.0 On W2K12R2

–

When you see the following connection string, your ADFS installation is using SQL. In this case the screen dump was taken from a W2K12R2 server with ADFS v3.0. It is also using the default SQL instance. If a custom SQL instance was used, you would see something like <Server Name>\<Instance Name>.

Figure 4: Leveraging SQL For ADFS v3.0 On W2K12R2

–

For differences between ADFS on WID or ADFS on SQL see: The Role of the AD FS Configuration Database.

To migrate the database from WID to SQL see: AD FS 2.0: Migrate Your AD FS Configuration Database to SQL Server

–

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/ ########

———————————————————————————————