locked
ADFS 3.0 with SQL Server in a geographically distributed setup RRS feed

  • Question

  • Hi,

    I am working with an AD FS 3.0 environment where there are three sites worldwide. At one of the sites resides the SQL Server cluster where configuration is stored. The AD FS server on that site works perfectly fine.

    On the other two sites logon is very slow, if it succeeds at all. I know the reason is that it takes too long time for the federation service to communicate with the database to check configuration and I assume this is due to latency. 

    As a side note, it takes a long time to open the console at these sites or to run for example Get-AdfsProperties.

    I know that the solution is to add SQL Servers in these two sites and set up replication.

    What I would like is to find documentation from Microsoft that says "you need SQL Server instances at all locations" or "you need SQL Server instances at all locations if network latency is xyz". The closest I find is this article:

    https://docs.microsoft.com/en-us/windows-server/identity/ad-fs/deployment/set-up-geographic-redundancy-with-sql-server-replication

    However, it doesn't actually say it is needed, just how you can do it.

    Is there such a document? Have I lost my mojo with Bing/Google?


    Hth, Anders Janson Enfo


    Friday, July 28, 2017 11:30 AM

All replies

  • Anyone?

    Anyone from MS?


    Anders Janson Enfo

    Thursday, August 3, 2017 8:10 AM
  • From an ADFS perspective, you don't really care how the high-availability of SQL is set-up. You care that the database has to be available all the time. When the ADFS node cannot reach its database, it doesn't serve clients at all.

    What I would do is that unless you are using features that require SQL as a backend, I would move to WID. That is something can do with ADFS Rapid Restore. You can save your SQL based ADFS farm and restore it using a local WID database.


    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Friday, August 4, 2017 5:27 PM
  • Thank you for responding!

    Yes, I do understand what you are saying but it's not quite the answer I was looking for and WID is not an option since features are used ruling out WID.

    The setup works but at the remote data centers, where there's no SQL server, authentication is painfully slow if it doesn't time out.

    Question is, is there a paper from MS saying that "if you have a geographically distributed AD FS environment with configuration in SQL - implement SQL replication to the remote sites unless latency is below X time units". Or something along those lines.

    As mentioned, I know I need to implement SQL replication to the remote data centers but it would be great if the vendor of the software had some written guidelines on the topic. Otherwise I need to "prove" the issue as the solution comes with a cost.


    Anders Janson Enfo

    Friday, August 4, 2017 8:08 PM
  • So this is what I got from a colleague:

    "Our general recommendation is that SQL and ADFS be in the same data center - our testing is generally done with < 5ms round trip.  The highest regular latency I’ve seen in a working production environment was 30-40ms round trip, which was fine for token issuance but when combined with a few thousand of RPs, slowed the MMC UI to the point of being unusable (PowerShell cmdlets still worked for management)."

    Note that there are not a lot of scenarios requiring SQL in the backend.

    1. You need more than 30 ADFS nodes in your farm (it used to be 5 now it is 30, I have not seen a customer in that case yet)
    2. You want to handle more than 100 trusts (I have seen this a couple of time)
    3. You need to use the Artifact Resolution profile of SAML (I have seen only 1 customer using it)
    4. You want to use Token Replay detection. But that is valid ONLY against other Claim Provider trust, not against ADDS as a CP. So if you don't have a trust with another Claim Provider trust you cannot even use that feature.

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Wednesday, August 9, 2017 5:06 PM
  • Hi,

    Any news on this one? We've succesfully implemented SQL Merge replciation with our ADFS configuraiton to have geo redundancy, but the issue we're having is linked to the ADFS syncing the database connection string across all datacenters, so no matter what you do, all your ADFS servers all point to the same database. 

    As requested in the beginning by Anders, any news on documents for this configuration? 

    Best regards,

    Carlos Shrimpton

    Tuesday, October 3, 2017 11:57 AM
  • I have no news from my side of things. This seems to be a scenario that falls out of testing scope at MS as there are no official documenation on how to set this up and what the limitations are.

    The latency numbers quoted by Pierre seems to be a bit on the positive side, in my opinion (based on experience) AD FS is more sensitive to latency than that.

    At the moment it seems that due to the lack of documentation and guidelines from MS we need to abandon the geo-distributed setup which is really not ideal.

    From a design perspective of AD FS internal workings there are most definiately room for improvement here apart from the documentation gap.


    Anders Janson Enfo

    Wednesday, October 4, 2017 7:59 AM
  • By the way, what are the reasons why you opted for SQL instead of WID?

    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Thursday, October 5, 2017 11:06 PM
  • Hi guys,

    Pierre - We are expecting to go well above 100 relying party trusts, there is also a security request for token replay detection.

    Anders - In my scenario, we originally had SQL cluster with the ADFS databases in one datacenter, and wanted an active active scenario with geo redundancy across two datacenters. We use F5 to use GTM for geo-loadbalancing internally and externally, so not all traffic goes via WAP's, our internal users attack ADFS VIP directly, externals on External go via the WAP vip. Health checks ensure that the site is up, no advanced health checks apart form IDP log in page being present, but we're working on that.

    To achieve this, we implemented SQL Merge replication, but the ADFS servers in the new datacenter (Datacenter 2)kept pointing to Datacenter 1.  Turns out, if you use an existing SQL installation something is replicated across in teh database that points them there. We removed the SQL replication, and recreated using the scripts, then proceeded to re-implement merge replication. Running "Get-WmiObject -namespace root/ADFS -class SecurityTokenService " now shows the ADFS servers connected on each datacenters corresponding SQL server, we now have the ADFS datacenters geo redundant, and operating on active active. I expect my scenario to be a rare one as i already had one SQL server up, and implemented Replication after the fact.

    The only setting that isn't geo-redundant that i can see is "artifactdbconnection" which is stored in an xml file in the SQL Database, so even with geo redundancy, adfs will only use one location for that paramter which is a shame.

    Also, if you're preparing a 2016 upgrade from 2012 on SQL, note that the domain admin account has to have access to the SQL database too now, not just the service account. THere's a new switch in Add-ADFSFarmNode for the Installation Credentials, as well as the Service Account Credentials which wasn't present in 2012.

    Hope some of this data helps Anders, let me knnow if you have any further questions to configurations, i've spent the better part of two weeks deepdiving on this setup and will be implementing it in production in the coming weeks.

    BR, Carlos

    Friday, October 6, 2017 8:44 AM
  • Don't abandon it man, I've got it running :) 

    Shoot your issues across i'll see if i can help.

    I agree on the latencies, when i was operating cross datacenter with a high quality dedicated MPLS adfs wasn't performing optimally. SQL Merge replication works though! and both sites are running with their own configurations.


    • Edited by Iberiatum Friday, October 6, 2017 8:47 AM
    Friday, October 6, 2017 8:45 AM