Could not connect Report Builder to SQL Server database

[arunbyc]

I am unable to connect to SQL Server through Report Builder (for Paginated Reports). If I use Windows Authentication, I get an error message that "Windows Authentication is not supported in this version of SQL Server". I know I do not use SQL Server Authentication when I use SSMS to connect to the server. But if I go ahead and try this option in Report Builder anyway, as expected, I get an error that "it cannot open the server."

Here is some relevant info: When I connect to SQL Server using Power BI Desktop, I get an error when I select Windows Authentication, but I successfully connect if I select the Microsoft Account option and enter the credentials that I use to log into my Remote Desktop.

(This is where I get the Microsoft Account option in Power BI Desktop:  File->Options DataSource Settings -> edit credentials -> Edit Permissions -> edit Credentials).

When I checked my SSMS connection, the Authentication type is: Microsoft Entra MFA and I successfully connect to SQL Server through SSMS always using the same login credentials that I use for my Remote Desktop.

Given the above, how do I configure my connection to SQL Server through Power BI Report Builder? I do not see a Microsoft Account option in the Report Builder connection to the SQL Server data source.

Thanks

[Anonymous]

Hi, @arunbyc 

I am glad to help you.

According to your description, you want to know why when you connect to SQL Server using Power BI Desktop, you get an error when you select Windows Authentication and you could not connect Report Builder to SQL Server database?

If I understand you correctly, then you can refer to my solution.

First of all, in general, if you want to use Windows Authentication in Power BI Desktop or Report Builder, then in SSMS you should also select Windows Authentication instead of using Microsoft Entra MFA: 

However, in your description, I would like to ask if your computer and SQL Server are located in the same domain? 

If not, do you have a trust relationship established between the two different domains? 

If yes, you can connect to SQL Server from Power BI Desktop using Windows authentication without performing any additional steps compared to using your computer to connect to SQL Server in Power BI Desktop. 

However. if there is no trust relationship between the two domains. You can use the runas /netonly command to accomplish your requirements. 

1. Open command prompt and run the following command, enter the password of domain user when prompted. 

runas /netonly /user: Domain\username "C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe" 

2. Create a saved windows credential for the SQL Server you want to connect to. 

For more details, please check the following similar blogs. 
Connect to SQL Servers in another domain using Windows Authentication 
Pretend You’re On The Domain With Runas /NetOnly 

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

[arunbyc]

Thank you so much for your reply. Some of my answers may not be technical.  My work PC is a Cloud PC. I know that the SQL Server is also on the cloud. I am not sure whether I or any of the tech people whom I can contact have the authority to change the MFA Entra Authentication for SSMA. I will try, but is there any way I can connect  Report Builder to SQL Server if this authentication cannot be changed? Depending on what you say, I can ask the right questions/make requests and will let you know.