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