1

I am trying to establish a very secure table(s). As a matter of fact, I prefer the same settings to apply to all tables within the database. Basically, this database would contain sensitive information: PersonID, CreditCard, Names, Address, PINs.

Nobody has the need to query this database; NO person. The only thing that needs to access the database is the integration services during the ETL process. As a result, my strategy to "secure the database/table" is to limit the access to the table to just the service account for Integration Services in COMBINATION with using TDE (encryption at rest).

my questions are: 1) is this a good strategy? it's important not to allow anyone (including myself) to be able to query the database 2) how do I limit access to a table to just the service account in SSMS? I dont know how I can just give "SSIS" access and nobody else. (I use SQL Server 2012 and SSIS 2012)

Thank you so much!!

LearnByReading
  • 1,813
  • 4
  • 21
  • 43
  • A sysadmin will always have access to everything. This is unavoidable and is actually a good thing. What is your plan if you prevent anything but a service account accessing this table and there is something wrong? Aside from a sysadmin this simply standard permissions. You have SSIS login with some service account and that information is not known for anybody else to login with. – Sean Lange May 13 '16 at 13:43

1 Answers1

0

I don't see how this would be a good solution. The problem is, someone could find out the password to the service user, resulting in access to everything. Also you couldn't restrict DBAs.

I would rather store all the information encrypted with a password that is not available to anyone. SSIS or whatever interface accesses the data would take on the role of encrypting and decrypting (if even needed). SQL Server also has some encryption options, if you don't have a corporate encryption method.

Also you could use a specific schema for the tables with sensitive data. You could by default deny permission to normal users for that db schema.

Raul
  • 2,745
  • 1
  • 23
  • 39