1

Here is our problem, when using SSRS Report Builder we are able to successfully connect to our data source with no errors, but when we try to connect to a data set we get the following error. Everything was working normally, this issue just started appearing about two weeks ago. Other than trying to access a dataset on the report server, everything is operating normally. We don't get any errors running the report in our web client. This error occurs on any PC that tries to connect to the server using Report Builder, then viewing a dataset.

Dataset Error

We've done a bit of research on this trying to resolve the issue. We have exported every certificate under the Report Servers Personal certificate store to the local machine. We've checked to make sure that none of the certificates are expired. We've checked the certificate selected in the properties of the network protocols in the SQL Server configuration manager. There are two possible certificates to choose from and we've tried them both. (restarting the service after each). We've checked all of the records in DNS and DHCP to make sure there were no errors. We've also checked the CN and subject alternative names on each of the certificates to make sure they were correct for their respective domains.

I think the problem might be there are too many certificates for two domains. We connect to the server using a public domain and a private domain, so we have certificates for both. There are actually three total certificates in IIS. One for the public domain, issued by Godaddy. One is a self-signed certificate for our private domain, and the third is a certificate issued by our in house Certificate Authority for our private domain. Only the two private domain certificates are accessible under the SQL Server configuration manager, but we generally connect to the server through the public domain.

Do we need to remove any of the certificates? how do we get the datasets in report builder to connect properly? It's probably worth noting that we only get this error when viewing properties on a dataset in report builder. SSMS connects with no issues, RDP connects without issues, and our Web servers connect without any issues.

Screenshots of the various certificates. Black boxes are private domain, red boxes are public domain.

Successful Connection to Data Source in Report Builder Successful Data Source

SQL Server Properties Certificate options SQL Properties Cert

Availble Certificates in IIS IIS Certificates

Certificates in the Personal Certificate store on Report server Personal Store on Server

Certificates in Trusted Root Certificates Authority in client PC Trusted Root Certificate Authority

Gene
  • 111
  • 2

1 Answers1

0

For anyone who ends up with the same issue, here is what solved it in this case for us. The root cause was not the certificates. It was actually the Data Source used in the reports. When the report server was originally configured, the data source created for us had a connection type of "Microsoft Azure SQL Database" We changed the connection type to "Microsoft SQL Database" since we are not hosted by Azure. This resolved the error.

Gene
  • 111
  • 2