Getting error in Report Server while selecting an option "As User Viewing the report"

[arun_1047]

Hello,

I  have built a report using Direct Query(SQL Server) . While deploying report in Report server, i have used an option "As User Viewing the report" instead of  providing the default credentials and getting the below error message.

Can you please help me to resolve the issue

  Error Message:

"We couldn't run the report because we couldn't connect to its data source. The report or data source might not be configured correctly.

Please try again later or contact support. If you contact support, please provide these details.

We couldn't connect to the Analysis Services server. Make sure you've entered the connection string correctly."

Based on further analysis from log files i got that 

A connection could not be made to the data source with the Name of '64e6db52-d306-49b3-bd72-072fd408d98a'.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.. The exception was raised by the IDbConnection interface.

Power BI report Server Version : OCT Version

[jcollinson2001]

If you want to verify that SPNs are set on an account you can run the following command to list them (I don't think you need any special domain privileges to run this but I'm not 100% sure - sorry!).

setspn –L DOMAIN\ACCOUNT

Swap "DOMAIN\ACCOUNT" for the account that's running your back end service (e.g. SQL Server/SSAS), which the SPNs should be created on.

To create an SPN (for which you will need a certain level of domain privileges) run:

setspn –A SERVICE/SERVER:PORT DOMAIN/ACCOUNT

Here, SERVICE is specific to the service in operation, SERVER:PORT is the server hosting the service, and DOMAIN/ACCOUNT is the account running the service.

Here's an example for SQL Server:

setspn –A MSSQLSvc/MYSERVER:1433 MY_DOMAIN\SQL_ACCOUNT

Here's an example for SSAS:

setspn –A MSOLAPSvc.3/MYSERVER MY_DOMAIN\SQL_ACCOUNT

[jcollinson2001]

Replace the emoticons with colon + P!!! 

[Anonymous]

I am receiving the same error. I do not receive the error with every user, but random times and users:

library!ReportServer_0-19!f54!04/30/2019-08:30:16:: i INFO: Call to GetItemTypeAction(/Operations/Field Operations/CSR WorkBench). User: MFAOIL\Cynthia.St.Clair.

library!ReportServer_0-19!f54!04/30/2019-08:30:16:: i INFO: RenderForNewSession('/Operations/Field Operations/CSR WorkBench')

dataextension!ReportServer_0-19!f54!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId 4e41de21af3b4ec8abece0909938556d

dataextension!ReportServer_0-19!1bf8!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId c63c8c37765f47dc83918873f3f00f62

dataextension!ReportServer_0-19!10b4!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId 9edcc92f55994d299ad2a8b7f2788403

dataextension!ReportServer_0-19!10b4!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId 9edcc92f55994d299ad2a8b7f2788403

processing!ReportServer_0-19!174c!04/30/2019-08:30:16:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Landing'. ---> System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)

   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

[luisrh]

This looks like a 'double hop' issue.   When AD authentication goes beyond two hops (Client browser to web server and then to DB server)  - the auth will not flow beyond the second hop (web server).   That is why you are seeing anonymous.  The only way to resolve this is to set up Kerberos authentication on your PBI RS server.   Is there a specific reason for doing this?  is it because you are protecting data based on user account?    

[Anonymous]

I know our Network Admins installed Kerberos, but we are fairly new to the on prem PBI RS. I do have a question: it seems to be happening with one or two reports only. The reports in question are report builder reports. They have a datasource with several datasets. Within the datasets, the query shows the datasource, but the query calls another datasource also. Example: the datasource is Landing. The query calls tables from Landing and ODS. Could this be posing an "AD" double hop issue? 

[jcollinson2001]

I agree sounds like a double-hop issue.

Have your network team correctly setup SPNs to support Kerberos authentication? They will need to create SPNs  for each data source - e.g. if you have two SQL Server servers, and one SSAS server, you will need to create SPNs on the accounts running the services for each server.

[luisrh]

What jcollison2001 says above is correct...  

[Anonymous]

We are receiving the same error. Here is a capture from our log file:

library!ReportServer_0-19!f54!04/30/2019-08:30:16:: i INFO: Call to GetItemTypeAction(/Operations/Field Operations/CSR WorkBench). User: MFAOIL\Cynthia.St.Clair.

library!ReportServer_0-19!f54!04/30/2019-08:30:16:: i INFO: RenderForNewSession('/Operations/Field Operations/CSR WorkBench')

dataextension!ReportServer_0-19!f54!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId 4e41de21af3b4ec8abece0909938556d

dataextension!ReportServer_0-19!1bf8!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId c63c8c37765f47dc83918873f3f00f62

dataextension!ReportServer_0-19!10b4!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId 9edcc92f55994d299ad2a8b7f2788403

dataextension!ReportServer_0-19!10b4!04/30/2019-08:30:16:: i INFO: Opening SqlConnection with ClientConnectionId 9edcc92f55994d299ad2a8b7f2788403

processing!ReportServer_0-19!174c!04/30/2019-08:30:16:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Landing'. ---> System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)

   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject

[jcollinson2001]

Looks like you haven't set up Kerberos authentication which you must do when authenticating with Windows credentials. Follow the guide here:

https://docs.microsoft.com/en-us/power-bi/report-server/configure-kerberos-powerbi-reports

Workaround: select "Using the following credentials" and enter credentials of an account that has permissions on your data source (either Windows or SQL Server account).

[arun_1047]

Thanks for your reply.

The link which you shared provide details on  "how to configure SPNs for Analysis service".  Can you help me out  how to do the same for the SQL server Database? We are using SQL direct Query .

[dhawal16]

Hi Arun,

can you please let me know if you are able to resolve this? If yes can you please help on how?

[jcollinson2001]

Sure... follow the same steps as with analysis services but swap "MSOLAPSvc.3" with "MSSQLSvc".

Then remember to set delegation on the Report Server account for MSSQLSvc as well.

More details on setting SPNs for SQL Server here: https://technet.microsoft.com/en-gb/library/bb735885.aspx