May 4, 2017 at 3:06 pm
I have a fairly fresh install of SSRS 2016 with a few canned reports from a 3rd party tool populated into it along with migration of reports from our live system over to the test system. This all went smoothly and I am quite happy with it.
The next step was migrating the data sources. Our old setup was to use the data sources with a hard-coded account. While this works, it also means if we screw up permissions on the SSRS web page, then users may be able to see things they shouldn't be able to see. That is, we could accidentally (or through some nested groups or something) end up having a user see a report but that specific user doesn't have the SQL permissions to see the data. Since the data source is hard coded, it would allow them to see the report.
We wanted to have the extra level of security on this and were looking at changing our data sources over from "Using the following credentials" to "As the user viewing the report". If I change this and click on "Test connection" while RDP'ed into the server hosting SSRS, I get a nice green checkmark and things look good.
If I do this from my local machine, I get a red X and it complains:
Any thoughts on this? It works fine (as do reports) if I run them from the machine hosting the SSRS instance, but if I run it from my local machine (or any other machine) I get errors.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 4, 2017 at 3:47 pm
Once I tell you, you'll know. And hit your forehead.
Double-hop, Kerberos.
After doing the SPNs and delegation, check the RSReportServer.config file. Authentication type used to need to be set to <RSWindowsNegotiate/>. I would guess the same is true for 2016 but haven't played around with that enough to know for sure. It just allows SSRS to use Kerberos.
Sue
May 4, 2017 at 4:12 pm
I had read about all the double hop stuff, but not sure that is my case. I could be compeletely wrong on this mind you.
The SSRS web instance hosts the SQL instance. Lets call that box SSRS. So SSRS has SQL 2016 instance and SSRS running on it. It has mixed mode authentication turned on (so we can access it via sa or any odd bits of software that need SQL authentication). The SSRS Service account is set to be a windows account.
We have a second server hosting the SQL instance with the data I want to report on. Lets call that SQLDATA. It is a SQL 2008 instance and has the service account the same as the SSRS box and has that service account with sysadmin permissions on the SQL Instance.
If I RDP into SSRS and navigate to http:\\SSRS\reports and try to add a new data source using "As the user viewing the report", everything works great. Do the same thing from any other machine and it doesn't work.
I tried changing that value you suggested and I hit a brand new snag. The previous value of that was "<RSWindowsNTLM/>". After changing it to the value you suggested, if I browse to http://SSRS/reports on the server SSRS, works just like before (everything is happy). I start up IE on my local desktop and try and now I get a login box popping up (which wasn't there before). I type in my credentials and I get a "HTTP 400 Bad Request" error.
It is progress for sure, I am just not sure if it is forward progress or if I'm going backwards.
EDIT - Forgot to mention, this is all operating within a single domain and there is nothing fancy going on. No VPN or anything and the SSRS server is hosted on a VM that has its physical hardware in the same room as the AD server.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 4, 2017 at 4:47 pm
Naw...you're going forwards. You have the classic double-hop error. Client to Report Server to Data source can definitely lead to double hop. When you are on the Report Server, there is only one hop. Anonymous Login, credentials weren't passed.
One of the reasons for the prompts for credentials can be due to the browser being used or the security zone settings when using IE. Or you might just need to run as Administrator for now. That bad request error is usually bad URL or the IP addresses messed up in the configuration.
The combination of the two, usually something weird. Did you run IE as administrator? And did you bounce the SSRS box after making the changes.
Also check the Reporting Services log. It usually (not always) will record something if it sees a configuration error. Might just be a warning, not necessarily an error.
You can set the config file to use both NTLM and Kerberos but using Negotiate it just falls back to NTLM if Kerberos can't be used and tends to have few issues.
Walk though this article - it's older but still pretty good. The links at the end have some good related info on the browsers, authentication and there is also a whitepaper for Kerberos and SSRS that is pretty good:
Enabling Kerberos Authentication for Reporting Services
Sue
May 5, 2017 at 8:18 am
I'm glad I'm making progress then. Odd about the double hop error though.
So, I am using IE and the security zone "Local intranet" is set to LOW. If I have it set to use NTLM instead of Negotiate, then the URL works. If I set it to Negotiate, then I get the 400 error using the same URL.
I just tried running IE as admin and I get the same error. And yep, I restarted SSRS and all SQL services just to be on the safe side. The SPN appears to be set correctly as when I run "setspn -L <service account>" (where <service account> is the name of the service account which is an AD account), I get back "http/SSRS.xxx.xxx.xxx:80" on the top line.
The only things in the log that appear interesting are:
InitializeExternalUserGroupSid failed. Implied authentication will be disabled
Implied authentication manager initialization failed. Implied authentication will be disabled
and then 2 failed to register a SPN notices so I registered both of them manually and restarted the SQL instance. Still getting the 400 error though. Going through the link you provided now.
Also realized we must be in different time zones. Either that or you were up all night trying to help me (11:47 PM was your last post).
EDIT - was reading through the article you posted and when I got to the "you need to allow delegation on the service account and computer", I asked the IT guys to change that and they told me no. So, going back to using the old method (NTLM) and setting the credentials to "Using the following credentials" and hard code it in. Then we will just apply security on the folders and individual reports.
Thanks for the link though. That is going into my bookmarks for future reference. It is a very good read (even if I didn't get it set up).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2017 at 8:24 am
You also need to set the service account or computer accounts (which ever the SPNs are registered against) delegation rights within active directory for it to delegate the Kerberos tokens around if you have not already done so.
May 5, 2017 at 10:17 am
bmg002 - Friday, May 5, 2017 8:18 AMI'm glad I'm making progress then. Odd about the double hop error though.
So, I am using IE and the security zone "Local intranet" is set to LOW. If I have it set to use NTLM instead of Negotiate, then the URL works. If I set it to Negotiate, then I get the 400 error using the same URL.
I just tried running IE as admin and I get the same error. And yep, I restarted SSRS and all SQL services just to be on the safe side. The SPN appears to be set correctly as when I run "setspn -L <service account>" (where <service account> is the name of the service account which is an AD account), I get back "http/SSRS.xxx.xxx.xxx:80" on the top line.
The only things in the log that appear interesting are:
InitializeExternalUserGroupSid failed. Implied authentication will be disabled
Implied authentication manager initialization failed. Implied authentication will be disabledand then 2 failed to register a SPN notices so I registered both of them manually and restarted the SQL instance. Still getting the 400 error though. Going through the link you provided now.
Also realized we must be in different time zones. Either that or you were up all night trying to help me (11:47 PM was your last post).EDIT - was reading through the article you posted and when I got to the "you need to allow delegation on the service account and computer", I asked the IT guys to change that and they told me no. So, going back to using the old method (NTLM) and setting the credentials to "Using the following credentials" and hard code it in. Then we will just apply security on the folders and individual reports.
Thanks for the link though. That is going into my bookmarks for future reference. It is a very good read (even if I didn't get it set up).
Wow...that is not too bright by the IT guys - never ran into that in any place or environment. That's some of what's been screwing it up. So you can have things less secure now. Just a guess but I suspect things are setup well to support Kerberos in the infrastructure. Nonetheless, you have my condolences 🙂
That article is the right way to do it and does cover most of the pieces that burn people setting up SSRS and Kerberos.
Do you still have the 400 Error?
I had thought we were in close time zones - you end your sentences just like a Canadian. Could be a bad guess on my part but that was my guess. I'm in the states so we wouldn't be far off if you are from the North. Do the times look right on your own posts? You may have the UTC time zone - I had that for awhile. Go to settings on your account and check the time zone you have set.
Sue
May 5, 2017 at 10:36 am
Sue_H - Friday, May 5, 2017 10:17 AMWow...that is not too bright by the IT guys - never ran into that in any place or environment. That's some of what's been screwing it up. So you can have things less secure now. Just a guess but I suspect things are setup well to support Kerberos in the infrastructure. Nonetheless, you have my condolences 🙂
That article is the right way to do it and does cover most of the pieces that burn people setting up SSRS and Kerberos.
Do you still have the 400 Error?I had thought we were in close time zones - you end your sentences just like a Canadian. Could be a bad guess on my part but that was my guess. I'm in the states so we wouldn't be far off if you are from the North. Do the times look right on your own posts? You may have the UTC time zone - I had that for awhile. Go to settings on your account and check the time zone you have set.
Sue
I do not get the 400 error if I use NTLM instead of Kerberos. So that fixed the problem on my end. Just means I need to set it to use "Using the following credentials" instead fo "As the user viewing the report". But can still get a little better security by clicking the "Log in using the credentials, but then try to impersonate the user viewing the report" which appears to work in some cases.
And I didn't realize I sounded like a Canadian, but you are a good guesser; I am a Canadian. So our time zones are not that far off then. I'm in the CST/MDT timezone (ie no daylight savings which is pretty awesome in my opinion). I was just surprised to see a reply at 11:47 PM. I'm very rarely up that late. Brain is usually a little melty after a day of work so I tend to relax with TV and/or video games and/or a movie. But now I'm getting a bit off topic, eh?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2017 at 11:00 am
bmg002 - Friday, May 5, 2017 10:36 AMSue_H - Friday, May 5, 2017 10:17 AMWow...that is not too bright by the IT guys - never ran into that in any place or environment. That's some of what's been screwing it up. So you can have things less secure now. Just a guess but I suspect things are setup well to support Kerberos in the infrastructure. Nonetheless, you have my condolences 🙂
That article is the right way to do it and does cover most of the pieces that burn people setting up SSRS and Kerberos.
Do you still have the 400 Error?I had thought we were in close time zones - you end your sentences just like a Canadian. Could be a bad guess on my part but that was my guess. I'm in the states so we wouldn't be far off if you are from the North. Do the times look right on your own posts? You may have the UTC time zone - I had that for awhile. Go to settings on your account and check the time zone you have set.
Sue
I do not get the 400 error if I use NTLM instead of Kerberos. So that fixed the problem on my end. Just means I need to set it to use "Using the following credentials" instead fo "As the user viewing the report". But can still get a little better security by clicking the "Log in using the credentials, but then try to impersonate the user viewing the report" which appears to work in some cases.
And I didn't realize I sounded like a Canadian, but you are a good guesser; I am a Canadian. So our time zones are not that far off then. I'm in the CST/MDT timezone (ie no daylight savings which is pretty awesome in my opinion). I was just surprised to see a reply at 11:47 PM. I'm very rarely up that late. Brain is usually a little melty after a day of work so I tend to relax with TV and/or video games and/or a movie. But now I'm getting a bit off topic, eh?
Login using the credentials - If things work sometimes and not others, it's usually not the configurations. Can be but usually not with SSRS. I think that does go back to what I mentioned earlier with the environment and support of Kerberos.
Doubt this would be an issue but one odd thing to check that's not really mentioned anywhere is the local security policy. It should get set right when using Configuration Manager but on the Report Server, just do start, run, secpol.msc. Under local policies, User rights assignments, check Impersonate a client after authentication. Make sure the Reporting Services account is in there. Should be but worth a check.
I'm in MDT but we have DST which I don't care for. So no, my reply was at 3:47 pm. I would be fast asleep by 11:47 - I don't think i get on the computer in my sleep. That's just for on-call. And off topic is normal on Fridays I think. Just seems to happen a lot on that day.
Sue
May 5, 2017 at 11:09 am
Sue_H - Friday, May 5, 2017 11:00 AMbmg002 - Friday, May 5, 2017 10:36 AMSue_H - Friday, May 5, 2017 10:17 AMWow...that is not too bright by the IT guys - never ran into that in any place or environment. That's some of what's been screwing it up. So you can have things less secure now. Just a guess but I suspect things are setup well to support Kerberos in the infrastructure. Nonetheless, you have my condolences 🙂
That article is the right way to do it and does cover most of the pieces that burn people setting up SSRS and Kerberos.
Do you still have the 400 Error?I had thought we were in close time zones - you end your sentences just like a Canadian. Could be a bad guess on my part but that was my guess. I'm in the states so we wouldn't be far off if you are from the North. Do the times look right on your own posts? You may have the UTC time zone - I had that for awhile. Go to settings on your account and check the time zone you have set.
Sue
I do not get the 400 error if I use NTLM instead of Kerberos. So that fixed the problem on my end. Just means I need to set it to use "Using the following credentials" instead fo "As the user viewing the report". But can still get a little better security by clicking the "Log in using the credentials, but then try to impersonate the user viewing the report" which appears to work in some cases.
And I didn't realize I sounded like a Canadian, but you are a good guesser; I am a Canadian. So our time zones are not that far off then. I'm in the CST/MDT timezone (ie no daylight savings which is pretty awesome in my opinion). I was just surprised to see a reply at 11:47 PM. I'm very rarely up that late. Brain is usually a little melty after a day of work so I tend to relax with TV and/or video games and/or a movie. But now I'm getting a bit off topic, eh?
Login using the credentials - If things work sometimes and not others, it's usually not the configurations. Can be but usually not with SSRS. I think that does go back to what I mentioned earlier with the environment and support of Kerberos.
Doubt this would be an issue but one odd thing to check that's not really mentioned anywhere is the local security policy. It should get set right when using Configuration Manager but on the Report Server, just do start, run, secpol.msc. Under local policies, User rights assignments, check Impersonate a client after authentication. Make sure the Reporting Services account is in there. Should be but worth a check.
I'm in MDT but we have DST which I don't care for. So no, my reply was at 3:47 pm. I would be fast asleep by 11:47 - I don't think i get on the computer in my sleep. That's just for on-call. And off topic is normal on Fridays I think. Just seems to happen a lot on that day.Sue
Thanks for the heads up on that secpol thing. Checking that now. I suppose that is part of the fun of inheriting systems is that a lot of the initial config was set up for me. This particular SSRS instance I set up myself on a tiny test box so if it goes dark, that's ok too.
And since we are currently in the same time zone, I wonder why it thought it was sent in so much later? I think I have my timezone set properly on here as I see your latest reply was 11:00 AM, and mine (prior to this one) was 10:36 AM. This one is going to be posted around 11:10 AM I expect.
But it looks like things are goofy (on my end) as it says I posted the original question at 10:06 PM, but I don't work that late unless a server crashes in the night. Thankfully I've only had that happen twice. One time somebody else fixed it for me (as I was on vacation), and the other time I got up at 2:00 AM and worked on it until 6:00 AM... Windows updates blew things up on me and services started up in the wrong order.
EDIT - good catch with that impersonate thing. That was not set up. Quick and easy fix, but even still it helped me.
This SSRS instance was set up as we are in the process of upgrading Microsoft Dynamics GP from 2010 to 2016 and we wanted to deploy the canned reports (although some of them are busted). It pushes them out expecting either a new folder specifically for the reports and data sources OR that you have a blank SSRS server to drop things on. Our consultant (who did the initial deploy for test and documentation) got the reports out but had a lot of issues as we had used some folder names that already existed on the SSRS server he did (like "Data Sources") and GP (according to the consultant) can't push them to a defined folder. My install that I did (still test, but wanted to make sure the document was good) got this into a separate folder (which is good as we want that on our live deployment), but all the data sources were set to credentials "As the user viewing the report". So future upates are likely going to break the data sources again, but at least I know how to fix it.
EDIT2 - I had the wrong time zone set in my settings... it was set to GMT with DST turned on. Turned off DST and set it to -6 and I am hoping that fixes my weird time issues.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2017 at 12:35 pm
You're probably fixed on the time now, I had the same thing awhile back.
If you are going to have to do regular changes to the reports and it's for a lot, figure out how to do them with rss files using the RS.exe utility. You can do pretty much anything against reporting services using that. It's just VB.Net and scripting so pretty easy to write. Examples would be in the samples directory on the install (if you installed them) or google RS Utility Scripts and you can get quite a few. Most places I've been at use that for deployments. Some samples and links to others here too:
Script Deployment and Administrative Tasks
Sue
May 5, 2017 at 12:41 pm
Sue_H - Friday, May 5, 2017 12:35 PMYou're probably fixed on the time now, I had the same thing awhile back.
If you are going to have to do regular changes to the reports and it's for a lot, figure out how to do them with rss files using the RS.exe utility. You can do pretty much anything against reporting services using that. It's just VB.Net and scripting so pretty easy to write. Examples would be in the samples directory on the install (if you installed them) or google RS Utility Scripts and you can get quite a few. Most places I've been at use that for deployments. Some samples and links to others here too:
Script Deployment and Administrative TasksSue
Thanks for the link and the tip about rs.exe. We do not do a lot of regular changes to our reports. We basically build it once and then send it to end users to play with. The reports that I build generally are not changed once they are live. End users run them (with the parameters they need) OR they will create a subscription to it.
But that is a pretty nifty thing that RS.exe. For migrating the reports, I found a nifty tool called ReportSync that I may start using more.
With our SSIS packages, as we don't deploy that many so we do them all manually, our new policy is that we pull the package from the test server to the live and then assign the live environment to it. The reason for this is that then it is more likely that it was properly tested. Had too many go live that didn't even run through the test server so I made that rule. Thinking it isn't a bad rule for SSRS either.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply