I have been reading everything I can about SQL/Server security best practices.
I have a "multi-tenant" database shared by different customers. Although I am providing the software/service/administration/hosting (SaaS-style), the data should be confidential except to the customer. (Each customer can supply their own encryption key.)
I want to be able to say: "Our employees cannot see your data either by policy or capability". How do I accomplish that?
Maybe SQL/Server isn't really setup for SaaS in this way -- I can't seem to find the way this is done in practice.
PROBLEMS:
1.Encrypting the database or using TDE makes the MDF useless if it is stolen from disk or backup, but doesn't protect it from anyone with sysadmin right.
- SQL/Server choices are only windows authentication or combo SQL/windows authentication, so anyone who has access to machine where SQL/Server is running gets full access to the DB. There's no way to force password access to a DB when logged into SSMS directly, right?
SOLUTIONS?
So, I'm left only with data encryption.
SELECT decryptByPassphrase( "my passphrase",fieldname) where decryptByPassphrase(fieldname2, "my passphrase") = 'xyz'
Above statement works great, what's wrong with this approach? On AdventureWorks Customer table, 18k records, if I do this on a field called EncryptedName which is indexed, addes one-half second to the query. Yet, I never see this suggested out there.
So I can either have the customer enter their passphrase (in memory, never stored) or I can encrypt it with a short PIN (again entered on the fly by user, never stored).
How is the rest of the world solving this problem for SaaS use or whenever storing data that should not be seen even by the developer/DBA while still allowing queries?
