As part of troubleshooting a recent Windows Server 2016 AD FS issue, I wanted to take a look at the database using SQL Server Management Studio (SSMS). In order to successfully connect there are a couple of gotchas to note as the database used was the Windows Internal Database (WID). This is the default AD FS 2012 R2 and 2016 deployments. There is no SQL management interface and the correct connection string details must be entered.
The intent of this article is to illustrate how to view the AD FS WID database. Changes should be made using the standard AD FS tools unless directed by Microsoft support.
Download and Install SQL Server Management Studio
At the time of writing, the latest version of SSMS available from the Microsoft download centre was 17.8.1 - this will change over time.
SSMS was downloaded and installed onto the primary AD FS 2016 server in the lab. Check the pre-requisites in case additional components need to be installed.
Running SQL Server Management Studio As Administrator
Note – you will need to run SSMS as an administrator. If you do not elevate the tool, it will not be able to connect and you will get the error below:
Connection String For WID
You will need to connect to WID using named pipes and integrated authentication. The sytax is below so it can be copied and pasted:
This is an example of how the connection dialog should look:
Connected to WID
After clicking the Connect button, you should see the below:
Note that we can now see the AdfsArtifactStore database.
Cheers,
Rhoderick
