I recently was working on a project where my client had a desire to leverage an experienced SQL administration team to monitor and provide availability for their new ADFS 3.0 database. Normally the ADFS databases (Artifact and Configuration) can be installed and made highly available with utilizing the Windows Internal Database. Technical constraints around using WID revolve around the number of servers in your farm (Less than 5 is supported) and the number of trusts you plan on having (Less than 100 is supported). My client doesn’t have anywhere near the need for SQL from a technology standpoint, but using in-house expertise to manage these databases makes a lot of sense.

There isn’t a wealth of online documentation around a setup like this, so I decided to dedicate a bit of time to seeing if I could make this happen. Install your ADFS roles on your servers as you normally would via server manager or PowerShell. The tricky part comes into play when you have to get the database installed. Due to the selective trust, you have to explicitly setup permissions to access the resources in the target forest. I setup a login for the adfs service account on the SQL server and provided “allowed to authenticate” to the SQL server host via nested groups. I received a cannot generate SSPI context error when trying to satisfy the prerequisite for the ADFS database install. I threw Sql Management Studio on my server and tried connecting using my service account for ADFS. Same error when connecting to the database. So I requested and received remote access to the target SQL server and saw that a service account was running SQL. I allowed my service account to read all properties on the SQL service account. Low and behold, I could connect without an issue to the SQL server/instance. Ran my wizard again, same error. Hrm….

I went down the path of reviewing security logs and found some NULL SID errors. I double checked the SPNs and everything looked proper. So, I reached out to Microsoft for some clarification around supportability and next steps. They provided me an SSPI context log tool to generate some better logs than the eventviewer for these errors. So finally, I ran across a useful error…or at least somewhat useful. “The computer you are signing into is protected by an authentication firewall. The specified account is not allowed to authenticate to the computer.” So, I double check that my nested groups to allow authentication to the computer objects was in place. Everything appears to be configured correctly in both domains ie; SPNs, ACLs, etc…

I then remember there’s a service account running the SQL services that I set read all properties for my ADFS forest service account to read (This allowed me to connect through the SQL Management Studio console, but not through the wizard/pshell for ADFS). I set allowed to authenticate from the ADFS service account to the SQL service account cross forest. Everything installed after that without issue. So long story short, make sure when you are doing selective trusts that any resource that has a service account running the resource…gets allowed to authenticate and read properties.

Dp