January 11, 2018 at 10:10 am
We have SSRS installed on a different server to our SQL server.
If we use SQL logins when running a report from the web front end it works OK but if we try and use Windows Integrated Security we get the below error message.
Couldn't connect
The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I have confirmed with the below SQL script that the logins are made using KERBEROSSELECT sys.dm_exec_connections.session_id AS SPID,
sys.dm_exec_connections.connect_time AS Connect_Time,
DB_NAME(dbid) AS DatabaseName,
loginame AS LoginName,
sys.dm_exec_connections.auth_scheme as Auth_Scheme,
sys.dm_exec_connections.net_transport AS Net_Transport,
sys.dm_exec_connections.protocol_type as Protocol_Type,
sys.dm_exec_connections.client_net_address as Client_Net_Address,
sys.dm_exec_connections.local_net_address as Local_Net_Address,
sys.dm_exec_connections.local_tcp_port as Local_TCP_Port
FROM sys.sysprocesses
Right Outer JOIN sys.dm_exec_connections
ON sys.sysprocesses.spid=sys.dm_exec_connections.session_id
--Order By Auth_Scheme, Net_Transport
ORDER BY Connect_Time
| SPID | Connect_Time | DatabaseName | LoginName | Auth_Scheme | Net_Transport | Protocol_Type | Client_Net_Address | Local_Net_Address | Local_TCP_Port |
| 99 | 52:17.4 | ReportServer | AD\SSRS | KERBEROS | TCP | TSQL | XXX | XXX | 1433 |
| 100 | 52:35.6 | MSCRM_CONFIG | AD\SSRS | KERBEROS | TCP | TSQL | XXX | XXX | 1433 |
| 113 | 52:35.6 | MSCRM_CONFIG | AD\SSRS | KERBEROS | TCP | TSQL | XXX | XXX | 1433 |
What else can I try??
January 11, 2018 at 10:40 am
Sue
January 11, 2018 at 1:29 pm
Sue_H - Thursday, January 11, 2018 10:40 AMTry downloading and checking the kerberos configuration for the Reporting Services using SQL Server Kerberos Configuration Manager -
Microsoft Kerberos Configuration Manager for SQL ServerSue
Thanks for your reply. I have installed and run the Configuration on the server which runs SSRS and it does come up and say under "Status" - "Kerberos not enabled" which i guess answers that question. I have tried to understand how to enabled this (and thought I had done) so can you provide some pointers towards this?
January 11, 2018 at 1:39 pm
stuaz - Thursday, January 11, 2018 1:29 PMSue_H - Thursday, January 11, 2018 10:40 AMTry downloading and checking the kerberos configuration for the Reporting Services using SQL Server Kerberos Configuration Manager -
Microsoft Kerberos Configuration Manager for SQL ServerSue
Thanks for your reply. I have installed and run the Configuration on the server which runs SSRS and it does come up and say under "Status" - "Kerberos not enabled" which i guess answers that question. I have tried to understand how to enabled this (and thought I had done) so can you provide some pointers towards this?
Sure - Here is the article from MS documentation:
Register a Service Principal Name (SPN) for a Report Server
There a bunch of other docs out there if you search on spns. Here is another article with screen shots -
Demystify Kerberos usage: How-to for SQL Server
Sue
January 11, 2018 at 1:56 pm
Sue_H - Thursday, January 11, 2018 1:39 PMstuaz - Thursday, January 11, 2018 1:29 PMSue_H - Thursday, January 11, 2018 10:40 AMTry downloading and checking the kerberos configuration for the Reporting Services using SQL Server Kerberos Configuration Manager -
Microsoft Kerberos Configuration Manager for SQL ServerSue
Thanks for your reply. I have installed and run the Configuration on the server which runs SSRS and it does come up and say under "Status" - "Kerberos not enabled" which i guess answers that question. I have tried to understand how to enabled this (and thought I had done) so can you provide some pointers towards this?
Sure - Here is the article from MS documentation:
Register a Service Principal Name (SPN) for a Report ServerThere a bunch of other docs out there if you search on spns. Here is another article with screen shots -
Demystify Kerberos usage: How-to for SQL ServerSue
Thank you for the help. Am I right in thinking that SQL Server service itself should be running under a Domain user? At the moment we run it under an NT Service Account.
SSRS we run under a domain account though.
Just trying to digest all the info.
January 11, 2018 at 2:16 pm
stuaz - Thursday, January 11, 2018 1:56 PMThank you for the help. Am I right in thinking that SQL Server service itself should be running under a Domain user? At the moment we run it under an NT Service Account.
SSRS we run under a domain account though.Just trying to digest all the info.
Usually yes. But your trying to digest a lot and everything with the accounts is a lot to add on. Your gonna make your head hurt 🙂
Kerberos is simple in a way and complicated in another. Just take your time and go through several docs until you feel more comfortable with it. Not to where you feel like you know it well and could give a presentation on it but to where you get the basics of it and understand the terminology used.
Here are a couple of overviews from more of the windows side and how it all flows with kerberos:
Kerberos for the Busy Admin
Understanding Kerberos Double Hop
The error you got is the classic double hop so that last article is good to understand why you had that logon failure instead of a failure from the account itself.
Like I said, just worry about Kerberos and getting that worked out for now. You'll get there - you're already putting pieces together.
When you got Kerberos down, the documentation about the accounts is pretty good. And it's also another one that can get pretty confusing when you dig into it. Read the documentation - probably takes a few times:
Configure Windows Service Accounts and Permissions
Sue
January 11, 2018 at 2:41 pm
Sue_H - Thursday, January 11, 2018 2:16 PMstuaz - Thursday, January 11, 2018 1:56 PMThank you for the help. Am I right in thinking that SQL Server service itself should be running under a Domain user? At the moment we run it under an NT Service Account.
SSRS we run under a domain account though.Just trying to digest all the info.
Usually yes. But your trying to digest a lot and everything with the accounts is a lot to add on. Your gonna make your head hurt 🙂
Kerberos is simple in a way and complicated in another. Just take your time and go through several docs until you feel more comfortable with it. Not to where you feel like you know it well and could give a presentation on it but to where you get the basics of it and understand the terminology used.
Here are a couple of overviews from more of the windows side and how it all flows with kerberos:
Kerberos for the Busy Admin
Understanding Kerberos Double HopThe error you got is the classic double hop so that last article is good to understand why you had that logon failure instead of a failure from the account itself.
Like I said, just worry about Kerberos and getting that worked out for now. You'll get there - you're already putting pieces together.When you got Kerberos down, the documentation about the accounts is pretty good. And it's also another one that can get pretty confusing when you dig into it. Read the documentation - probably takes a few times:
Configure Windows Service Accounts and PermissionsSue
My head already hurts🙂
I do under Kerberos in principal and I thought I had it setup correctly... but it appears I need more research!
Appreicate the help and links provided though.
January 12, 2018 at 4:18 am
Finally got to the bottom of this.
I was not editing the XML config file correctly. Once I had sorted that, it was working ok.
Thanks again for the help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply