Security Access to SQL server

[Heythere82]

HI Everyone,

I have a split access database and I am in the process of moving the back end to SQL server.

One reason for the migration is that the back end data is not currently protected with the split access format. Basically if a user finds the back end on our network, they can easily delete everything. I am currently saving backups on another drive to mitigate this risk.

I can't password protect the back end because it causes users not to be able to use the front w/of the password.

I am just starting the learn SQL server, so I'm not sure if I would experience the same issue even after the migration. I have read that I can create user logins, but would that give them access to delete entire tables?

After the migration, I will begin building a web application to replace the Access front end.

 

[Ranman256]

You CAN attach tables WITH passwords saved.
This way users don't have to enter passwords.
The server is secure.
Everybody wins.

 

[Galaxiom]

You CAN attach tables WITH passwords saved.
This way users don't have to enter passwords.
The server is secure.
Everybody wins.

Except that finding the saved passwords isn't very difficult. And the backend file can be copied and hacked offsite.

Access back end = Clayton's security.

 

[Rx_]

Security, agree!
My preference is to use VBA code to
1. Destroy linked tables
2. Recreate linked tables with vba code for the session.
3. If the DB is on a Citrix or Remote Terminal server - limit access to only admin
4. If the DB is not on Citrix - compile the vba code HIDE the Object browser from user

I love Citrix. It provides a secure sandbox for VBA and vastly lowers the network bandwidth. - that is just a lazy programmer's preference.

 

[AccessBlaster]

Basically if a user finds the back end on our network, they can easily delete everything.

That should not be the case. SQL Server will block anyone trying to browse a SQL directory. I mean isn't that the point?

Unless you are using a "shared network drive" outside of SQL.

You could setup ODBC to use a password or Windows logon authentication.

Once you have your SQL tables linked to your frontend you will find there is enough security for most casual users.

 

[ButtonMoon]

When using SQL Server best practice is to use Windows integrated security. Don't use SQL logins or passwords. You can grant only the permissions needed for each user. They don't need to have the ability to delete or update data unless you want them to. No need to save passwords for linked tables.

 

[Rx_]

If the Front End is just the standard format where people have access to the Object Browser (tables, Queries) and such, that is good advice. But, it still won't allow someone with delete rights from exercising that right.

If the FE uses compiled code to control the user completely through forms, and limits the user to forms only (no general Access menu items), then we are looking at what level of user might exercise an unintended right.

If the standard ODBC is set up, a medium user can even use Excel to link to the ODBC on a desktop to get unintended rights.
My preference (and there are other valid options) is to use SQL Server Native Client with VBA Code. On the application open, delete all linked tables. then user VBA to re-create all the linked tables.
Noting is perfectly fool proof. However, the next level of using Citrix or Terminal Services puts a very significant layer in place.

As another safeguard, outside the nightly (or hourly backups), creating log files has proven very useful.

Just a short example where Security alone won't prevent deletions (or overwrite) : A new employee came in and was given instructions and a stack of files to update. The instructions were wrong. The new employee spend the day deleting and conducting other activities before the totally awesome DBA (Blush,... that would be me) discovered the activity. A log file provided every unique ID and field level update.
The log showed how efficient the new employee was! Plus it had an exact list of corrections to the previous day's activity.

I just missed attending a new MS presentation for AZURE using Citrix.
The security set up for using Access with Azure brings up security, but also probably offers our community some new opportunities!

 

[SQL_Hell]

Heythere82,

Did get an understanding of the possible security options available when connecting MSAccess to MSSQL or would you like additional help?

Some good replies in this thread, but ultimately we need to learn more about your environment in order to provide good advice.

Using windows authentication and the correct SQL server database roles is the most secure way, but if you don't have a domain attached SQL instance then simply using the correct SQL server roles will stop any options of deleting data or tables but you will always have a password vulnerability.

Please post back if you need more help