SQL Always On Availability Groups for MIM

Edited July 2 2022 after reviewing my Facebook discussion with Eugene Sergeev on Microsoft's product team.

MIM 2016 SP2 (and 4.4.1459.0 or later supports SQL Server Always On Availability Groups (AG))! Yeah!

Ok let's implement it!

But wait! It won't give us all we hope for!

• Up to the moment distributed backup of the data -- yes!
• Automatic instant failover -- not without a huge caveat!

What do you mean it won't give us Automatic Instant Failover?

Let's discuss what AG's give us over the old SQL Failover cluster and mirroring

Failover Clustering

With SQL Server (prior to SQL Server 2012) Failover clustering we have two or more servers in the same subnet (they can be in different datacenters via a stretch VLAN). When the active node goes offline then one of the other nodes will try to grab the necessary resources for that service, and bring them online. One of the critical pieces to note is that the Service has a virtual server name and virtual IP address. DNS points the virtual server name at the virtual IP address. During a failover, the new active node grabs the virtual IP address and sends out a gratuitous ARP request, which causes all devices on the subnet (including the router) to update the MAC address associated with the Virtual IP Address. So almost instantly requests for that IP address will be sent to the newly active node.

Failover Clustering on Multiple Subnets

Starting with SQL Server 2012 we gained the ability to do failover clustering  over multiple subnets. However, failover is much slower as we would now depend on the cluster updating DNS, and DNS needs to replicate as well as DNS records have a time to live (TTL) which tells clients how long to cache the DNS record. The default TTL for cluster registered DNS records in 20 minutes. Which means that it could take 20 mins plus DNS replication time (default is every 180 minutes) for all clients to find the new server. Of course you can lower this, but I wouldn't go below 60 seconds. If your DNS doesn't or isn’t taking dynamic registrations from your SQL Server then you have to update it by hand.

SQL Mirroring

With SQL Mirroring we have two or more servers in the same subnet or different subnets, where one server is principal for a database and the others are mirroring/receiving the transactions either synchronously or asynchronously. Synchronous mirroring with a witness provides for instant failover to a database server in a remote subnet, provided that the client could support the failover_partner parameter in a connection string, or the application would allow you to do that. Of course, if the server specified in the connection string as the data source is up the server can pass the client the name of the failover partner. But it didn't provide for other databases to failover with it as a unit. It also meant that you needed to manage the connection strings, not such a daunting task for only a few clients. 

Always On Availability Groups

SQL 2012 also brought Always On Availability Groups or AGs. With an AG you can group databases together and have them fail over as a unit. You create a listener (a Virtual Server Name and set of IP addresses). The cluster registers the Name with DNS and depending on the cluster parameter RegisterAllProvidersIP it will register all of the IP addresses for all of the nodes hosting the availability group. If in your connection string you set MultiSubNetFailover=TRUE, if your provider supports it, then your client will simultaneously try all of the IP addresses listed and then whichever one responds first (the one hosting the primary replica) will be used. Otherwise, if the MultiSubNetFailover=FALSE or your provider doesn't support it, but RegisterAllProvidersIP =1 on the cluster the client will try all of the IP address returned from DNS, serially, rather than in parallel, which could result in substantial delays connecting to the server. 

What does this mean for MIM?

Now we can turn out attention back to MIM. MIM uses the SQL Native Client 11.0 OLEDB which doesn't support the MultiSubNetFailover keyword. Additional Info on timeouts. In fact, this provider was deprecated until March 2018 . When they released version 18 which adds support for the MultiSubnetFailover keyword.  

 

Per Eugene Sergeev, you could put a second NIC into the MIM server and put it on the other subnet where the other SQL Server is and then you get failover in about 10 seconds. This bypasses the DNS issues.

Does MIM use this new provider? 

Maybe! No mention of it in SP2 release notes  but in the hotfix prior to MIM 2016 SP2, 4.5.412.0, the article references this new ole db provider: 

• TLS 1.2 support is added to the MIM Service and Portal installer. This update will install if TLS 1.2 is the only enabled protocol .  
• After you install this update, the change-mode setup of the MIM Service and Portal will succeed by having only TLS 1.2 enabled and SQL OLE DB driver installed.  
• Microsoft OLE DB Driver 18 for SQL Server must be installed:

But this only references the MIM Service and Portal and not the sync engine. Furthermore, the connection string would still have to change to MultiSubnetFailover=TRUE.

Even if it didn't, we could still cheat if we could control the connection string: "If an availability group possesses only one secondary replica and is not configured to allow read-access to the secondary replica, clients can connect to the primary replica by using a database mirroring connection string." But MIM builds the connection string from the parameters in the registry. It does not allow us to control the connection string to the sync engine DB or the FIM Service DB.

Per Eugene: "MIM Sync is running under .net v4 runtime, even though compiled for 3.5. However, DB access code is c++ and it indeed uses that old driver that does not support multisubnetfailover=true keyword"

So MIM supports High Availability Groups for SQL  but "Synchronization server HA is not supported." 

We are also told that "The SQLNCLI OLE DB Provider does not support the MultiSubnetFailover keyword. To use the MultiSubnetFailover keyword, use the ODBC driver." 

Hmm, could we switch all of the services to use the ODBC provider? NO! Not that I have found. The documentation doesn't provide a way to do that. Therefore, this can only refer to SQL connections made using an MA.

Per Eugene: "As for ODBC drivers - ignore that part. Only GSQL MA uses ODBC."

So what does the "support" for High Availability Groups give us? 

We get distributed mirroring that can automatically failover, albeit very slowly subject to DNS TTL. In the meantime the services will have failed. 

• The FIM Service will retry 10 times with a 6 second timeout after which it fails. 
• The FIM Synchronization service timeout and retries isn't specified. 
• But it too will fail, especially if in the middle of a sync. 
• So we can configure the Services to restart after a short delay. 
• So if we lower the HostRecordTTL cluster parameter to 60 seconds and configure services to restart we might have an automatic failover.

If you set the RegisterAllProvidersIP to 1 then you will get timeouts trying to start the sync service and the FIM service some of the time. If it set to 0 then you will get timeouts after a failover.

This is why the MIM Prepare SQL guide says: 

MIM 2016 SP2 supports SQL AlwaysOn Availability Group (AoAG) listeners with RegisterAllProvidersIP option set to 0, meaning that SQL Server AoAG cross-subnet failover is not currently supported.

So, if you need the MIM Sync database to be highly available you could do an Always on Availability group that is on the same Subnet. If you need it to be distributed you could do a stretch VLAN.

Eugene says my summary here is mostly correct: "The bigger deal is the MIM Service, wherein the failure of a SQL connection can lead to workflow failure. Since the code doesn't retry SQL transactions, quicker failover doesn't help us prevent workflow failure"

Bottom line

The Always on Availability Groups give us a lot of headache and in return we get slightly improved mirroring while the SQL transactions replication/mirroring slows things down. So don't use this if Sync performance or Portal performance is important. Use an AG for MIM PAM or if you have a smaller environment and are doing Password Sync.

Otherwise, doing a staging server or a standby server with SQL Log Shipping.

Wanted: Up and coming Cyber Security Professionals

Cyber Security -- Identity Management Implementer

Secure your identities against the dangers of the Cyber World, automate the repetitive, and empower your users!

Let's

1. Shut the front door on the most obvious  vector for Cyber-attacks
2. Reduce the IT department's compliance burden (SOX, HIPAA, FERPA, GLBA, ISO etc).
3. Free IT people to do tasks that require more brain power

By

1. Automating the drone-like work of managing user identities
1. Disabling accounts of terminated users
2. Creating accounts for new employees, contractors and vendors
3. Deleting old disabled accounts
4. Synchronizing identity info
5. Birth right access
2. Implementing Multi-Factor Authentication
3. Implementing Single Sign On
4. Empowering end-users with Self-Service for Access and Password Resets

 

We focus on Microsoft's Azure Active Directory and Microsoft Identity Manager 2016. We also use other tools. We architect solutions with the aforementioned tools, we enhance those tools by writing code in T-SQL, .NET (C# and VB.NET) and PowerShell that manipulate identities in Active Directory, Azure Active Directory, and many other applications.

 

We want up and coming Cyber Security professionals with 1-5 years' experience in IT who are excited to work with a great mentor (author and 12-time alum of the Microsoft MVP program) and make a difference working with household name clients and others.

 

We are looking for people with some of these skills who can learn the rest quickly. Let's start off with the skills that a Senior Implementer would possess. If you have some of these then we will talk about a role as Senior Implementer, if not then go to the second list.

 

Cyber Security -- Identity Management Implementer Senior - Skills:

• Microsoft's Azure Active Directory
• Especially using it to manage account creation and access to Cloud Applications and On Premise apps too
• Multi-Factor Authentication
• Information Protection
• Password Protection
• Privileged Access Management
• SIEM
• Microsoft Identity Manager 2016
• Synchronization Engine
• MIM Portal
• Privileged Identity Management
• General background in what HR databases look like

 

Now onto the Skills needed for us to ramp you up to being a Cyber Security -- Identity Management Implementer. You don't need to be expert level at all of these or even have experience in all if you are really good in one, and so-so in one or two others and some exposure in any of the rest great! Even if you are so-so in three of them. Databases + Active Directory is a great combo. Active Directory plus .NET also great. Databases plus .NET also great. We will teach you the rest through OJT and online classes.

 

Cyber Security -- Identity Management Implementer - Skills:

• Databases
• Microsoft SQL Server (ideal)
• T-SQL Stored Procedures and Functions
• But at least LEFT Outer Joins and Right
• Some Database administration too
• Oracle, MySQL or something that uses ANSI SQL (Good)
• Programming
• .NET  (dot Net)
• C# (ideal)
• VB.NET (ok)
• Java (then you can learn .NET)
• Scripting
• PowerShell (ideal)
• Unix Shells (good)
• JavaScript (good)
• Dos Batch (ok)
• Directory Administration
• Active Directory (ideal)
• Can Write LDAP queries and understand some of the underlying AD Attributes (Great)
• Understand Domains and Trusts (Good)
• Created some users and groups (ok)
• Any other LDAP (ok)
• Understand Object Classes
• Know difference between LDAP and LDAPS
• Cloud Applications
• Various common cloud applications -- authentication and account creation
• SCIM
• OpenID Connect
• SAML 2.0

 

Work from home the vast majority of the time (occasional travel to clients is required 1-4 trips per year is typical).

 

Pay is competitive with the market, commensurate with experience, and based on how quickly we think we can bring you up to speed to work on clients. Generous annual bonuses based on measurable individual performance as well as contributions to the team.

 

Benefits: Health Insurance, plus a 401k (with low expense mutual funds) with 6% match dollar for dollar plus 401k Profit sharing contributions based on company performance as a whole.

 

 

 

 

MIM Portal Groups whose displayedOwner isn't among the Owners

In the MIM Portal it will create issues if you have a group whose displayedOwner isn't among the objects in the multivalued reference attribute Owner. Querying this through XPath is just about impossible so here is the SQL query to do it.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO​

USE FIMService​

GO​

​

SELECT DOwn.*

FROM (​

SELECT groupObjID = G.[objectID]

           , GroupDisplayName = GAOVS.ValueString

           , userDisplayName= UAOVS.ValueString

           , UserObjID =  U.[objectid]​

FROM [fim].[ObjectValueReference] GOVR​

JOIN [fim].[ObjectValueString] GAOVS​

        ON GOVR.ObjectKey = GAOVS.ObjectKey​

JOIN [fim].[Objects] G​

          ON G.ObjectKey = GOVR.ObjectKey​

JOIN [fim].[ObjectValueString] UAOVS​

           ON GOVR.ValueReference = UAOVS.ObjectKey​

JOIN [fim].[Objects] U​

          ON U.ObjectKey = GOVR.ValueReference​

WHERE GOVR.[AttributeKey] =65 -- DisplayedOwner​

     AND UAOVS.[AttributeKey] = 1 

    AND GAOVS.[AttributeKey] = 1 -- DisplayedName​

) DOwn​ --DisplayedOwners

LEFT JOIN​

(​

SELECT groupObjID = G.[objectID]

              , GroupDisplayName  = GAOVS.ValueString

             , userDisplayName = UAOVS.ValueString

             , UserObjID =  U.[objectid]​

FROM [fim].[ObjectValueReference] GOVR​

JOIN [fim].[ObjectValueString] GAOVS​

        ON GOVR.ObjectKey = GAOVS.ObjectKey​

JOIN [fim].[Objects] G​

         ON G.ObjectKey = GOVR.ObjectKey​

JOIN [fim].[ObjectValueString] UAOVS​

          ON GOVR.ValueReference = UAOVS.ObjectKey​

JOIN [fim].[Objects] U​

          ON U.ObjectKey = GOVR.ValueReference​

WHERE   GOVR.[AttributeKey] =138 -- Owner​

        AND UAOVS.[AttributeKey] = 1 

        AND GAOVS.[AttributeKey] = 1 --DisplayedName​

) Own​ -- Owners

On Down.gObjID = Own.gObjID 

    AND Down.UObjID = Own.UObjID​

WHERE Own.UObjID IS NULL​

order by DOwn.GacctName

Latency vs the Cloud

"The cloud is so fast! We can spin up servers and services so quickly to extend our environment and then all the users across the globe can access these services, so why does it take so long for you to get our users into the cloud?"

(Latency) x (# of Round Trips)

Most CLoud Identity Management APIs are built so that consumers must retrieve the data one object at a time or load it one object at a time. This means one roundtrip per object. Naturally, a data set in the cloud tends to be farther away than between two servers in the same data center. So the one object at time paradigm that worked ok in the data center works fine in the cloud for very small sets of objects. Once you start loading even moderately sized data sets of objects the additional latency shows up quite harshly. More bandwidth won't solve the problem.

Let's try an analogy: bandwidth is like the number of lanes on a freeway, which means more traffic can pass through the same point at the same time, whereas latency is like the length of the freeway between your source and destination, the farther away you are from your destination the longer the trip will take. 

Building in a bulk mechanism for cloud APIs is a must! When moving across the country I want to load my stuff into a moving truck, not take one object at a time in my car! The same applies to the act of moving data, which I move across the country a whole lot more frequently than I do my family. Please, owners of Cloud APIs, don't force me to get/load one object a time, let me use a moving truck (bulk export and import)!

MIM Open Source Schedulers

Your MIM installation is in, the config is done, programming all set and now to automate the running of the Management Agents.

Options? Most people use Windows Task Scheduler with a PowerShell script or VBScript -- which works but can get cumbersome to maintain. With my SQL Server background, I often use SQL Server Agent Jobs because it has much better follow up and executing database commands.

• Task Scheduler -- runs as a windows service
• Can launch a job based on multiple triggers: 
• scheduled
• CPU idle  
• an event in the event log
• startup
• user logon 
• Step Types
• Command line
• What it lacks is follow up -- it doesn't write to the event log, it doesn't have email capability, it doesn't do a good job of storing the results.
• SQL Agent -- also runs as a windows service
• Schedule types:
• Recurring -- recur every x hours, x min or x seconds
• Daily
• Weekly
• Monthly
• One time
• SQL Agent Starts
• CPU idle
• Step Types
• SQL
• Command Line
• PowerShell
• SSIS
• Follow up is pretty good -- 
• Write to the event log
• It automatically records the results of each step -- I find it much easier to troubleshoot a SQL Agent job as opposed to a Task Scheduler job.
• You can control the level of detail recorded
• Send an email on failure or success
• Can even have a pager schedule
• You can jump from one step to another on success or failure

A search of GitHub reveals a few Open Source options that provide features more specific to MIM.

Open Source options:

• Aseand's RunScript is a PowerShell script. How does that help? It has all kinds of pre-built functions that are commonly needed in your own PowerShell script -- so this isn't really a scheduler as you still need to use Task Scheduler, but not a bad place to start your own PowerShell script.
• AnyInProgress Ma's
• Clearrunhistory
• MSSQLExecute
• export-count
• import-count
• stage-count
• start-agent -- runs an Agent asynchronously
• Runagent -- runs an Agent and waits -- looks to have some great error handling
• getRunHistory
• getRunHistoryOld
• WriteXmlToScreen -- for printing out the CSEntry data
• SaveChangeCS -- get the pending CS changes
• Aseand also created a PowerShell script that does some pretty nice Sync engine documentation by querying the Sync engine database
• Fellow MVP Ryan Newington (lithnet)'s miis-autosync -- runs as a windows service 
•  Schedule
• Recurring schedules 
• events such as changes to AD, HR etc
• The other key feature is it can be set up to only run if there is work to be done. It can also run as many profiles in parallel as possible. This is one of the best ways to cut a sync cycle as short as possible. 
• Send emails when job fails
• Clear Run History
• Wim Beck's FIM Scheduler -- uses XML config files, can run linear sequences and parallel sequences. It also runs as a Windows Service
• Former MVP Soren Granfeldt's MA Run Scheduler -- was built as a replacement for the MASequencer from the MIIS Toolkit. 
• Traxion Solutions also has an MIIS Sequencer to replace the MA Sequencer

How to Be an MVP in Life -- Launching Nov 27th

We are launching my new book, “How to Be an MVP in Life: Lessons in Living and Leadership from Sports & Tech MVPs” on November 27th. It is available now for Pre-order at Amazon.

Featuring an interview with the 2016 World Series MVP, Ben Zobrist, stories about 2-time Pro-Sports MVPs: Steve Nash, Dale Murphy, Steve Young and Sid the Kid Crosby, as well as interviews with 18 Microsoft MVPs.

More info

Missing the old Directory Experts Conference? Try HIP!

On Monday, Nov 5th, and Tuesday the 6th I will be attending and speaking at the Hybrid Identity Protection (HIP) Conference in NYC. On Monday at 4 PM I will be giving an updated version of Top Lessons Learned from Disasters in Identity Management as well as a sneak peek of my new book, How to be an MVP in Life.

I am very excited to attend this conference. Thanks to Darren Mar-Elia and Micky Bresman at Semperis for putting it all together. This should be a lot like the old DEC -- Directory Experts Conference since it looks like DEC co-founder Gil Kirkpatrick is heavily involved.

I would recommend going to Sean Deuby's talk on Azure AD Protection but he and I are speaking at the same time.

I do highly recommend Brian Desmond's talk about 10 quick Identity Wins with Azure AD, and the Conditional Access Deep Dive with Joe Kaplan.