Lync/SfB Server: Certificate assignment location

Usually to check the Lync/SfB Server assigned certificates we simply use the Certificate Wizard (Step 3 in the Lync/SfB Deployment Wizard):

Or we can also use a PowerShell cmdlet:

Get-csCertificate
https://docs.microsoft.com/powershell/module/skype/get-cscertificate

However, there is another way… We can go directly to where the assigned certificates information is located.

The certificate assignment is stored in XML, in the Item table, that belongs to the XDS database hosted in the RTCLOCAL SQL Instance.

We can use the SQL Management Studio to connect to the RTCLOCAL using the following SQL query:

SELECT [Data] FROM [xds].[dbo].[Item] Where ForLocalStoreOnly = 1;

Note: The Item table contains the copy of the topology, but there is an entry that is local to each Front End, this is why we can filter for ForLocalStoreOnly.

Or we can also use PowerShell to save it to a XML file:

(Invoke-Sqlcmd -MaxCharLength 8000 -ServerInstance “.\RTCLOCAL” -Query “SELECT [Data] FROM [xds].[dbo].[Item] Where ForLocalStoreOnly = 1;”).Data | Out-File -FilePath “C:\Temp\LocalSQLCertificates.xml”

Note: We need to specify the MaxCharLength to 8000 because sometimes the XML can be larger than the default max value (4000).

As previously mentioned the Item table contains the a copy of the Lync/SfB Server Global Configuration. This is store in several Documents to understand what documents we need to use the Document table:


SELECT [DocId]
,[Name]
FROM [xds].[dbo].[Document]
Order by DocId

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.