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!
Best Answer
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.
"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.
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.