Avatar of RDLFC
RDLFC
 asked on

MS Access store SQL passwords in App

I have developed a MS access app with a SQL backend.   The ms access app uses recordsets to connect to the SQL backend and put or pull information.  I have the sQL login credentials hard coded into the MS Access app vba code for the app to use each time it connects to the SQL database.

my problem is that for security purposes the sql password must be changed every 30 days.  when this happens i need to update the password being used by the ms access front end.  the only way i know to do this is to reissue the updated front end with the new password built in.  

is there a method that can be used for the sql credentials to be dynamically stored and updated in the front end without having the send out a new front end.  i do not want to store the credentials in a local table because they are not secure there.
Microsoft SQL ServerMicrosoft Access

Avatar of undefined
Last Comment
Bitsqueezer

8/22/2022 - Mon
Nick67

<irony>And they're so much more secure in the code?</irony>
Is there a good reason not to use Windows Authentication with the SQL Server?
i do not want to store the credentials in a local table
So you are asking it there's a way to dynamically update the vba code for the connection string?

Yes, it isn't impossible.  You could code in the startup forms Open event to open some textfile someplace, and open the code module where the connection string is and write something new.

But you still have the pwd in plain text sitting some place.
Security through obfuscation only
Nick67

Depending on the environment, you could connect via a File DSN.
You could then update the DSN and have it pushed out via Group Policy.
But you likely aren't using DSN for the same reason you have the connection string hardcoded.
Jim Dettman (EE MVE)

It's doable, but not straight forward.  What you'd need to do is store the username/password in a table, but encrypt them when you do so.

The DB then would have the code to decrypt them and use them to establish a connection, or would update the .connect property on the fly of all the attached tables.

Jim.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

the only way i know to do this is to reissue the updated front end with the new password built in.  

If your front end distribution system is automated, ...is this really a big deal?
ex: http://autofeupdater.com/
(Update your reference copy of the Front end, and drop it into your Update Folder, then all users will automatically get the new front end the next time they open the database.)

Some Access developers routinely push out front end updates *weekly* ...no big deal...
;-)

JeffCoachman
Nick67

:)
Some Access developers routinely push out front end updates *weekly* ...no big deal...
Depending on what I am doing, it can be hourly.
I try not to need to update more than once an hour -- it means I really haven't bug-checked well enough.

And I just finished up a Q on how to roll your own deployment script here
https://www.experts-exchange.com/Database/MS_Access/Q_28572159.html#a40478812
Bitsqueezer

Hi,

in my current project I use a low privileged SQL Server user who has only the permission to execute one stored procedure. This user's password does never change as this is no security problem.

The SP gets a user/hashed password combination which is handled by the application in SQL Server tables. The SP checks the permissions of this user and if everything is OK the credentials of the SQL Server user with higher permissions are returned which the frontend uses in the connection strings. On this way this SQL Server user can change the password as often as wanted.

Using Windows authentication is of course the simplest solution which does not require to send any password as this is done through Windows login already. In my case this is not possible because of the administration overhead (long story...) so I decided to do it that way. It also offers me the possibility to separate permissions on SQL Server level and application level. The higher privileged SQL Server user can only access stored procedures to handle anything with the frontend, but he cannot access the tables directly. For this purpose I have a third SQL Server user which has database owner permissions but on the other hand is only a database user and cannot be used to login into SQL Server. This one is used to execute the stored procedures with the necessary permissions on the tables etc.

The system is a little bit more complicated as it uses a handshake to create application and user "tickets" (a GUID each) to check application permissions. But that is not important here.

Cheers,

Christian
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Simon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
RDLFC

ASKER
i have over 700 users access the ms access front end so pushing out a new version with the updated passwords is not very practical, and the autoupdater is a good idea but it will be a problem to convince the info security team to allow the program within the environment.

I like how the windows authentication method sounds but not sure how to implement it.  currently i connect to the server with the code below

if i added all the users to a AD group how can I modify the code below to authenticate the users in the group using windows authentication?

Public Const connstring = "Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=" & serverip & ";UID=" & usernamewrite & ";PWD=" & passwordwrite & ";DATABASE=SQLDatabase;encrypt=true"

Public Sub setrst()
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = connstring
    rst.CursorType = adOpenDynamic
    rst.LockType = adLockOptimistic
    rst.CursorLocation = adUseServer    
End Sub
Bitsqueezer

Hi,

simply change the connection string:

Public Const connstring = "Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=" & serverip & ";Integrated Security=SSPI;DATABASE=SQLDatabase;encrypt=true"

Open in new window


The rest depends on which permissions are assigned to the AD group on your server. The AD group must be added to the SQL Server.

Cheers,

Christian
RDLFC

ASKER
I have another question regarding using windows authentication.  Once this method is implemented I see how the database will access the SQL server but how do I stop a random user from just logging straight into the SQL server if they just happen to have SQL server management studio installed on their machine?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.