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!

Archive for the ‘DB On SQL’ Category

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

Posted by Jorge on 2016-07-31


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

Posted in Active Directory Federation Services (ADFS), DB On SQL, Ports | Leave a Comment »

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

Posted in Active Directory Federation Services (ADFS), DB On SQL, DB On WID, Uncategorized, Upgrading | 5 Comments »

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

Posted by Jorge on 2014-03-17


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.

image

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>.

image

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.

image

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>.

image

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

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

Posted in Active Directory Federation Services (ADFS), DB On SQL, DB On WID, Security Token Service (STS) | 2 Comments »

 
%d bloggers like this: