Recommended ways to create a new database from an ASP.Net application

Question

Our ASP.Net application uses SQL Server 2008. Most of the time the application connects to SQL Server using a SQL account with very limited access rights.

However once in a while we need to be able to create a new database on the fly. As such we need elevated permissions and I am a little nervous about storing this connection string in Web.config, which may be in a DMZ.

We are considering writing a Windows service to run on the SQL Server machine (i.e. not in the DMZ) which will monitor a table for requests to create a new database, but it seems like overkill.

Any suggestions for alternatives or recommended practices?

Answer 1

You can store the connection string in the registry and protect that by limiting access to the specified registry keys. That's one of the ideasI ran across back in .Net 1.1 as a reccomendation from Microsoft. The concept is still the same in 2.0 and up. Here's a link to the documentation.

http://msdn.microsoft.com/en-us/library/aa302406.aspx

It sounds like you're already concerned about security, so I'm guessing you've read through or at least run across the "Building Secure ASP.Net applications" section of the MSDN library. The link above is in the how-To section of that guide. Hopefully this is helpful.

Also, if you DO store your connection info in the web.config, at a minimum, encrypt those portions.

And I just ran across this. Probably more like what you were looking for.

http://msdn.microsoft.com/en-us/library/aa302388.aspx#secnetch08_storingsecrets

Answer 2

If you are using mixed mode authentication in your database connection strings (I.E., username and password) then you should encrypt the web.config connectionStrings element.

Answer 3

What about using a stored procedure to create the database? I haven't tried it; the one part I'm worried about is specifying the database name through a variable. By using the stored proc, you only need to grant your web id execute access on the stored proc.

Another option would be to create a console app (instead of a service). Then use a job scheduler to run the job every 15 or 30 minutes or upon request if you have a capable scheduler. That will be much simpler than writing a service; it just isn't an "instant" process. I do this for some Active Directory work that triggers off of web site updates (I didn't want to give my web id Domain Admin priveleges).