Saturday, February 12, 2011

What credentials am I supposed to enter for SQL Server Reporting Services (SSRS) to access the Lync Server 2010 Monitoring Server back-end database?

I don’t think the documentation for deploying Lync Server 2010’s Monitoring Server role was clear for the part where you’re asked:

Specify the credentials to be used by SQL Server Reporting Services (SSRS) to access Monitoring Server back-end database.

image

Here’s what the documentation (LS_Deploy_Monitoring.doc) states:

In Specify Credential Page , specify the user credentials that you want to use to access the Monitoring Server database (QoEMetrics and LcsCDR).

clip_image001Note:

For details, see "Specifying Credential and Connection Information for Report Data Sources (SSRS)" at http://go.microsoft.com/fwlink/?LinkId=203152.

image

Going to the link provided gives you a detailed description of what this is all about but I find that it may be just as confusing for those who aren’t familiar with SQL and SSRS.

So what really happens when you enter credentials during this step in the deployment?  What the wizard will do is actually take the credentials and make an attempt to create the account in Active Directory and then assign permissions for the account to the QoEMetrics and LcsCDR database.  There has been instances where the creation of the account would fail and you’re left stuck not knowing what the proper permissions are.  If you ever run into such a situation, you can actually manually create the AD account and head into SQL Server to assign it to the 2 databases as such:

Database role membership: ReportsReadOnlyRole

image

The example above shows this for the LcsCDR database.  Repeat this for the QoEMetrics database as well and you’ll notice the following during the Monitoring server deployment:

SQL Server login credentials for ‘domain\userAccount” already exist. Use the existing logon credentials.

[QoEMetrics]” role “[ReportsReadOnlyRole]” has already assigned to “domain\userAccount”.

[LcsCDR]” role “[ReportsReadOnlyRole]” has already assigned to “domain\userAccount”.

image

If you’ve ever made the mistake of assigning the account (say you put in an enterprise administrator account) or would like to change the account that is used to connect from SSRS to the back-end SQL database, all you really need to do is navigate to the SSRS reports folder:

image

… then click on the 2 data source listed:

  1. CDRDB
  2. QMSDB

… and change the credentials in there:

image image

Hope this helps clarify exactly what happens between the Monitoring server, SSRS, and back-end SQL server hosting the monitoring server’s databases.

1 comment:

Anonymous said...

Hi Terence,

just following your documentation and have one question. should I create this account in AD first and then use it when the wizard asks for it or should I not. because you sound like the account is auto created in AD when we specify it on the wizard "What the wizard will do is actually take the credentials and make an attempt to create the account in Active Directory "