5

We have multiple SQL Servers with each holding dozens of databases - one per client (a client in this case means a customer organization). These databases are accessed via an application, but the application is currently using a single Windows login. Therefore, this creates a security risk, i.e., it's theoretically possible to access "the other" client's database if some application vulnerabilities exist.

What is the best way to handle this scenario?

Should we create a separate login for each client and have the application connect using separate login credentials? This will reduce the security risk but create significant management overhead (which might be worth it).

A follow-up question would be: should we use Windows AD security or SQL Server authentication in this case.

I appreciate any suggestions!

Improve this question

5 Answers 5

Reset to default
11

Should we create a separate login for each client and have the application connect using separate login credentials?

Short answer: Yes.

You've segregated each client's data into a separate database, so now you need to segregate the access to those databases as well.

This will reduce the security risk but create significant management overhead (which might be worth it).

"Significant"?
Just how often do you take on new clients?
If the answer is "a lot" then you seriously need think about some Automation to help you out, here.

A follow-up question would be: should we use Windows AD security or SQL Server authentication in this case. I appreciate any suggestions!

One advantage of using SQL Server authentication is that is (or, at least, used to be) much easier to create SQL Server accounts using scripting (although PowerShell's done a lot to level that particular "playing field"). You can create the database account (and password) and the application configuration that uses that account through scripting, without mucking about creating Domain accounts by hand.

Improve this answer
4
  • 2
    1 db per client is the way to go imo, really good fit to Azure sql db elastic pools too. Yes you need to segregate db access as well
    – Stephen Morris - Mo64
    May 3 at 8:24
  • To answer the point above, we onboard new clients on a regular basis, so yes, this would need to be an automated process.
    – SQL_Guy
    May 3 at 20:26
  • @SQL_Guy Don't you already have an automated process to create the new database for new client?
    – Bergi
    May 3 at 22:48
  • 2
    @SQL_Guy FWIW, I think you're overestimating the overhead, if you currently only have "dozens of databases". When I used to work for a SAAS company with one database per client, we had roughly 1,000 total client databases divided across two different servers, following similar security provisioning as Phil's answer recommends. We probably added a new client every week or so. The overhead to managing this implementation was minimal even then.
    – J.D.
    May 4 at 1:35
5

One option that I don't think was mentioned is contained users (contained authentication, partially contained databases, whatever you want to call it).

You don't have a login. You either have a SQL user with password or you create a user based on a Windows account. Or, in Azure, if you have AAD "FROM EXTERNAL PROVIDER".

Now you don't have the dependency of a login, makes it easier to move databases. And, this is the "natural" way to do it in Azure SQL Database.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

Improve this answer
1

dozens of databases - one per client

I am guessing, that is the reason for down votes of the question. That is a very bad organizational decision. But if you "client" is actually a group of individuals - departments for example, then yes, this approach can have some merit. If a "client" in your case is an individual person - please reconsider! There are tons of single-user databases starting with Access and sqlite, the SQL Server is not a good DBMS for personal databases.

Once this controversial problem is out of the way...

The approach to security on mulit-db server is largely the same as with a single database. Create as many logins on the server as needed (one per person). But assign each login default database and, if needed, add this login as user to other databases.

It is better (at least from user point of view) to use Windows AD security instead of SQL Server's own. Because in this case, the login become something like "mycompany/personid" and you just add this login to specific databases as needed. But the actual user will not need to "login" to database anymore.

For DBA using domain security is also easier, since if a person forgets his/her password - the domain's admin will deal with it, not database admin... But from all other DBA's points of view - domain or server security does not differ much.

the application is currently using a single Windows login

That is also very bad decision. Application should not use any logins at all, but ask the user to provide credential. They can be hidden by usage of domain logins, but it still would be personal logins.

If you really need an application (like a scheduled robot) to do a login without human present - that is usually done by creating a special user in the domain - the one which would be specific to this application and no human should be able to use it.

Improve this answer
1
  • 1
    Thank you for the feedback. I clarified my question - the database is not for an individual, but for the organization. These organizations are our clients. Each organization has their own users, security etc. - this is internally maintained within each database and configured/maintained as part of the application functionality. The question was about our cloud application making connections to those databases.
    – SQL_Guy
    May 3 at 5:11
1

You can use individual login for individual database and make sure to sort out user mapping for each user so that it is assigned the dbowner of the database.

Also be sure to use different password for each user and that system user password is only known to few. If you have to give password to clients themselves.

PS: Whenever removing or migrating a database make sure you first remove user mapping and then proceed to do migration and detachment.

Improve this answer
-1

All these answers are wrong (something I have never written before.) All the users should be put into separate groups and the groups should be granted permissions. Maintaining permissions on an individual level is very, very challenging (a real time suck.)

Improve this answer
2
  • 1
    Ah, the venerable “only assign to a group” argument. Generally a good one actually, but not in this case. The maintenance would be the same either way. The point here is that these users (logins) would not share common permissions. Rather each user would have permissions to only their own database. So you’d need a group for each database and then permissions for each group. Doesn’t buy anything other than twice the management object count.
    – Walden Leverich
    May 11 at 20:36
  • In real life more then one person will access db. I have never seen a db where only one person accesses it.
    – benjamin moskovits
    May 13 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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