Linq and Database Security

Question

I'm a DBA within our development group and am trying to develop policy and standards for our application developers.

Our next project is an internet web application using .net framework 3.5 and SQL Server 2005.  Authentication to SQL Server is currently via a single application login, credentials are embedded in the connection string.

The developers want to use Linq and generate Linq queries using dynamic SQL to update the database.  This means I'll be granting direct table access:  select, update, insert, delete, across all accessed tables to the application login.

I found the following best practices document for web development which specifically states that direct table access should not be granted.  The problem is that it has not been updated to include anything about Linq.  I'm not sure whether the advice here is outdated, or if it still stands as Microsoft best practice.

***********************
Best practice policies for web development. In chapter 14 they outline all of their best practices for database access. Refer to the subsections (Use Stored Procedures and Data access authorization, assembly, and database).

 

 

http://msdn.microsoft.com/en-us/library/aa302430.aspx#c14618429_008

******************************

Pls be advised that I'm aware that Linq can be used just to call stored procedures, but that's not what our developers are interested in doing.  It saves time for them to code the db access directly in Linq, but I'm very concerned about opening the database security up to allow the level of access that would be required under a full implementation of Linq.

Here's my question ... does anyone know of a formal best practices document (like this one) which discusses best practice security measures for SQL Server access from Linq?  Some DBAs are advocating granting select access to tables, but not create, update, delete.  I'm hoping Microsoft has published something on this issue.

thx very much,
Kathy

Answer 1

Hello Kathy,
some information can be found on:
http://msdn.microsoft.com/en-us/library/cc716760.aspx

I am not sure whether you have specific questions.

Best regards

Ryszard Gawron [MSFT]

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Cross post, so case anyone else stumble on this via google or bing then there's a parallell version of this thread here:

http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/293a7770-86e2-418c-a8ae-63500b2e0d90

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)