2

I am designing a web application that will have probably around ~1000 users. The website is facing the internet but it can only be used by partners (i.e. the general public cannot just "sign up an account"). Account maintenance will be handled by our own IT support.

I am considering having each individual user to have their own SQL login associated. This way I do not need to store their salted/hashed password - SQL server will handle the authentication. This also allow us to use SQL Server based audit and logging tool.

Is there any disadvantage doing that?

3
  • One disadvantage I see is managing security. Unless you plan to put all 1000 users into the db_owner role or with grant all on a schema, what are you going to do when you add a table or procedure? Grant rights on it to all 1000 users individually? Are you really going to have the application change the credentials in the connection string for every user? (This defeats connection pooling.) May 29, 2015 at 15:54
  • 1
    Honestly, storing a salted password hash for application users is not all that hard - there are plenty of tutorials out there for doing this right. I think 1000 users in a table is much easier to manage than 1000 SQL logins/users. May 29, 2015 at 16:00
  • Consider using ASP.NET Membership (msdn.microsoft.com/en-us/library/tw292whz(v=vs.140).aspx) for authentication and perhaps authorization. The database, schema, and data layer are already designed.
    – Dan Guzman
    May 30, 2015 at 15:15

4 Answers 4

2

Yes, plenty :)

  • You will have to setup encryption from the application server to the database server as the password is passed in clear text.

  • All login requests from the internet will result in an expensive authentication request to the database server.

  • You are not able to use connection pooling which will result in having a single connection for each user accessing the database.

1
  • SQL Server TDS protocol login packets are always encrypted. I don't think SQL Server authentication requests are any more expensive than other authentication round trips. The connection pooling issue is a biggie, though; user will have a separate pool.
    – Dan Guzman
    May 30, 2015 at 15:22
0

If every web user is a SQL user, and needs to authenticate with SQL server using it's own account you will have to store the SQL password instead of the web user password, and you will have to store it unencrypted as you need to decrypt it before you can authenticate to SQL.

Just act normal, use either windows authentication or a single SQL server account to authenticate the web site to the SQL server, and manage your web users in your web application.

0

The biggest flaw that I see is that if any of your users ever found a way to access your database directly (I realize it might be locked down, but accidents and hacks happen), they'll then have the ability to trash it. Your application will make sure that they don't modify the data in unacceptable ways, but if they login directly no such safeguards will be in place.

@spörri's point about connection pooling is very valid - the performance of such a setup could be poor.

I second @aaron-bertrand and @tom-v - SQL logins are not intended for this use case, and it could be real management headache to try and make it work that way.

-2

The only strong reason to install separate instances on same hardware is if you have very very stringent security isolation requirements. Otherwise is always better to have only one instance. One single instance can better optimize all hardware resources for the load you're facing. Multiple instances don't communicate to each other and they overlap the memory, I/O and CPU load resulting in worse performance.

Also a single instance is easier to administer, monitor and troubleshoot rather than separate instances.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.