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

About the Author: Steve Bowman

Steve Bowman is a Partner at Model Technology as well as their Vice President of Sales and Marketing. Steve is a father, husband, Franciscan, and lover of technology. He's bilingual in business and technology and have over 30 years of experience in selling enterprise technology solutions in a variety of industries.

Three Minutes For A More Secure & Efficient Infrastructure

Short and to the point, Steve’s Email Blasts give you endpoint management tips, tricks, and news in three minutes or less email read-time, guaranteed.

Model says no to spam. Privacy Policy

Model Technology Solutions

Model Technology Solutions is a small but mighty band of infrastructure experts. We’ve helped companies in diverse industries to modernize and automate their infrastructures through effectively managing their Microsoft endpoint suite.

With us on your team, you’ll watch your security and compliance go up and your IT team’s costs (and headaches) go down. You’ll relax in knowing that your endpoints will be secure and online when your users need them most. And you’ll finally get back to your most-important tasks.

Model Technology Solutions
12125 Woodcrest Executive Drive, Ste. 204 Creve Coeur, MO 63141

Phone: (314) 254-4138
General Inquiries: model@model-technology.com
Sales and Quotes: sales@model-technology.com