AD FS 2.0 & WID ( Windows Internal Database ) - under the hood


The default database used for AD FS 2.0 is the WID ( Windows Internal Database ). It is far enough for the most environments, but a real geek like you or me wants to have more control over this. The WID is too much like a black box.

Get access to the WID

  • the WID uses pipes
  • there is no way to connect to it from remote
  • no database tools are installed
  • Actually, it is a stripped down SQL server 2005

In order to connect to the WID using sqlcmd, you should download the following two SQL server 2005 components:

  • SQL server native client: sqlncli_x64.msi ( for x64 systems )
  • SQL server sqlcmd: SQLServer2005_SQLCMD_x64.msi ( for x64 systems )

Download sqlcmd + native SQL client

You can also use SQL Management Studio Express. The connection string is \\.\pipe\mssql$microsoft##ssee\sql\query .

Query the WID database

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
1>SELECT name, collation_name
2>FROM sys.databases;
3>go
name                                  collation_name
------------------------------------- ------------------------------- 
master                                SQL_Latin1_General_CP1_CI_AS 
tempdb                                SQL_Latin1_General_CP1_CI_AS 
model                                 SQL_Latin1_General_CP1_CI_AS 
msdb                                  SQL_Latin1_General_CP1_CI_AS 
AdfsConfiguration                     NULL 
AdfsArtifactStore                     NULL 
(6 rows affected) 

1> use AdfsConfiguration
2> go
Changed database context to 'AdfsConfiguration'.
1> select name from sys.tables
2> go

name
-------------------------------
ClaimDescriptors
ClaimDescriptorExtensibleProperties
Scopes
ScopeIdentities
ScopeSigningCertificates
ScopeClaimTypes
ScopeContactInfoAddresses
ScopePolicies
ScopeExtensibleProperties
Authorities
AuthorityIdentities
AuthorityContactInfoAddresses
AuthorityExtensibleProperties
AuthorityPolicies
MetadataSources
AuthorityArtifactResolutionServices
AuthoritySamlEndpoints
ScopeSamlEndpoints
ScopeAssertionConsumerServices
AuthorityClaimTypes
ServiceSettings
LeasedTasks
ServiceStateSummary
ServiceObjectTypeRelationships
SyncProperties
Policies
ClaimTypes

(27 rows affected)


1> select PropertyName from IdentityServerPolicy.SyncProperties
2> go
PropertyName
--------------------------------
LastSyncFromPrimaryComputerName
LastSyncStatus
LastSyncTime
PollDurationInSeconds
PrimaryComputerName
PrimaryComputerPort
Role

(7 rows affected)


1> use AdfsArtifactStore
2> go
Changed database context to 'AdfsArtifactStore'.
1> select name from sys.tables
2> go

name
-------------------------------
Artifacts

(1 rows affected)

1> select ArtifactID from ArtifactStore.Artifacts
2> go
ArtifactID
--------------
(0 rows affected)



 The database and tables hierarchy is:

  • master
  • tempdb
  • model
  • msdb
  • AdfsConfiguration
    • ClaimDescriptors
    • ClaimDescriptorExtensibleProperties
    • Scopes
    • ScopeIdentities
    • ScopeSigningCertificates
    • ScopeClaimTypes
    • ScopeContactInfoAddresses
    • ScopePolicies
    • ScopeExtensibleProperties
    • Authorities
    • AuthorityIdentities
    • AuthorityContactInfoAddresses
    • AuthorityExtensibleProperties
    • AuthorityPolicies
    • MetadataSources
    • AuthorityArtifactResolutionServices
    • AuthoritySamlEndpoints
    • ScopeSamlEndpoints
    • ScopeAssertionConsumerServices
    • AuthorityClaimTypes
    • ServiceSettings
    • LeasedTasks
    • ServiceStateSummary
    • ServiceObjectTypeRelationships
    • SyncProperties
      • This is the most interesting table that you can find because it contains the settings of the replication between the AD FS nodes
    • Policies
    • ClaimTypes
  • AdfsArtifactStore
    • Artifacts