1

I am deploying an ASP.NET application and SQL Server (2008) database on a live (production) server. The physical server is running both SQL Server 2008 and IIS 7 - it is provided by a hosting company and is not part of our internal network.

I have a couple of questions regarding database security and the connection string for the ASP.NET application.

Previously I would create a database user and specify the SELECT/INSERT etc. permissions for each table - but my issue is that there are 50+ tables in this database, so doing this would take a long time.

The application requires SELECT/INSERT/DELETE/UPDATE on each table.

  • Is there a better way than specifying the permissions for each table individually?
  • Is there an equivalent of integrated security for a live web server - what are the drawbacks?
  • Or is there a way of elevating the access rights for a particular user to full access for a particular database

Also how would the connection string change?

I just looking for some expert advice, just someone to point me in the right direction and a link to some documentation on how to achieve a better way of doing it.

Many thanks.

Chris Cannon
  • 1,157
  • 5
  • 15
  • 36

2 Answers2

1

You have essentially three unrelated questions in your bullet list, not one.

  • The first one is a better fit at ServerFault.com, as it deals with SQL Server, not necessarily a programming question.
  • For the second one, Integrated Security, yes, there is a way to use integrated Security with ASP.NET. See this article: http://msdn.microsoft.com/en-us/library/bsz5788z.aspx
    • The biggest drawback is that it's more to configure. it's a non-standard (but supported) configuration, so maintenance programmers may not have seen the setup before.
    • Also, if you're doing this, there could be security concerns if you're using an account that has permissions elsewhere. Be sure to follow the principle of least privilege. It might be best to create a Domain account specifically for each website, so if one gets compromised, it limits the damage that can be done. You know your security concerns better than I do, so this may or may not be relevant advice, but it's something to consider.
    • Finally, (and this is probably too obvious to point out) it would be foolish to use a real person's UserId. If that person leaves the company and their account is removed, the website will obviously break.
  • Now that I've found an answer for the first question, the third one becomes moot.
Community
  • 1
  • 1
David
  • 72,686
  • 18
  • 132
  • 173
  • http://msdn.microsoft.com/en-us/library/bsz5788z.aspx - If your application runs on a Windows-based intranet - is this for intranet websites? – Chris Cannon Apr 16 '12 at 17:57
  • Yes. that would be necessary for Windows Integrated security ***IF*** the SQL server is on another server. You need a permission source that both the DB and the website trust. If the SQL Server is on the same machine as the web server, you can use a local account set up on the machine. Most websites have the DB on another server, though, for performance, if nothing else – David Apr 16 '12 at 18:13
  • Thanks for your help, in my case IIS and SQL Server are both running on the same machine - it is feasible to set up integrated security for this ASP.NET application on this server? Which is a external/public web server. How difficult is this? Are you saying it requires creating a locked down user account on the server? – Chris Cannon Apr 16 '12 at 18:18
  • Yes, it is feasible. In your scenario, you can grant the \Network Service the required access on the SQL Server. The thought of it makes me nervous, but I'm not sure why. Think ing it through, I can't see how this is any more/less secure than having a SQL user with access. I'd ensure that it has only the permissions it needs and no more. Also, of course, protect against the OWASP Top 10 to the best of your ability. https://www.owasp.org/index.php/Top_10_2010-Main – David Apr 16 '12 at 18:25
  • You obviously know a lot more about SQL Server security than I do. It looks like the best option is to add the database user to the db_datawriter role. Does this include stored procs and UDFs? – Chris Cannon Apr 16 '12 at 18:32
  • I don't know definitively. Based on my experience, I htink that you need to grant permissions to the stored procedures, AND the user has to have the appropriate rights to the underlying tables. So for example, if you have a stored proc called "UpdateCustomer" that updates a "Customers" table, you need to ensure that the user has EXECUTE rights on that stored procedure AND UPDATE rights on the table. – David Apr 16 '12 at 18:41
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10134/discussion-between-chris-cannon-and-david-stratton) – Chris Cannon Apr 16 '12 at 18:49
1

You can create or use an existing database role. Then you put the user into that role to allow that user to have all the permissions you need. For example, you might put the user account you are using in your connection into the db_datawriter role given the scenarios you described.

See http://msdn.microsoft.com/en-us/library/ms189121%28v=sql.105%29.aspx

This article also related to how you could elevate a user's permissions.

Integrated security will work--it just requires that the login used on the computer that is making the connection be recognizable by the database server (in the same or in a trusting Windows domain).

Shawn
  • 8,374
  • 5
  • 37
  • 60
  • Using db_datawriter seems like the best approach, am I right in thinking Integrated Security is only for websites running locally or on an intranet? – Chris Cannon Apr 16 '12 at 18:23
  • In typical situations, you are correct. See http://stackoverflow.com/questions/651531/windows-authentication-for-intranet-internet for other details on the non-typical configurations that might allow it to work even in an internet scenario. – Shawn Apr 16 '12 at 19:23