How to find/query the Reporting Services SQL database in MS Dynamics CRM

Question

I'm a SQL Developer and have been tasked with auditing our in house CRM reporting to see which reports are used and which can be discarded, prior to moving to a Data Warehouse solution. I'm fairly comfortable producing reports in SSRS that access the CRM base and filtered tables, but the CRM (MS Dynamics CRM 2015) front end is fairly new to me.

My question is how to find and access the Reporting Services SQL Database that sits behind Dynamics CRM so I can run queries on the report usage?

If can connect to 'a' Reporting Services database on the server from SSMS using the \ format. SSMS reports it is version 11.0.5058.0, but underneath are only three folders, "Jobs", "Security" and "Shared Schedules" - No databases.

Expanding the Security folder I get an error message that ends in

"The report server installation is not initialized. (rsReportServerNotActivated) ReportingServicesLibrary)"

Any ideas? I don't want to log onto the CRM server directly as it's a live system.

Many thanks,

Answer 1

I would suggest that you use powershell. You wont need to directly logon. But you will start a Powershell Session on the live server.

The code you will need is

Enter-PSSession <ServerName> 
Add-PSSnapin Microsoft.Crm.Powershell
Get-CrmOrganization -Name <OrganizationName> 

This should then return something like the following:

BaseCurrencyCode      : GBP
BaseCurrencyName      : Pound Sterling
BaseCurrencyPrecision : 2
BaseCurrencySymbol    : £
BaseLanguageCode      : 1033
DatabaseName          : <Db_Name>
FriendlyName          : <Org_Name>
Id                    : 50c8c53b-be48-4acf-bdd9-10c2a8c0c53c
SqlCollation          : Latin1_General_CI_AI
SqlServerName         : <Sql_Server>
SqmIsEnabled          : False
SrsUrl                : http://<Sql-ReportingServer>/ReportServer
State                 : Enabled
UniqueName            : <Unique_Name>
Version               : 7.0.1.129
ExtensionData         : System.Runtime.Serialization.ExtensionDataObject

If you get the error

Deployment Web Service URL is not available

Then you should manually pass the Deployment Web Service but when you do this, you should also send the a deployment admin credentials.

Enter-PSSession <ServerName>
Add-PSSnapin Microsoft.Crm.Powershell
$Creds = Get-Credential
Get-CrmOrganization -Name <OrganizationName>  -DwsServer <Deployment Server URL> -Credential $Creds