Forum Discussion

saabbir's avatar
saabbir
Frequent Visitor
5 years ago
Solved

HTTPS PowerBI Report Server to HTTP SQL Server data source - error 401

I have just experienced a bizarre problem today. My PowerBI report server (on prem) was previously on a self cert SSL before. I have reports that runs on Imported mode from a remote SQL Server which ...
basic authentication
datasource error
HTP 401
HTTPS
PowerBI Report Server
Remote SQL Server without encryption
  • saabbir's avatar
    saabbir
    5 years ago

    Hi all,

     

    Thanks for your support and time. I have now setup another VM with exactly the same config, except the SQL server is TLS enabled. I deliberately left "Force Encryption = False". Everything is working like a charm.

    I have tested the following scenarios:

     

    1. Non-encrypted SQL + HTTP RS = Works
    2. Non-encrypted SQL + HTTPS RS = Doesn't work
    3. Encrypted SQL + HTTP RS = Didn't test
    4. Encrypted SQL + HTTPS RS = Works

     

    Few things to remember: Don't forget to add the SPNs, using FQDN is important and make sure user has at least datareader permission on SQL DBs.

     

    Hope this helps.

     

    Environment: Windows Server 2019 Enterprise, SQL Server 2017 Enterprise Core, PowerBI Report Server May 2021.

     

    Thanks

    Sabbir

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi saabbir ,

 

Please try to change the service account from "Virtual Service Account" to "Domain account". And for kerberos, you need to register spn under the domain account.

 

To schedule refresh a report, the execution account must have "system user" permissions. Please make sure the powerbi server cog->settings->site settings->security is giving access to that user

 

And please also check if the user you used to connected to sql database have permission to create agent job. 

 

 

Here is some simliar post:  https://community.powerbi.com/t5/Report-Server/Power-Bi-report-server-was-unable-to-create-a-schedul...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

saabbir's avatar
saabbir
Frequent Visitor
5 years ago

Hi v-deddai1-msft ,

 

Sorry for my delayed response and thanks a lot for taking the time to find a solution to my problem. I really appreciate.

 

In our environment, everything is running using a Domain admin account (you can guess, it has more power than it needs). Kerberos SPNs are registered too. HTTPS was missing before in SPN list, I have added that already before posting here. Didn't work.

 

The same domain admin account has System Administrator and System User permission in PBIRS site settings.

 

The service account is a sysadmin in all the SQL servers, so it has permission to create agent jobs. The basic account (SQL Server login) that is used to connect to the datasource only has data read permission. The reason I think giving this basic user permission to create agent job wont work is, the exact same settings work when I enable http and try to create the schedule jobs via http://ServerName/reports (instead of https://ServerName/reports). My guess is when PBIRS tries to connect to the datasource from https url, it tries to use encrypted connection even though in the PBIX use encryption is unchecked (from PBIX data refresh works 100% and without errors).

 

At some point I will test my theory by enabling encryption in one of the SQL servers and try to create a scheduled refresh from https PBIRS to https SQL server. I will update the forum once I do that.

 

Thanks a lot again and appreciate your help.

 

Regards

Sabbir