securing SQL SERVER when using MS-ACCESS-frontend & ODBC
-
Hello,
After migrating some MS Access based ETL applications to a MS SQL SERVER DB (through ODBC connections) I need to secure the database access.
Can anyone point me out a good link with a best practice for this kind of procedure?
Thanks a lot,
-
When i moved my access -> ms sql + frontend (via odbc) i used standard trusted authentication on the SQL side of things to secure access - that way it didnt matter what the front end did/was/etc - if we dont allow users access in the sql server security settings, they arent getting in at all!
If your using an access database as the front end, ive found theres only so much you can do to secure things. Ive never liked accesses built in user stuff and completely ignore it, I lock things down by.....
- hiding all objects in my access front end (so they cant see anything if they load your access front end into access by hand, unless they have view hidden objects turned on
)- I get my user login from what they are logged into windows with, and then use that for internal user preferences/security etc. in code in a startup form - if they can't log into windows with that user account, they are not using that user in my system!
- only ship the .mde files, and I also have a sliver of code on each form in my system that checks if my startup form has been run when I start the application. If it hasnt, the forms won't let themselves open (and close everything down
) - so users must go through my startup procedure (which includes security checking) to use any of the front end. (Helps stop people who load up your application by hand into ms access and start browsing through objects)I don't go beyond that, as all the users are `trusted employees of the company`
- locking down their user access in sql server, and securing up the front end as above is about as much as I know how to do - if someone really wanted to mess things up they could bypass the front end all together and just hit the ODBC connection directly - hence putting the security emphasis on the sql server.Is that any help?
Martin
-
Thanks a lot for replying!
-
A few things come to mind:
I don't trust any method (including Windows authentication) that gives any permissions to users outside of the application. Therefore, I use application roles (applied on the 3 main Access connections) with ADE front ends exclusively. App Roles, however, have some issues that you need to work around (see the MS KB).
As second best, for ODBC MDE files, I would use SQL Server security with a defined Application User account, that has its password obscured in your VBA code. You can define and assign different Application User account based upon the curent logged on User.
Don't ever rely on any built-in Access security, as it is trival to hack with a password finding program (< $100).
Never let users enter SQL statements directly (SQL injection).
Don't ever let users have access to the database window.
Don't let users edit or access built-in command bars, that provide access to data editing tools.
HTH,
Rich
-
What type of practices are people using to prevent users bypassing Access all together and accessing the SQL Server. Example issue... using Excel's GetData function to access the SQL Server through the ODBC connection.
-
As I mentioned above, Application Roles can solve that problem completely. You can also embed obfuscated connection info for for hidden accounts into your code.
You can also use a web front end or middle tier to completely prevent any network access to the SQL Server.
My preference for Access is App Roles.
-
Um! Why? Provided you've got your SQL groups and Windows security set up properly, there are good reasons to allow this e.g. for a mail merge or possibly for a pivot chart in Excel.
Unless you mean locking the data up, you need to allow some sorts of access. If you set your security properly users should only see what you want them to see and edit only what you want them to edit. It doesn't matter if the controls are unlocked or whatever, SQL Server will give them some really messy error messages if they try to edit the data.
That's why I prefer to have security at the user level. It also allows auditing to see who did what.
-
setting up SQL Server for Windows Security and using 'trusted connection' in Access is the most secure!!! That way, security is set up in sql server for any application that connects, not just the access app. This also gives the app the ability to execute only views or stored procedures and deny direct access to the tables. It's also MUCH easier to secure in SQL Server than Access. If the user get's it wrong, it's easy to lock up the app. I haven't searched password scraping apps.
-
"Application Roles can solve that problem completely"
defiantly an interesting way of doing it
If you do have a security breach and someone finds out a password though, you will have to deny access to everyone using that connection, and redistribute a completely new client with any updated user/password to allow anyone to connect again.
(My clients rely on a connection to the database to check if there are later versions of themselves available to automatically update so closing connections down completely would be a bit evil.)
I bet someone could write an interesting article on all the plus/minus points on all this kind of thing (ive not come across one before in my travels)
martin
-
"If you do have a security breach and someone finds out a password though, you will have to deny access to everyone using that connection, and redistribute a completely new client with any updated user/password to allow anyone to connect again."
Not true...
In my Access apps using App Roles, admins can choose to randomly change the AppRole passwords (via automatic client side VBA code). App Roles pws are reconstructed in the client app through a custom routine using heavy encryption. Passwords are NEVER stored in the app, or in the database itself. I store some encrypted info in the database, and some encrypted info is stored in the client and other (secret) places, that allows me to reconstruct the passwords on the fly via a rather complex custom algorithm.
With completely obfuscated and regularly changing AppRole passwords, you get an extremely secure system, with no access from outside my app. There is no password to steal. In fact, it is not possible for anyone to know the current random password, as it is never stored or revealed in an unencrypted state.
This of course assumes standard network protocol encryption/SSL etc. Even if a packet sniffer were used on an unencrypted (gasp!) network connection, my AppRole passwords would be regenerated on the next login, thereby preventing reuse. And of course, a user can't ever use an AppRole password to log in directly. App Roles must be applied in code directly to an existing connection - so you need to write an application just to use the pw.
BTW, changing an App Role password has no effect on other users, because the info required to regenerate the obfuscated pws is stored (heavily encrypted of course) in various places, and is regenerated in the client app whenever it is needed.
There are a few articles on this, although I have not met anyone else who has successfully applied to ADP/ADEs.
The most important undocumented caveat is that you can't use standard subform linking - you have to use InputParameters or dynamic SQL instead of Parent/Child fields, and you have to use a SQL statement for the subform's recordsource, and never a table name. If you don't follow these rules, the subform connection will not have the App Role appled to it.
You also have to remember to apply the App Role to the separate combo box connection that Access uses, in addition to the CurrentProject Connection.
It's not trivial, but it works quite nicely.
-
very interesting! thanks for that
martin (who will be sticking to sql server security
)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply