September 19, 2016 at 3:03 am
Hi
how do people view the security (or lack of it) between SQL server and MS Access ?
Would anyone use MS Access for sensitive data ?
September 19, 2016 at 3:34 am
I'm quite sure that SQL Server is much more secure than MS Access.
If your data is really sensitive, I'd avoid Access.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2016 at 3:56 am
Hi thanks SS Coach. Thought as much.
It's clinical trials data.
September 19, 2016 at 4:11 am
page853 (9/19/2016)
Hi thanks SS Coach. Thought as much.It's clinical trials data.
So it contains PHI data. I'd say that's classified as sensitive.
September 19, 2016 at 4:20 am
PHI ie personal health information. Yes.
I'm guessing the consensus is we should avoid using MS Access
September 19, 2016 at 8:12 am
Well, they are different in a few ways. Access would depend on the ACLs in the file system to limit someone from opening the Access files. However, someone could take ownership here. There would be a record of that, but still an issue.
This is the same for SQL Server in that ACLs usually limit the mdf/ldf/ndf files to the service account, so you aren't granting extra rights to users. The service usually has the files opened, so they can't be copied, though there are ways around this.
From the user perspective, depending on version, you can protect items in the database by assigning rights to users, using macros, etc., but not sure how hard it is to bypass these items. SQL Server security is fairly strong, no access by default, and the admins must grant access to tables, views, procs, with roles or individually. There is row level security in SQL 2016, but not easily implemented in previous versions.
Access tends to be implemented as file based security for the database file, but you can compile the application and set AD group security, which is similar to what you have in SQL Server.
I think the view from the technical side is SQL Server is more secure, but in practice, I'm not sure how much more secure. SQL Server tends to scale better and handle concurrency better. There are options for encryption and protection at more granular levels, but perhaps that isn't a concern for you.
It's easier to move data around in Access (copy the db), which is something I view as a security item. If it's hard to get SQL Server applications or connections made, then people tend to fall back and use something like Excel to share data, which is a security risk. In Access, they can share the db file, which is similar risk, but there can be encryption and password protection.
On balance, I lean towards SQL Server, but I'm a SQL Server guy. I'm not sure that practically it matters that much.
September 19, 2016 at 8:20 am
Hi SSC-Dedicated - thanks.
We use classic ASP web front end........so external (i.e. not part of our organisation users) granted access to the application can obviously access the Access data (as opposed to SQL server where an application tends to use a proxy account).
We do encrypt certain fields (name /address etc) and have these in a separate Access DB - but as someone mentioned to me - it's easy to lift the entire Access DB and hack these at your leisure.
Does the fact that we're using classic ASP and in Access in tandem increase the lack of security ?
September 19, 2016 at 8:32 am
Most RDMS are air-tight from external hacking attempts when the DBA properly implements authentication and authorization.
At least based on news reports (and subsequent technical analysis by the industry), I'm left with the impression that most bulk data breaches are the result of a hackers gaining access to the network file system (stealing data files and documents) or SQL injection. With MS Access, you're more at the mercy of how the network itself is configured and how the application is coded.
SQL Server is clearly more secure than MS Access in that regard.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 19, 2016 at 8:47 am
If it's clinical trials data, that's all covered under HIPPA/PHI rules.
Having Access involved is a security disaster waiting to happen. It has no built-in security anymore. If at all possible, don't let users view data through Access, because of the way the "security" model in Access works - it's file-based. So anybody who can read the database can copy it/do whatever they want with it.
I had to explain to my boss the kinds of things the company I worked at was opening itself up for using Access. Not pretty at all.
September 19, 2016 at 9:06 am
If using MS Access as the database, then you can improve security by implementing something like a web services based data access layer. Specifically, the service account that the web appliation runs under should not have access to the folder containing the .mdb file.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 19, 2016 at 9:22 am
Hi SSCrazy
could you point me to the relevant HIPPA stuff re: MS Access (or alludes to it)
thanks
September 19, 2016 at 10:09 am
Eric M Russell (9/19/2016)
Most RDMS are air-tight from external hacking attempts when the DBA properly implements authentication and authorization.At least based on news reports (and subsequent technical analysis by the industry), I'm left with the impression that most bulk data breaches are the result of a hackers gaining access to the network file system (stealing data files and documents) or SQL injection. With MS Access, you're more at the mercy of how the network itself is configured and how the application is coded.
SQL Server is clearly more secure than MS Access in that regard.
SQL Server is just as vulnerable to SQL Injection, especially in the OP'd case, as Access.
I would agree Access is more susceptible to issues at a file level than Access.
September 19, 2016 at 10:10 am
pietlinden (9/19/2016)
If it's clinical trials data, that's all covered under HIPPA/PHI rules.Having Access involved is a security disaster waiting to happen. It has no built-in security anymore. If at all possible, don't let users view data through Access, because of the way the "security" model in Access works - it's file-based. So anybody who can read the database can copy it/do whatever they want with it.
I had to explain to my boss the kinds of things the company I worked at was opening itself up for using Access. Not pretty at all.
Are you sure? I've seen notes from Access 2010 and 2016 that with a compiled application, you can set user level permissions through AD. I know older verisons allowed all users that would open the file access to all data. Is that still the case?
I'm not an Access expert, was going based on what I've read recently.
September 19, 2016 at 10:11 am
page853 (9/19/2016)
Hi SSCrazycould you point me to the relevant HIPPA stuff re: MS Access (or alludes to it)
thanks
It's pietlinden, not SSCrazy.
September 19, 2016 at 10:33 am
Steve Jones - SSC Editor (9/19/2016)
Eric M Russell (9/19/2016)
Most RDMS are air-tight from external hacking attempts when the DBA properly implements authentication and authorization.At least based on news reports (and subsequent technical analysis by the industry), I'm left with the impression that most bulk data breaches are the result of a hackers gaining access to the network file system (stealing data files and documents) or SQL injection. With MS Access, you're more at the mercy of how the network itself is configured and how the application is coded.
SQL Server is clearly more secure than MS Access in that regard.
SQL Server is just as vulnerable to SQL Injection, especially in the OP'd case, as Access.
I would agree Access is more susceptible to issues at a file level than Access.
With SQL Server we have option of stored procedures, thus avoiding potential of SQL injection, but with MS Access it's embedded SQL.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply