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

[saabbir]

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 doesnt accept encrypted connection. All within the same domain. I have upgraded the SSL to a SAN SSL the other day on the report server. All the data source connection remain the same (non SSL remote SQL Server). BTW I am running May 2021 report server. This morning one of the colleagues reported that the data wasnt refreshed. Upon checking I saw the schedule refresh failed. And I tried to add refresh schedule on another Import mode PBIX report. Got a big fat "Something went wrong". Data source have basic credentials and on the data source page test connection is successful with the basic SQL login credentials.But whenever I try to add a refresh schedule i get the same error. Went on checking log and verbose. Got "Error 401". Detail error, if anyone is interested:

2021-07-07 00:47:55.7026|ERROR|63|OData exception occurred: System.Net.WebException: The request failed with HTTP status 401: Unauthorized. at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2010.ReportingService2010.CreateCacheRefreshPlan(String ItemPath, String Description, String EventType, String MatchData, ParameterValue[] Parameters) at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.<>c__DisplayClass136_0.b__0() at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.SoapMethodWrapper1.ExecuteMethod(Boolean setConnectionProtocol) at Microsoft.SqlServer.ReportingServices2010.RSConnection2010.CreateCacheRefreshPlan(String itemPath, String description, String eventType, String matchData, ParameterValue[] parameters) at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapRS2010Proxy.<>c__DisplayClass19_0.<CreateCacheRefreshPlan>b__0() at Microsoft.ReportingServices.Portal.Services.SoapProxy.SoapAuthenticationHelper.ExecuteWithWindowsAuth[TReturn](SoapHttpClientProtocol soapClient, IPrincipal userPrincipal, Func1 func) at Microsoft.ReportingServices.Portal.Repositories.SubscriptionService.CreateCacheRefreshPlan(IPrincipal userPrincipal, CacheRefreshPlan cacherefreshPlan) at Microsoft.ReportingServices.Portal.ODataWebApi.V2.Controllers.CacheRefreshPlansController.AddEntity(CacheRefreshPlan entity) at Microsoft.ReportingServices.Portal.ODataWebApi.Controllers.Reflection.EntitySetReflectionODataController1.Post(ODataPath oDataPath, T value) at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_2.<GetExecutor>b__2(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken) --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ApiControllerActionInvoker.d__1.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ActionFilterResult.d__5.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Web.Http.Controllers.ExceptionFilterResult.d__6.MoveNext().| RequestID = s_9b953a1c-bc48-41f3-ad5b-12e903990663

By now I may have read 95% of the forums/blogs/solutions/documentations without luck.

So I thought, lets try to do it over HTTP (port 80) instead of HTTPS (port 443). And it worked like a charm. BTW, by default HTTP binding is removed, only HTTPS is allowed. I have checked "Encrypted connection is disabled" in the datasource settings in PowerBI desktop and refresh+redeployed. Same issue over HTTPS but works fine on HTTP.

Does anyone have a permanent solution/fix to this problem? Because running report server on HTTP is not allowed. I have tried restarting both instances, changing reg values mentioned on the internet, nothing worked. Additionally we have kerberos enabled, and our windows authentication works perfeclty fine for remote SQL Server datasources.

Thank you all for reading and using your brain space. Have a good night.

Note: the forum removed some parts of the error message. It doesnt let me submit them.

[saabbir · Accepted solution]

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]

Hi @saabbir ,

Please also refer to https://docs.microsoft.com/en-us/sql/reporting-services/security/configure-ssl-connections-on-a-nati...

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

Best Regards,

Dedmon Dai

[v-deddai1-msft]

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]

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