topic Re: Error while connecting to dtaa source in Report Server

Error while connecting to dtaa source

vijaycp2 — Tue, 14 Jan 2025 21:38:42 GMT

We are getting following error message. The connection from PowerBI desktop works fine however we can't connect to SQL server database from Power BI

We have defined data source connection using settings - > manage connections and gateways

Re: Error while connecting to dtaa source

lbendlin — Tue, 14 Jan 2025 23:21:40 GMT

You need a gateway to access that on-prem database.

Re: Error while connecting to dtaa source

vijaycp2 — Wed, 15 Jan 2025 00:06:56 GMT

We do have gateway which connects to on-prem database.

Re: Error while connecting to dtaa source

lbendlin — Wed, 15 Jan 2025 01:07:05 GMT

Please show the connections section.

Re: Error while connecting to dtaa source

vijaycp2 — Wed, 15 Jan 2025 15:55:10 GMT

Re: Error while connecting to dtaa source

vijaycp2 — Wed, 15 Jan 2025 18:28:31 GMT

Look at the error message Do Sql server defines firewall rules for different executables? I can run queries from SSMS.

Error message

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: '10.110.36.69', Database: 'Viewpoint', User: 'vijay.sql'. Check the connection configuration is correct, and make sure the SQL Database firewall allows the Data Factory runtime to access. 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.), SqlErrorNumber=-2146893022,Class=20,State=0, The target principal name is incorrect Activity ID: 532c3901-2a0c-42c5-a676-ff236b3f5c2d

Re: Error while connecting to dtaa source

Anonymous — Mon, 20 Jan 2025 02:27:52 GMT

Hi,lbendlin,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello, vijaycp2 . I am glad to help you.
According to the screenshot of the error message you subsequently provided, the system suggests that the connection was successful but the name of the target subject is incorrect

Errors with incorrect target subject names are usually related to SSL certificate configuration.
I hope my suggestions below are helpful

1. Check the SSL certificate: make sure the subject name of the SSL certificate used by SQL Server matches the server name. If a self-signed certificate is used, make sure the client machine trusts the certificate.

URL:
Solved: provider: SSL provider, error 0:The certificate ch... - Microsoft Fabric Community
SSL Security Error with Data Source | Microsoft Power BI Blog | Microsoft Power BI
2. Try disabling SSL encryption: If SSL encryption is not required, you can disable SSL encryption by adding Encrypt=False to the connection string.

DataSource.Error: Microsoft SQL - Power BI - Microsoft Q&A

I can not rule out the possibility that your database “Viewpoint” is inaccessible to the account used for gateway authentication.
I noticed that the authentication method selected in your gateway connection is Basic mode.
So you are using a SQL Server login account to access the database.
Please check if your SQL Server account has access rights to Database: 'Viewpoint', and if not, please give it access rights.
You can try to create a new test database on the same computer IP (same SSMS) and give access rights to the database to the SQL Server login account you are using for the gateway connection, create a pbix report with the test database and configure the Service gateway connection to check if the same problem exists.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Error while connecting to dtaa source

vijaycp2 — Wed, 05 Feb 2025 16:54:45 GMT

Worked with Microsoft technical support team and modified data gateway sqlsrvr.dll.config file. After dll.config changes started working.

Re: Error while connecting to dtaa source

lbendlin — Wed, 05 Feb 2025 23:20:31 GMT

what did you change in that file?