October 27, 2015 at 3:18 am
Hi all,
Thanks for taking the time to read my post. I'm having some security design issues which I'm not sure how to tackle. Let me describe the infrastructure we have now.
First we have a Windows environment with Kerberos enabled. We have a SSRS and SQL Server database on the same server (I know, we are going to separate these onto two servers). We have enabled Windows Integrated Security on the data sources in SSRS. We have created a number of Windows logins on the SQL Server based on Windows Active Directory groups. We have created a database role and added these Active Directory groups into this role. We have granted Login to these Active Directory groups to the database that holds the data we run the reports on. Then we granted SELECT rights on a number of views that are needed by the reports.
So far so good. Everything runs smooth, people can run reports etc. But here comes the part I don't like. People are able to make a direct connection to the SQL Server and query the views directly. Content-wise it's fine because the views are limited to what they are allowed to see. But I don't like it that people directly query the SQL Server.
Is there a way I can deny direct logins to the database and only allow people to go through SSRS when query'ing data? I have tried to REVOKE CONNECT from these Active Directory groups but then the reports stop working (login denied for user......)
Btw, the reason I use Windows Integrated Security is because we have row-level security built into the views so people are only able to see the rows they are allowed to see. If I were to remove Windows Integrated Security and use a service account, I think this feature would not be possible right?
Any guidance here is greatly appreciated.
Kind regards,
October 27, 2015 at 3:41 am
Does the report have to run as the user running the report?
If not then change the data source to use a generic account to run the queries, this is the easiest way.
If you do, you will need to look at a logon trigger that sniffs the application name of the app the user is using to connect (NOTE: this doesn't guarantee the user wont get in as app names can be spoofed) and only allow anything thats using the SSRS application name and where the username is in a particular list of users. You dont want to lock everyone out of using SSMS to query the server directly.
October 27, 2015 at 3:50 am
Hello Anthony,
First of all, thank you for taking the time to answer my question.
The reason I run the reports under Windows Integrated Security is because we have enabled some row-level security mechanisms where the IS_MEMBER() method is used through a JOIN on the views requested and only those rows are returned which the user is allowed to see. So running a report under a general account (as far as I know) eliminates this requirement by my client.
A login trigger will probably do the trick indeed, I will look into this option. Have to be careful with using the login trigger, don't want to lock out everybody 🙂
If there are most design options, I'd love to hear them.
October 27, 2015 at 3:54 am
You could also firewall off people from connecting to the DB server by blocking your active port for the instance out for everyone, then only punching a hole into the rule for those computers who need to query SQL directly.
Would mean you would have to set everyone who needs to query the DB up with a static IP address, don't want your DHCP address changing locking you out from accessing the server
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply