Reporting Services Point with SQL Cluster

[Ente]

I'm trying to get reporting services working and currently when I attempt to create a report I get the following error:

 

"The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel."

 

Reporting was an after-thought when I built this 2012 environment as its not vital to our infrastructure, but now we need to make use of it.

 

We have a Windows 2008 R2 cluster with 2 nodes and each runs SQL 2008. I initially put the Reporting Services Point on the name of the cluster itself, but I'm not sure where exactly the Reporting Role should be place.

 

Should it be on each SQL node or the Primary Site server itself?

 

We just run a standalone Primary Site connected to a 2 node SQL cluster.

[GarthMJ]

SSRS is not supported to be installed on a cluster. You can install SSRs on one or both node but each will be separate install of SSRS. In order to use CM12 RP you would need to install the RP on each node and access then via the node address not the cluster address.

 

As an aside, I never recommend splitting SQL from the primary server.

[Ente]

Not to get into a best practices argument, but isn't it usually best to keep the database running on a separate server from client management products? I'm thinking from a disaster recovery point of view.

 

I've been burned in the past by running them on the same box.

[GarthMJ]

There is no best practice either way, there is few note about performance on site with 100 000 device or more. However, from a management standpoint you will have far fewer support issues with SQL be local than with it being remote. Heck I add 15 days to any project where SQL is remote due to the complexity it add and the inevitable problem that will crop up over time.

[Ente]

I added the RP to both SQL nodes and I'm still receiving the same SSL/TLS error.

 

I'm not very experienced with SQL, but I'm assuming this could have something to do with Certificates?

 

I have my SCCM environment working in native mode with a PKI.

[GarthMJ]

Where did you install the RP? Are you trying to access the site via the cluster or the one of the node of the cluster?

[Ente]

I installed the RP's on each node and I was attempting to create a report from within the SCCM Admin Console.

[GarthMJ]

Create a report, I think that I missed that tidbit, did you copy the SQL certificate to you local PC?

 

And just to close off the other bit, can you run report from your RPs?

[Ente]

I finally had some time to take a deeper look at this and here are the errors I see when attempting to install the Reporting Point on just one of the SQL Nodes:

 

Error Milestone XXX 10/28/2013 12:05:06 PM SQLNODE02.DOMAIN.LOCAL

SMS_SRS_REPORTING_POINT 7403 The report server service is not running on Reporting

Service Point server "SQLNODE02.DOMAIN.LOCAL"; start the service to enable reporting.

 

This is strange because I can verify that the service is running on the SQL node.

 

Here's some of the other log entries I see:

Information Milestone XXX 10/28/2013 12:05:11 PM sqlnode02.DOMAIN.local

SMS_SITE_SYSTEM_STATUS_SUMMARIZER 4721 Site System Status Summarizer 

detected that the availability of the "Reporting services point" role on server "\\sqlnode02.DOMAIN.local" 

has changed to Failed. Possible cause: This Configuration Manager Server Role is unable to function. 

Solution: Verify that no security or configuration problems exist and that all related components are 

running.

Information Milestone XXX 10/28/2013 12:05:10 PM SQLNODE02.DOMAIN.LOCAL

SMS_SITE_SYSTEM_STATUS_SUMMARIZER 4709 Site System Status Summarizer 

detected that the storage object "\\SQLNODE02.DOMAIN.LOCAL\M$\SMS" on site 

system "\\SQLNODE02.DOMAIN.LOCAL" has 335762524 KB of free storage space, which is above the Free 

Space Thresholds for this storage object. Site System Status Summarizer will now set the status of the 

storage object to OK in the Site System Status summary in the Configuration Manager Console.

Information Milestone XXX 10/28/2013 12:05:10 PM SQLNODE02.DOMAIN.LOCAL

SMS_SITE_SYSTEM_STATUS_SUMMARIZER 4702 Site System Status Summarizer 

successfully accessed storage object "\\SQLNODE02.DOMAIN.LOCAL\M$\SMS" on site 

system "\\SQLNODE02.DOMAIN.LOCAL". Possible cause: The storage object had been inaccessible 

since "10/28/2013 12:00:00 PM". Solution: If the storage object has sufficient free storage space, Site 

System Status Summarizer will now restore the status of the storage object to OK in the Site System 

Status summary in the Configuration Manager Console.

Information Milestone XXX 10/28/2013 12:05:10 PM sqlnode02.DOMAIN.local

SMS_SITE_SYSTEM_STATUS_SUMMARIZER 4709 Site System Status Summarizer 

detected that the storage object "\\SQLNODE02.DOMAIN.LOCAL\M$\SMS" on site 

system "\\sqlnode02.DOMAIN.local" has 335819200 KB of free storage space, which is above the Free 

Space Thresholds for this storage object. Site System Status Summarizer will now set the status of the 

storage object to OK in the Site System Status summary in the Configuration Manager Console.