Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
Replace the emoticons with colon + P!!!
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)
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?
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?
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.
What jcollison2001 says above is correct...
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
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).
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 .
Hi Arun,
can you please let me know if you are able to resolve this? If yes can you please help on how?
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
3 | |
2 | |
1 | |
1 | |
1 |