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