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.