Hi! Welcome to my SQL Server Reporting Service (SSRS) double hop issue, in this tutorial
I'm going to show you how to fix the error or problems and a basic knowledge about that and
how to configure it and how to troubleshoot the problem. Okay, things should be simple straight
forward everyone can follow. So without further ado let's open up my browser go to a Reporting Service
as we normally do and then first thing you've noticed that you have to enter username and password
and second thing will be the page like the configuration page may take some time to load up
which is because the SPN has not set yet so once we set that you will see this
page load up much quicker and second thing is when you load up a report and you will get this error
At first you would think probably have some connection issue. It is a connection issue
so let's set the SPN to solve the problem the first log on problem and display problem
let's go to the page again as you can see now the page is loaded up a lot quicker no need to enter
username credential now but the report, let's look at the report, that's still coming up with error so
that's another bit of the thing we need to solve that's the delegation (Kerberos Delegation in specific), we need to delegate the
service account and allow that to pass your credential to the other server
in order to get the report. So now if you go to the page again and try to load up a report and uh yep
still getting error what's going on and let's look at the kerberos ticket you don't have the special
ticket to um grant you permission to other server so let's open up Edge. I'm sorry IE uh there's
actually a replacement anyway. So let's look at the report again. ah, still didn't work so in this case
I think you look at the Kerberos ticket again we need to purge it and before we go any further
we try to look at the report again and as you can see it's not working again
so it's not working on either browser if you try again nothing really happened
so what's going on here because as you can see the kerberos ticket already
there but still not loading the issue is actually just the the browser just caged the credential
so let's try again now it's working perfectly fine no issues and then i will show you another setting
which in the browser (sorry, only mentioned in PowerPoint) you have to configure before you can pass your authentication through
so that's another important part apart from setting that SPN and delegation
well as you can see the delegation tickets already granted
so that's the first important thing on the DC and let's cover some theory first for this
Single Sing-On stuff. It's actually boiled down to the Kerberos Negotiation or Authentication
so in the lab we have this setup basically we have three servers one is the
client server which is the DC the other two other SQL server and the middle one with
SSRS installed and the one on the right is the normal SQL server with a database
and we use the gMSA account for the reporting service which is pretty
easy to maintain and highly recommended for you if you want to configure a sql service including your
SQL server service as well but in this demo. I just configured for SQL reporting service which is
more than enough for this demo and so next page we want to see is the the Kerberos Authentication
so as I mentioned before, this Double Hop issue is boiled down to Kerberos authentications
so the three parts you need to look into , so just in a Nutshell there's three areas you need
to look. So the first one SPN and Service account delegation which we have already seen earlier in
the demo which is actually configured on the DC. Domain Controller and you only need to be a
Domain Administrator in order to configure that and the second part is SSRS authentication
type which is only available on the SSRS Reporting Server. I will show you
the configuration path and the content in it which will cover that later and the third
thing is the application and script application including browsers, script including your python
PowerShell etc, and in terms of Kerberos authentication I won't go into detail now you
can have a look at David's video and he actually give you a lot more detail on how it works
behind the scenes and after that we should have everything ready for Kerberos authentication
We'll cover the middleman (sorry again, I only mentioned it the PowerPoint) later on as well so
in terms of verification, in a browser you need to have your Domain Name in a Local Intranet instead
of Trusted Sites which is very important if you have that in the wrong place it will not work
and you can publish that via good policy or just edit that registry key that will do the job
for command or script PowerShell is the most advanced and trouble shooting tools so out
of the box you don't have to configure anything load any module in order to communicate with the
Kerberos so you can just issue that command you can get a report straight away from the server
but other scripting tools or commands like cURL or Python they have some limitations as I found
so as they don't have any modules (add-ons) that work with the Single Sign On, Double Hop issue
for pythons if you know anything for that solution feel free to comment down below and
let me know. Yeah, that would be good I share around with people who may have the same issue
so thanks so much for this in terms of troubleshooting uh there's a
few things you need to keep in mind one is your firewall. Because your SSRS Server need to
send a Kerberos request ticket to your from your SSRS to the DC so that's a capture from
your WireShark, so as you can see is actually using the port. Yeah so that's why you are seeing
that capture there and on the SSRS server sites you have the logs you can look for and look for
errors like anonymous authentication error that's probably you can find it in that log
on the also SSRS. I'm sorry on the Client machine or the SSRS server you under the system log
you can see this error which mean mainly just a Kerberos Authentication error. So in the config file
on this SSRS side. So that's the configure file you should look for the
mainly two things you need to look for are the authentication type and the username and user
SID. I will show you that a bit later and sorry about that, that's also you can use two
commands to troubleshoot your Kerberos authentication, one is klist you can list your
current coverage ticket and the important bit is the delegation bit in the ticket so if you have
the delegation bit, that's the delegation ticket so you can use that to pass your authentication
through to other server to get authenticated and then resolves the problem. The other thing is
the coverage patch it's always a good idea to purge your Kerberos tickets before you
if you restarted or made any changes, restarting the SQL reporting server for example you have to purge
your ticket and retry again. okay without further ado let's go back to the lab and have a look at
the server we have set up earlier. so according to the Powerpoint presentation. This is the
first server client server is the middleman with SQL and SSRS installed the last server is
just a SQL Server with one database. so let's go back to the middle server and have a look at the
the configuration so for example the gMSA account is actually being used for this
reporting server service and there's a there's an important configuration on the reporting server
the first one being the config file which is located there and two portions you need to
taking care of. The first one is reservation the other one is authentication
under the reservation section. There are two sections, one is for the API
section so reporting server which associated with that bit in the configuration manager
so the other bit is the report that's the second bit which where you configured that
that's GUI place you where you configure reports and permissions and Data Sources. etc so
that's the important places you need to look at the other thing is your service account
that's one way you configured it however when you save your file your service account may change
for example your SID can be changed, so service account name can be changed. so make sure you
open the file, make sure they are the same as you saved it and restart your reporting service
so that's important and also the authentication type that's the authentication
type you need for Kerberos delegation or double hop to work which is the other two
just you don't need them, they're optional or you can include them as well so it doesn't really
affect but as long as you have the first one on the top, the one, so on the third server
as you can see we only have one database and on this middle server we have two reporting databases
so that's pretty much it for the middle server on the
client server as you can see from the early demo that's what we have already configured basically
just set SPN for that service account against the server which is the SSRS server
and we can see once we configure that you should see
the following, takes a bit longer but that's what you finally see that service
account is actually bound to that server okay and that's already covered and the
delegation. I'm sorry, let's do a quick verification for the PowerShell script so
use PowerShell script you can do double hop and get the report from the report reporting server
without any issue as you can see there the pdf already generated and the report is looks
exactly the same as they have seen earlier, back to the delegation that's the initial delegation
before you ran that command so after you run it actually you have to enable that
feature otherwise you won't be able to see the Attribute Editor tab. So let's go down
after we ran the command earlier, so now you can see the it has changed to
81000 that's you know the delegation already for that service can already enable okay so I
think that's pretty much it i need to cover in this video if you like it please feel free to
share the video and if you appreciate my time of making this video, please give me a like
button and yeah so hopefully we'll see you in an other video and thanks for watching , alright bye