Connect to AD FS 2016 WID Using SQL Server Management Studio


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.

Download SQL Server Management Studio

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:

OOPS! Login Failed - SQL Error 18456 As You Probably Did Not Elevate SSMS

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:

\\.\pipe\microsoft##wid\tsql\query

This is an example of how the connection dialog should look:

WID Connection String

Connected to WID

After clicking the Connect button, you should see the below:

Successfully Connected to AD FS WID Database

Note that we can now see the AdfsArtifactStore database.

Cheers,

Rhoderick

Comments (0)

Skip to main content