USing Excel Connected to Access FE to MS SQL Server BE - security (1 Viewer)

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
Hi Guys,

i have Excel connected to Access FE linked to MS SQL Server BE.
How to do proper security?

I mean Excel is connecting to Access database but how to give certain user access to Access but with password? IT is possible? If it is only VBA i will have issue because VBA is easilt broken...

The second thing. How to secure Access to MS SQL Server. I want always user to use specific credentials for him - i mean secure database access with password specific for MS SQL Server - if user wants to change something in it.

Can anybody enlight me a little topic? How did you implement solution?

Best Wishes,
Jacek
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:04
Joined
Aug 11, 2003
Messages
11,696
SQL server can be given privileges based on the Active directory user... this would auto-authenticate the user using your access or excel sheet.

If the excel sheet is (ab)using the database only to get to the sql server data, skip it and run the query straight from excel to the sql server backend.
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
hmm thank you.

I am not catching entirely.

I love have Access a FE because set up queries is easy like for a child. i do not want to resign from it.
It is possible to secure for example Access FE with password and not encode it in VBA or enforce access to show msgbox with input password when first time is used?

With active directory is very nice idea! But i assume it will work only on local company network?

Jacek
 

namliam

The Mailman - AWF VIP
Local time
Today, 17:04
Joined
Aug 11, 2003
Messages
11,696
Yes AD works (best) when within one local network... I know it can work cross network, but I wouldnt know how... I am not a dba or anything.

You can design the queryies in access then take the sql and implement the sql in the excel like a pro, without bothering about the details.

Sorry cant help you much more than this.
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
Hi namliam,

thanks!

Yes you can just use select queries from Access in Excel and sql keep in Access only.
This is nice way but there is no security with adodb connection and wondering it is possible somehow...

Jacek
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure about Excel, but in Access, you can use DSN less connection to secure your access to the SQL Server data. What do you mean by no security in adodb connection?
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
Hi theDBguy,

thanks!

For example to connect to Access from Excel you are using connection string in VBa like:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Path\To\File.accdb";Jet OLEDB:Database Password="MyPassword";

but you have to provide password so VBA code it is easy to break....
How to avoid encoding pass in VBA project and secure connection to Access?

Jacek
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:04
Joined
Oct 29, 2018
Messages
21,358
Hi theDBguy,

thanks!

For example to connect to Access from Excel you are using connection string in VBa like:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Path\To\File.accdb";Jet OLEDB:Database Password="MyPassword";

but you have to provide password so VBA code it is easy to break....
How to avoid encoding pass in VBA project and secure connection to Access?

Jacek
Hi. Like I said though, I don't do much Excel VBA, but could you do something like?

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\Path\To\File.accdb";Jet OLEDB:Database Password=GetPassword();

GetPassword() would be a function you create to hide the actual password.
 

isladogs

MVP / VIP
Local time
Today, 16:04
Joined
Jan 14, 2017
Messages
18,186
Jacek
In a separate thread, you wanted to know how to find the forgotten password of a BE file from the FE
Now you want to know how to secure the password of a database that links to both Excel & SQL Server.

Can you see the contradiction here?
You both want to know ways of obtaining the password for yourself & simultaneously to make that impossible for others.
Access is very powerful precisely because it can be linked using automation. However that inevitably means that security can never be 100%.

Using a DSN less connection to e.g. SQL Server will still mean your connection string password is 'exposed' to others to view if they know how.
You can certainly make your database more secure but you can't make it foolproof.

The only 100% secure method of doing this is to avoid connection strings completely and only link tables 'on demand' in code using disconnected recordsets. But that is a whole new level of complexity.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 19, 2002
Messages
42,970
Access is not a secure tool for either a BE or a FE. SQL Server is secure as a BE. Is this an app that runs on your own company's LAN? If it is, you should use SQL Server and Windows authentication. You can also use Windows security to prevent people from opening your BE directly by preventing them from "seeing" the path to it. Tell us what your environment is and who you are trying to keep out of the database. Keep in mind that employees who try to view/update data they are not authorized to access can and should be fired for cause.
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
The only 100% secure method of doing this is to avoid connection strings completely and only link tables 'on demand' in code using disconnected recordsets. But that is a whole new level of complexity.
thank you.

Ok so secure whole Access Databse with 128 bit encryption it is only way.
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
Pat this is general question.
How to do this properly.

I will adjust environment but i have to know what is the best.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 19, 2002
Messages
42,970
In most of my apps, I have a log on screen where the user enters a userID and password. If that is successful, the menu opens. If not, it won't open. The login form never closes. Once the login is successful, the login form hides itself before opening the menu. All the rest of the forms reference the hidden login form for security information. Essentially, the app won't work if the login form is not open so although I said that Access is not secure, this method keeps the app from actually functioning although it doesn't keep people out of the weeds. I'm in the middle of a bridge game. If you want an example, I'll post it later.
 

isladogs

MVP / VIP
Local time
Today, 16:04
Joined
Jan 14, 2017
Messages
18,186
Jacek,
In case it helps, have a look at my article discussing ways of improving security in Access databases. There is an example app accompanying the article

EDIT: I just modified the link after you had seen my post
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
hi Pat,

but it is easy to break up i think this login form?

Jacek
 

isladogs

MVP / VIP
Local time
Today, 16:04
Joined
Jan 14, 2017
Messages
18,186
Thanks. In case you missed it, I updated the link. It now points to the second part of the article which is much more detailed.
 

jaryszek

Registered User.
Local time
Today, 09:04
Joined
Aug 25, 2016
Messages
756
I like your articles Colin, they make direction where to check those security hidden methods :)
 

isladogs

MVP / VIP
Local time
Today, 16:04
Joined
Jan 14, 2017
Messages
18,186
Most of my articles were written because I kept answering the same questions in forums.
Much easier to write once and point people to the articles 😁
 

Isaac

Lifelong Learner
Local time
Today, 09:04
Joined
Mar 14, 2017
Messages
8,738
I often base my login and access levels (to see forms, tabs on tab controls, and perform certain functions) on the logged in username, which I feel is very secure if you are in any kind of tightly controlled corporate environment where the assumption that network logins are secure is a pretty safe one and where people never share machines.

Grab their username automatically when they open the db, the db always knows who they are and can look up permissions from a table, that way you don't need a username and password. Of course in some environments this wouldn't be as effective and having them type a username and password might make more sense.

One thing I haven't done perhaps as much as I should have: Looking up all kinds of this type of info when the db first opens, and keeping it open hidden, so that as they work in the db, additional and ongoing dlookups aren't required. As I always say, users generally tolerate better a bit of 'startup' wait, compared to an ongoing slowness, if the tradeoff is to be made--but I am not sure I've always followed my own rule there!

As far as SQL Server, I would always enlist the help of a DBA, unless you can do it yourself, to create groups based on AD membership, to protect server data....perhaps in addition to any security my FE might also be doing. However, if you worry about "well I want user-X to be able to edit data in this SQL table through my app, but NOT any how else"...in that case, the server-based securities don't necessarily solve the problem, and in some cases, it might feel safer to connect to sql server using SA (service account) credentials, on the fly, and don't give anyone access on their own. I've often felt this way because I worry that a user having their own full access, even though we hope they only use my app to mess with data, what's to stop them from opening Excel and creating a connection and doing whatever the heck they want? Not a lot. So sometimes the SA can be a solution.

Recently I've heard that vba project passwords are easy to bypass, which I believe since it comes from experts, but quite a few years ago on UA someone who I thought was an expert told me that they were the "strongest type of encryption" and quite secure, (granted, they were talking about .xls at the time).

I've always felt like the choice between, "use an SA account, which depends on your vba project being secure" vs. "use SQL server security, which means your users could damage things using any ODBC method" is an uncomfortable tradeoff. I think next time I do more Access development I'm going to study up on @isladogs recommended methods! :)
 
Last edited:

Users who are viewing this thread

Top Bottom