remote sql server without encryption
1 Topic
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 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.Solved5.2KViews0likes4Comments