A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.) (Framework Microsoft SqlClient Data Provider)
Setting up SSRS on a new Windows and SQL 2022 server. When going through the Report Server Configuration Wizard we are receiving the below error. We have attempted to use a local Microsoft CA cert as well as a wildcard GoDaddy public cert but neither seem to be accepted by SSRS. SQL Cert Info.!
Why this happens/occurs
- When you don’t have valid certificate installed/configured for SQL Server
- There could be probability that certificate CN (Common Name) doesn’t match the server FQDN (Fully Qualified Domain Name).
- Installing/Configuring cert in the user store instead of doing it on Local Computer store
- SQL Server service account doesn’t have permission on private key of the certificate which is configured on the SQL Instance
- Note: Major times I have seen this below problem in most of the cases/problems:
Suppose you don’t have certificate installed on the SQL Server and you have chosen the option from the Flags as Force Encryption to “Yes” then this is the problem that you see in the configuration manager.
Why SSRS Don’t Encrypt?
If you don’t have certificate installed for the SQL Instance and you have chosen the option Force Encryption to “Yes” then, the SQL uses its own self-signed certificate, and this certificate will not be trusted by SSRS/PBIRS (Reporting Services) unlike other applications (SSMS, UDL). So, this happens because the SSRS/PBIRS behaviour is like that and there is nothing we can do about it. So, make it work as I told earlier either turn off the Force Encryption to “No” or provide a valid certificate to make it work.
Check Certificate for SQL
1. Most the time the problem happens in certificate binding to SQL, and this also happens because of the reasons above, you should always make sure that the certificate is issued against the FQDN of the SQL server or you can also put multiple DNS records to avoid any short of confusion.
2. Also make sure the certificate private key is given sufficient permissions to the SQL Server account. Follow this GUI way:
- Add the SQL Server service account (e.g.,
NT Service\MSSQLSERVERor your domain service account) → Read permission.
- certlm.msc → Personal → Certificates → right-click your cert → All Tasks → Manage Private Keys… and then add the account with full permissions/given local administrator.
If you look at the above image then you can see that SAN, I have two entries, but you can also provide the IP as well! Now here I have put SQL Server NETBIOS and FQDN so that it should work without any problem.
Important
- Don’t mix the SSRS/PBIRS certificate with the SQL, the SSRS/PBIRS portal URL’s can differs from SQL certificate even though the SQL and Reporting services are in same VM. People generally mix both the certificate used for SQL and Reporting Services.
- No matter what certificate you use for SSRS/PBIRS is has nothing do with the SQL Server Certificate and that is the reason you get this error message which we saw while configuring the new/existing database. More about Cert in SSRS/PBIRS.
- If you are not able to bind the certificate then make sure the SQL Server Service Account is part of Local Administrator Group, because you may this see unexpected error while binding the certificate to SQL Server.
- If SQL & SSRS/PBIRS server both are in same machine then you can use the same certificate as well, it is not creating any problem but make sure that the NETBIOS and FQDN of the is present in the SAN of the certificate.
- It is mandatory to have private key for the certificate you are going to use as part of SSRS (SQL Server Reporting Services) / PBIRS (Power BI Report Server) URL’s bindings.
- When you configure the SSRS/PBIRS web service and web portal URL’s then, the certificate will most probably/likely to use Common Name (CN), irrespective of SAN provided in the certificate. So, if the CN name is FQDN then portal URL will be like this: “http://prd-sql01.domain.local/reports“, if you want to use just the NETBIOS then you have to hardcode the URLs in the config file and also make sure to add the NETBIOS URLs in the URL Reservations using NETSH command.
