Guidance on passwords for links to SQL Server

[Awesomo]

Hi
At the moment, I only use Windows security on SQL Server. Frontends are a mix of ADE and ACCDE, using Access 2010.
However, I've recently become concerned as this means a user with Excel and ODBC knowledge get at all the tables and views. For example, all Clients.
So I'm looking for advice on the following formats

1) ACCDB
I have the tables linked via ODBC and linked table manager.
How could I make this more secure.

2) ADP
There seems to be two options here, both of which involve setting up a SQL user and storing the password in the ADP. Lets call this user "SQL_Test_User"

a) Store the password in the connection
Click Server/connection and you get the Data Link properties. I can choose Windows Security or "Use a specific name and password"
If I enter the SQL_Test_User credentials here, I get a warning that "Your password will not be encrypted before it is saved to file"

Its not clear to me how easy it will be to get the password

b) Change the connection through VBA code
On the Microsoft site, there is an example of storing the connection name and password in code. I will only be distributing the compiled ADE, how secure would this be?

https://support.microsoft.com/en-us...-the-connection-of-a-microsoft-access-project



I realise there may not be a right or wrong answer here, but I am interested in others opinions and how this issue is tackled by others.
Thanks

 

[Ranman256]

The ACCDE is compiled and cannot be read. The password is not visible.
Do the users not have any work to do except to try and decrypt the password out of the ACCDE? Who has time?

 

[Awesomo]

Ranman - do you mean the password in not visible in both 2a and 2b above?

 

[CJ_London]

@Ranman, don't quite agree.

with .accde, ensure the password is not hardcoded in vba but goes through manipulation first (e.g. using a hash function). In the OP example, not sure if passing as a parameter is sufficient. All hardcoded text values are visible to some extent in .accde

If you open a .accde in notepad and find 'P W D' (note the spaces) you will see what I mean

 

[Awesomo]

Good point. I could use 2b, and just store the encrypted password in code. And encrypt the user name as well.