Configuring SQL Server databases for use with CSLA

Gareth posted on Wednesday, December 13, 2006

Hi all, another question from myself this morning. Having now got the whole lot of CSLA 2.1.1 and ProjectTracker compiling and executing I am now moving the databases into a fully blown SQL Server instance on the same machine.

As an interim step I am using just the PTWeb application talking directly to the database, i.e. localproxy with a valid SQL Server connection string. i.e. DataSource=localhost;Initial Catalog=PTracker;....
(Note, later I'll be moving to a Remoting Host in IIS but I wanted to get the basics up and running first.)

Ok so my question is what is the correct way to permission the databases (PTracker and Security) within SqlServer?
Do I just add the ASPNET user as [dbo].[owner] to both? Or is there a more granular, secure and recommended method?

I will NOT be using Windows Authentication for the web application. It will require the user to enter a username and password as per the PTracker sample.

Regards,
Gareth.

Bayu replied on Wednesday, December 13, 2006

Hey,

Let me start by saying that I am certainly on security expert.

But I do know that you could create a specific user account that exclusively has rights on the DB. In the configuration of your website you can let IIS impersonate this specific account. The advantage would be that if someone hacks you website, he only has rights on your DB.

The ASPNET account on the other hand is shared by all the websites that run on your webserver. So setting up a specific account would already be more granular.

Additionaly, if you make use of stored procs, you can limit the rights of the user to 'execute' only. The benefit is not too much I guess, as you will usually also have sprocs that delete stuff. Additionnaly, when this security measure comes into effect, you have been hacked already, so what are you chances anyways?

In my experience, although a lot of energy can be spent on securing your app and DB for attacks from the outside, the most danger tends to come from the inside. Ignorant DBAs or simply users who happen to have excessive rights (e.g. support people who claim to need administrator rights) and so on can also very much pose as great 'threats' to your app. ;-)

Bayu

twistedstream replied on Wednesday, December 13, 2006

With my experience with securing the DB of an ASP.NET web app, you have three options:

Use SQL Server Authentication, creating a unique login for your database. The down side is you're usually storing the SQL Server login in your connection string.
Use Windows Authentication, authorizing the ASPNET account (or in the case of Windows Server 2003/IIS 6, the NETWORK SERVICE account) to access your database. This is better than option 1, however, like Bayu said, if you have multiple web apps, all running as that account, they all have access to your database.
Use Windows Authentication with a custom identity. This is the most secure option of the three since it provides more granularity, but it is more complex to maintain. If you decide to go this route, you need to configure that new account properly, otherwise it won't run ASP.NET apps. For Windows Server 2003/IIS 6, follow these instructions.

As Bayu mentioned, I would also recommend setting the necessary minimum permissions for that account in your database instead of giving it global or owner rights. If you're using SQL Server 2005, doing this is a lot easier via schemas.

~pete

Gareth replied on Wednesday, December 13, 2006

Thanks for that. I suppose going with Option 1 the connectionstring could always be encrypted.

twistedstream:
With my experience with securing the DB of an ASP.NET web app, you have three options:
Use SQL Server Authentication, creating a unique login for your database. The down side is you're usually storing the SQL Server login in your connection string.
Use Windows Authentication, authorizing the ASPNET account (or in the case of Windows Server 2003/IIS 6, the NETWORK SERVICE account) to access your database. This is better than option 1, however, like Bayu said, if you have multiple web apps, all running as that account, they all have access to your database.
Use Windows Authentication with a custom identity. This is the most secure option of the three since it provides more granularity, but it is more complex to maintain. If you decide to go this route, you need to configure that new account properly, otherwise it won't run ASP.NET apps. For Windows Server 2003/IIS 6, follow these instructions.
As Bayu mentioned, I would also recommend setting the necessary minimum permissions for that account in your database instead of giving it global or owner rights. If you're using SQL Server 2005, doing this is a lot easier via schemas.

~pete

twistedstream replied on Wednesday, December 13, 2006

Yes, and if you're using ASP.NET 2.0, encrypting the connection string is a lot easier than it was in1.x:

http://weblogs.asp.net/owscott/archive/2005/07/29/421063.aspx

~pete