I am an IT Systems Administrator, not SQL DB person. Let's just get that out of the way!
I have worked on this on and off for the past 3 weeks and spent countless hours traversing these and other forums, I think I have reached the end of the Internet trying to figure out this issue.
History:
For a small office, I had to migrate a Domain Controller and a 3rd party application and SQL database (2014 SP3) from a Windows 2012 R2 server to a modern Windows Server 2022 Standard VM (to decommission Server 2012 server). Not really important so far. We decided to stay with SQL 2014 SP3 on new server for budgetary reasons as they already owned the licenses. SQL/SSRS 2014 are hosted on the same server and are fully patched. Domain Controller is now a separate VM. File Server/SQL server/SSRS are now hosted on same VM (same as before, except DC was also on the old server).
I then learned client was also utilizing SSRS 2014 on old box as well. Moved all DB's over successfully, backed up all encryption keys, restored, Data is tested and moved, reports are accessible from host. Had an issue with the Data Source in SSRS pointing to the old SSRS instance, updated that and fixed most report problems.
Now, what I find out is that the Report links are embedded in the 3rd party application used by client all over the place, many report URLs are in various records. These links are hardcoded to old server hostname (example https://server01/Reports). I did not want to find and replace many URL's/links all over the database, so I decided to rename the old server to "server01-old", created a CNAME record in internal DNS for the new SQL/SSRS server (server02) with a host address of "server01" that points to "server02.domain.local". Create new self-signed SSL cert with "SERVER01" as the COMMON NAME. Install that in SSRS for the HTTPS bindings for Web Service URL and Report Server URL. Configure Service Account, Execution Account, etc. All is successful. Accessing the new SSRS report URL using the "old" hostname URL works. I can enter "https://server01/Reports" and I can pull up the reports, or the ReportServer URL works as well.
Now, this also works from 2 other computers/users (domain environment) on the network. Zero problems with them. However 2 other computers receive http error 401 while navigating to the URLs. I say computers when I explain this, because it is NOT user related. For one of the users that cannot access the URL and receives the HTTP error 401, (https or http, doesn't matter, same error), I can sign in as that user on a working machine as that same user, and then that user CAN pull up the URL. This problem is tied to the computers, NOT the users.
Another strange thing, on the non-working computers, if I access InPrivate browsing (MS Edge), or Incognito (Chrome) and attempt to access the URL's, I am prompted with a login window, entering the Domain Username and Password works, and the website opens up fine. The problem with this is that the embedded report URL's in their application don't work, because for whatever reason, the NTLM authentication isn't passed seamlessly to the SQL/SSRS server. But in a browser, while using InPrivate/Incognito, login prompt appears and I can log in. Please keep in mind the goal is to get the browser to access the SSRS URL without prompt, because if that is working, then the application displays the reports normally - this is the entire goal to fix. The URL being accessible after a login isn't really the endgame, because I can't force the application to "login" to SSRS first, so all their links work in their records (hopefully I am making sense with this). The endgame is for their Windows 10 machine to pass the NTLM credentials to the SSRS server seamlessly so the application records will display their reports normally. Not being able to access the SSRS URL is a symptom of the problem, not the entire issue.
I delved down the whole NTLM/Basic/Kerberos authentication options, and I am not changing this yet (even though I tested RSWindowsNegotiate and RSBasic without success). Reason being is that this all worked perfectly before with Default NTLM authentication with zero modifications to the SSRS authentication options. On the previous server it all just worked fine with default configuration. I realize I made some DNS and SSL modifications to make my solution work, but I can't get over the fact that 2 other Windows 10 computers (all computers at office are Win10) work totally normal. This must be fixable.
I have enabled the HTTP log in SSRS, and for the computers that are receiving the HTTP 401 error, there is NO username listed in the HTTP access log for that attempt. For the computers that do work, the username is listed correctly. This is what got me started down the path of this being a Web browser/authentication issue. I feel like the non-working computers are not passing the NTLM credentials when attempting to access the URL.
Important Note: From a non-functioning computer, and accessing the URL by IP address, via HTTP (not https), browser will prompt for login, and it works. Accessing by IP address of server using HTTPS and I receive a different error, "Not Found HTTP Error 404. The requested resource is not found."
So I started looking for differences between these computers in browser versions, network configuration, patch levels, hosts file, DNS, Local Admin, Internet Options (Intranet/Trusted Sites), DisableLoopbackCheck (server registry, set to 1), anything I could think of and found online. I haven't found any differences between the PCs yet, and any of my fixes have not worked for these 2 computers. I have also checked SSRS and SQL permissions ad nauseum, like I said, I can work with the 2 functioning computers and we are good regardless of user, so this isn't a permission issue on the Site Settings or Folder Settings in SSRS.
At this point I feel I have hit a wall. I was going to setup a new workstation, or Reset Windows on one of their computers, join it to the domain, and see if I can access the URL right away. If it works, I am literally going to have them replace the 2 non-working computers. However I know this is a config issue somewhere in Windows, or with the browser, and it's fixable.
Second thing I may do is purchase a wildcard SSL certificate, and get both the New and Old server names listed as the SAN's on that cert, and then Configure 2 HTTPS URL's in the SSRS Management console. Then I can test with New Server name in the URL and old, to see if there's a difference. I can't add a second URL in SSRS Management Console with the new server name, because it will only allow 1 certificate to be bound to https at a time. Currently using Self-Signed certs and apparently I am not smart enough to create a self-signed cert with multiple SANs listed on it.
I have yet to find a solution to this strange problem, and I can't keep grasping at straws for a solution. I need to find a solution and fast so my customer and I can move on to other projects. Anyone have any ideas on this?
DiableLoopbackCheck Registry modification on SQL/SSRS server.
ALL combinations of Internet Options - Intranet Sites/Trusted Sites -- Adding server address, removing it, logon to server automatically, only in Intranet Zone. No difference. In fact, the working computers are using default Internet Options for all security zones.
Local Admin accounts, non Admins, again this problem is just with the physical computers, not the domain user accounts.
HTTP Access logs in SSRS show NO username when non working computer attempts to access URL, like this:
12/04/2023 20:40:06 192.168.20.133 - 192.168.20.4 47873 server01 GET /Reportserver/ ?%2fMessageView&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true&sysid=M72DEB0E97D71531 401 690 0 1.1 Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36 - - -
The HTTP access logs in SSRS DO show a Username when reports are working from that computer:
12/04/2023 20:45:06 192.168.20.31 DOMAIN\domainuser 192.168.20.4 47873 server01 GET /Reportserver/ ?%2fMessageView&rs:Command=Render&rc:Toolbar=false&rs:ClearSession=true&sysid=M72DEB0E97D71531 200 1316 3484 1.1 Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/100.0.4896.75 Safari/537.36 - - -
Tried Changing to RSBasic Authentication in "rsreportserver.config" and also with RSNegotiate, with or without RSWindowsNTLM.
I don't think the problem is tied to the SSL Certificate, problem occurs via HTTP or HTTPS. Accessing via IP address of SSRS server prompts for login when using http, when using https I receive HTTP Error 404 - NOT Error 401.
Tried adding a second HTTPS URL in SSRS Management Console and binding a separate SSL cert to it that has the CN name of the new server, so I can test access with the new server name in the URL, but SSRS gives an error that only 1 Certificate can be used to bind to separate URLs, I can't use 2 different certs.
Modified Hosts files to point directly to new server using FQDN, new and old hostnames same IP of new server.