October 17, 2013 at 10:07 am
I’m a new analyst with my current employer. I was hired to automate some of the HR reports using SSRS but I’be come into an issue.
All of our HR data is stored in the servers of our vendor. We don’t have access to those server and use their web interface to export data onto excel and then import into MS Access.
What I would like to do is reach out the vendor and ask for a csv file with our data. However, I have to convince management that SQL is secured enough where HR and dba will be the only ones that have access to the data.
With that said, I’m reaching out to all you experts in SQL Server Central for some advice on how I can safely have the data in SQL and only us (HR) and the dba can have access to that data.
Should our data have its own schema?
Its own database?
Is there a way for sql to notify me when a query pulls in certain field (ssn, compensation, pto etc)
Once I convince management that it will be safe to have our data here the next goal would be to reach out to IT and have our department have its own data mart or database.
Please any advice would be useful.
October 17, 2013 at 10:14 am
encrypt the database and no one can see it. Do you stil want it in excel? SSRS cannot encrypt.
MCSE SQL Server 2012\2014\2016
October 17, 2013 at 10:31 am
If the database is encrypted will it affect any Connections? Will we need any "special" type password to access is or will it just be hidden from everyone else but us?
October 17, 2013 at 10:36 am
There's no need to create a schema. However, it would help you to manage the information. You can DENY premissions on specific objects. If you have a good user management, you could DENY ALL Privileges on the objects. Here are some links to more information.
October 17, 2013 at 10:45 am
This is a complicated question. It's not easily answered.
TDE will encrypt the data files and backups. Without the password, people will not be able to attach/restore the database.
However. Every person with a valid login/user to the database can see the data. That's why it's transparent. No code changes.
If you want to protect the data from some users, but not others, you need column level encryption. Symmetric/asymmetric encryption of data. That means some code changes. However, with passwords, you can protect the data from the DBA or unauthorized users.
October 17, 2013 at 10:47 am
Steve Jones - SSC Editor (10/17/2013)
This is a complicated question. It's not easily answered.TDE will encrypt the data files and backups. Without the password, people will not be able to attach/restore the database.
However. Every person with a valid login/user to the database can see the data. That's why it's transparent. No code changes.
If you want to protect the data from some users, but not others, you need column level encryption. Symmetric/asymmetric encryption of data. That means some code changes. However, with passwords, you can protect the data from the DBA or unauthorized users.
What Steve said... 🙂
MCSE SQL Server 2012\2014\2016
October 17, 2013 at 10:48 am
If the datababase is on SQL then only those in the sysadmin server-level security role, plus those giving security permissions to the database, will be able to view the data.
Transparent Data Encryption (TDE) does not affect the security design you implement or connections. But it does mean that if anyone copies a backup file, they will be unable to restore it elsewhere unless they have copies of the exported encryption keys, which you will hopefully export to a thumb drive and store in a safe away from the data center in case of server failure (otherwise you won't be able to restore a backup either).
TDE does create a 5-10% performance hit as data goes through the decrypt/encrypt process as it goes in & out of the database.
It is also possble to selectively encypt sensitive columns within a database.
October 17, 2013 at 11:26 am
Yes I know its not an easy question or decision for management.
This however is a good start. I will do some more research and present something soon.
October 17, 2013 at 12:26 pm
I'm just slightly confused: The current process having Excel files, MS ACCESS and the like involved is obviously "secure enough", but SQL Server is questioned? :doze:
Has there ever been a detailed security audit of the current process including the vendor and the web application? Just wondering...
Some security advices I would consider aside of the information already provided:
- The server should not be accessible from outside the organization that need access to it (e.g. using firewall settings).
- Use Windows authentication instead of Mixed Mode
- I'd prefer using windows logins vs. AD groups, if possible
- monitor all logins into SQL Server, not only the failed ones
- install an automatic auditing process that frequently check for any "unusal access" to the Server itself (meaning logon to the OS) as well as SQL Server
- allow access to the data only via stored procedures and include the required logging mechanism into those sprocs.
- prepare the data needed for the reports at the database level (again, via stored procedures only)
There's a lot more to consider...
October 17, 2013 at 12:33 pm
Hi LutzM,
I asked myself the same question. I'm still the new guy though & trying to figure out how things work but for the most part that's the current process.
October 17, 2013 at 11:26 pm
Having Access able to see/modify data in your database is a terrifying idea, IMO. You might be safe if you grant the users ReadOnly access, but a knucklehead with Access is a dangerous person. The place I used to work did something like that, and you're setting yourself up for a world of hurt doing that. One thing you can do with Access is automation, so a person with ill intent can do something like loop through records and send e-mails to people etc, and you wouldn't even know it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply