As time goes by, I find myself architecting most projects the same way : using cloud patterns. The reason is simple, I find there are so much advantages to using newer techniques and technology that looking back doesn’t interest me.
For ADO.NET, when working with cloud ideas and patterns, that might mean having to do things a bit differently; this article is about security.
When working with smaller systems it could sound practical to perform security checks at the data layer, hence on the database. It allows the database administrators to control access to data from whatever access method they chose (excel, asp.net, winforms, wpf…). But this is a technique I have never particularly cared for, as I have always found direct access to the database to be a scary techniques, especially when dealing with bigger systems where the database turns out to be the central point of the system.
With cloud patterns, where we want each layer to scale correctly, maybe we need to revisit where authentication and authorizations are performed…
The manifestation of this is using application accounts when access SQL server through ADO.NET and performing checks on the middle tier.
If we do things like pass-through security, which would allow the user’s identity to flow to the database, the following effects occur:
- authentication occurs using the database’s protocol (with SQL Server that would be Active Directory), something that is not always cloud and internet enabled
- things like federated identity are constrained to the database solution used
- security administration in a production environment must be performed by highly specialized workers
- and if you use ADO.NET, the optimisations that try to limit the time spent opening and closing connections (i.e. connection pools) become useless as it cannot cope with the amout of users.
Luckily, there are a few work arounds:
- You can disable connection pooling (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(v=vs.110).aspx)
- You can imitate the concept by using application roles (http://www.sqlteam.com/article/using-application-roles). See this article for how to integrate it in your programming : http://tivadj-tech.blogspot.ca/2011/09/spsetapprole-and-adonet-connection.html
I hope your next project using SQL and ADO.NET will include a discussion on this subject, it’s an important one to have !
Modelon Solutions 
Leave a comment