Project structure to prohibit direct access to database by users

triplea posted on Tuesday, September 09, 2008

Up till now I have only used CSLA with its simple dataportal implementation (everything done locally). I am at the designing stage of a bigger project where one of the main requirements is to prohibit users accessing the datastore (SQL Server 2005) directly via Excel/Access etc.
I have done a bit of reading (and need to review the book again after a long time) about how to get about this. Also, I checked the following posting about pros/cons: http://www.lhotka.net/weblog/TheCostsAndSecurityBenefitsOfNtierOver2tier.aspx. What is not clear to me yet is whether I will be able, by adopting a 3-tier model (or something alternative which I have completly missed), block users accessing the database directly. I think its a combined infrastructure/implementation effort since I assume that I could restrict the database to allow connections only from the application server. Is that realistic or did I get this completly wrong? Simply hiding database credentials (i.e. database server name) would not be enough in this scenario, an actual restriction is required.

We will be using CSLA 3.0.4 (.Net 3 installed on all machines), SQL Server 2005, it is a Windows Forms app and Windows Authentication is used throughout.

ajj3085 replied on Tuesday, September 09, 2008

The short answer is that yes, you can.  If you have Sql 2005 for example, you can set it up to accept only Local connections.  If you also use IIS on the database server, this will be your Application server as well.  So your BO's DataPortal code will run on the database server, and it will be a local connection.

This is how I have my system setup.  That, in combination with the compressed dataportal, actually sped up access.  The chattiest part of a database application is talking to the database.. now that happens all locally and just the BOs travel over the wire.  Which is perfect, because this made things much better for my users at the other office.

triplea replied on Tuesday, September 09, 2008

Thanks for the info. I just have 2 (small) questions:

"If you have Sql 2005 for example, you can set it up to accept only Local connections.  If you also use IIS on the database server, this will be your Application server as well."
What if for some reason running IIS on the db server is not an option? Would the structure still work? And can you set SQL 2005 to accept only Local connections on database level or is it only on server level?

"That, in combination with the compressed dataportal, actually sped up access."
What do you mean by "compressed" dataportal?

Thanks a lot!

ajj3085 replied on Tuesday, September 09, 2008

At that point you'd need some kind of firewall to restrict access.  For example, set the firewall to only accept database connections from a specific IP address.  There are other hardware configurations as well.. but no matter what, you want the quickest connection possible between the application server and the database server. 

I think on the CslaContrib project there's a data portal which compresses the byte stream before sending and decompresses it on the application server.  I'm using that DataPortal proxy.