Best Practices for Preventing Unauthorized Access to SQL Server in Access Applications

[gregbowers]

Hello

I'm seeking advice on implementing best practices for preventing unauthorized access to a SQL Server database within Access applications. Here's the scenario:

Background:

• We have an Access application that connects to a SQL Server database backend.
• Users authenticate through the Access application using their own credentials.
• We want to restrict access to the SQL Server database to only authorized users, preventing unauthorized access via other means.

Questions:

1. What are the recommended methods for ensuring that only authorized users can access the SQL Server database?
2. Is it possible to implement user-level security within SQL Server to control access at a granular level?
3. Are there any specific security features or settings in Access or SQL Server that we should be aware of and utilize?
4. How can we monitor and audit access to the SQL Server database to detect any unauthorized attempts or activity?
5. Are there any common pitfalls or mistakes to avoid when implementing security measures in Access applications with SQL Server backends?

Any insights, tips, or best practices from your experiences would be greatly appreciated.

Thank you in advance for your assistance!

 

[arnelgp]

did you just give advice here:
Missing SQL Server Logs | Access World Forums (access-programmers.co.uk)

I am sure you can work it out.

 

[Pat Hartman]

To prevent unauthorized usage of SQL Server from outside of your application, your app needs to use credentials unknown to the user. Simply passing through the credentials used to log in to your application or using Windows authentication leaves an actual security gap.

I'm sure there are multiple options but one that I've used is to use the user's Network login as the login ID but have the Access application generate a password known only to the Access app and the process used by the DBA to create new users. This is of course, more work for the DBA because he needs to be involved as each new user is added. You don't want to actually store the passwords in your app because that also leaves a vulnerability so I created a small app that used information based on the user's HD, Motherboard, and his Network ID. The DBA used the app to generate the password when he created the account and my app used the logic when logging in.