How to connect the Powerbi Report Server to sqlserver with "force encryption" enabled

Sun, Dong Wei (IN IT CN RS) 0

Dear Support:

we are trying to setup the PowerBI report server on our local environment, and according to our security policy, we are forced to activate the "force encryption" in SQL server, which makes us not able to connect to database successfully with below error message:

Could not connect to server: A connection was successfully established with the server, but then an error occurred during the login process. provider: SSL provider, error:0 - The target principal name is incorrect.

and here I can confirm I can use the same connection user (domain user) to log on the DB via SSMS successfully if I check the option "trust server certficate"; if I don't check the option, I will get the same error message.

so seems like we need to maintain the "trust server certificate" somewhere in the PowerBI Report Server, but no document found so far.

thanks.

2 answers

Erland Sommarskog 114.6K Reputation points MVP

2024-12-26T11:22:23.32+00:00

I don't use Power BI Report Server myself, but when googling for possible solutions, I found this blog post from Adam Saxton at Microsoft who knows this stuff well: https://powerbi.microsoft.com/en-my/blog/ssl-security-error-with-data-source/

It's a different solution than Trust Server Certificate, but it also a better solution, because with Trust Server Certificate you are open for man-in-the-middle attacks.
Please sign in to rate this answer.
Sun, Dong Wei (IN IT CN RS) 0 Reputation points

2024-12-26T11:26:05.21+00:00

hi, Erland:

thanks for the info, while seems like that's for the PowerBI service not PowerBI report server.

Erland Sommarskog 114.6K Reputation points MVP

2024-12-26T11:33:29.0933333+00:00

Hm, but the idea is the same. The certificate for SQL Server should be in the Trusted Certificate store on the machine you want to connect from. Or a root certificate that can verify the that the certificated presented to you is authentic.

Sun, Dong Wei (IN IT CN RS) 0 Reputation points

2024-12-26T12:07:04.51+00:00

We are just in the configuration phase, the database and reporting server are on the same machine, so I’m confusing which certificate using by power bi reporting server to connect to the database when it’s trying to create the DB. Btw , we applied the HTTPS certificate and binding it in the report server configuration management.

Erland Sommarskog 114.6K Reputation points MVP

2024-12-26T12:44:52.9633333+00:00

If you are running a local machine, the risk for man-in-the-middle attacks is about nil, and encryption is a little bit over the top. Then again, since you may change the configuration later, it's better to have it right from the start.

The certificate for SQL Server is not the same as for the web server. You configure it from SSCM by right-clicking the Protocols node in the left panel, as seen below.

If you have a proper certificate installed, all you need is to put it in the trusted certificate store. But if its blank, it is a self-signed certificate from SQL Server which really won't do.

Sun, Dong Wei (IN IT CN RS) 0 Reputation points

2024-12-26T12:51:19.5066667+00:00

Although I totally agree with you that the risk is very low due to the intranet and local machine, due to the security requirement, I have to follow.

and we do have the certificate as you mentioned , while when you say to put into the trusted certificate store, where should we put? Computer certificate or personal ? I would assume prefer computer which I also tried today, but I can try again tomorrow.

Erland Sommarskog 114.6K Reputation points MVP

2024-12-26T12:57:39.6633333+00:00

I think that should be clear from Adam's blog post. Else have a look at https://codekabinett.com/rdumps.php?Lang=2&targetDoc=create-install-ssl-tls-certificate-sql-server which I have followed myself when setting this up in my home lab. (That is, I don't remember on the top of my head.)
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ZoeHui-MSFT 38,956 Reputation points

2024-12-27T02:07:22.86+00:00

Hi @Sun, Dong Wei (IN IT CN RS),

Go to File->Options and Settings -> Data Source Settings in Power BI Desktop, select your SQL data source and tick "Encrypt connections".

If this does not work, you may try to connect to sql server data via OLEDB.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
Sun, Dong Wei (IN IT CN RS) 0 Reputation points

2024-12-27T02:12:17.7733333+00:00

hi, zoehui:

our issue is on the report server, not on the powerbi desktop.

ZoeHui-MSFT 38,956 Reputation points

2024-12-27T06:16:46.02+00:00

Hi @Sun, Dong Wei (IN IT CN RS),

You mean when you open the report on the Power BI report server, and then the error thrown?

Try adding TrustServerCertificate=True; to your connection string.

Sun, Dong Wei (IN IT CN RS) 0 Reputation points

2024-12-27T06:19:36.4766667+00:00

nope, we are just at the step to setup the report server. and in the "report server configuration", there is a step to connect the sql server instance to create the DB, in which we force the encryption with certificate, and stuck there.

always show the error message.

we are not yet at the step to create and upload the report now.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to connect the Powerbi Report Server to sqlserver with "force encryption" enabled

2 answers

Your answer