SSRS Dataset error : Target Principal Name is Incorrect

0
votes

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.

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.

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?

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

enter image description here

Availble Certificates in IIS

IIS Certificates

Certificates in the Personal Certificate store on Report server

enter image description here

Certificates in Trusted Root Certificates Authority in client PC

enter image description here

1
iisreporting-servicescertificatessrs-2012reportbuilder
These errors typically mean that the name that is being specified in SQL Management Studio doesn’t match the CN on the certificate. The CN identifies the fully qualified domain name associated with the certificate. It is typically composed of a host and a domain name, the certificate is valid only if the request hostname matches at least one of the certificate common names. Please check the certificate information and look for the CN value. Then you can find the FQDN by executing the command shell: IPCONFIG /ALL. If they don’t match, please add the FQDN to the CN value of your certificate. - Jalpa Panchal
refer this link for more detail:stackoverflow.com/questions/37734311/… - Jalpa Panchal
@Jalpa, We did check that thread. We already made sure the permissions were identical. We've also verified that the CN name on the two private certificates match. It's probably worth mentioning that we have absolutely no issues connecting to the database using management studio or odbc strings on our web server. The 1 and only connection issue is the one described above. - Gene

1 Answers

0
votes

This one turned out to be a little odd, but i'll post the answer in case anyone else finds this thread. When the SSRS service was set up, they pre-installed the data sources for us. The connection type for the data source was set to "Microsoft Azure SQL Database". We don't use Azure. I changed the connection type to "Microsoft SQL Database" and the error went away.

enter image description here