SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Kerberos, but don't allow direct SQL Server connection


SSRS Kerberos, but don't allow direct SQL Server connection

Author
Message
sirokinl
sirokinl
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 52
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,
anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62326 Visits: 8584
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.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


sirokinl
sirokinl
SSC-Enthusiastic
SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)SSC-Enthusiastic (145 reputation)

Group: General Forum Members
Points: 145 Visits: 52
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 Smile

If there are most design options, I'd love to hear them.
anthony.green
anthony.green
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62326 Visits: 8584
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



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search