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.
RDLFCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
<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
Nick67Commented:
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 (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Jeffrey CoachmanMIS LiasonCommented:
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
Nick67Commented:
:)
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
http://www.experts-exchange.com/Database/MS_Access/Q_28572159.html#a40478812
BitsqueezerCommented:
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
SimonCommented:
+1 for Windows Authentication, unless there's a really strong reason not to. It means you can see (and log the activity of) each individual user from the SQL server and not just lots of connections from various workstations using the same generic login.
technet: Kerberos Authentication and SQL Server
We grant membership to AD security groups (e.g. App1_BasicUsers, App1_Admins, App1_Developers...AppXYZ_Developers) and grant those groups permissions on the relevant securables.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RDLFCAuthor Commented:
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
BitsqueezerCommented:
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
RDLFCAuthor Commented:
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?
BitsqueezerCommented:
Hi,

SimonAdept has already answered that above: You must add either the Windows user itself or (that's the normal case) an Active Directory group where the needed users would be added to.
To add a user or a AD group you must open the "Security" tab in SSMS and add the element as login user. Only the members of this group/the single user which is added here can access the SQL Server objects. Additionally you need to add the databases of this SQL Server where these AD elements can work with. So it is a two-step security: The first step is to add them as general login server, that allows to generally login to SQL Server. But without adding them also to a specific database they can do nearly nothing even with this login. Inside of each database you can then add more granual permissions, i.e. to a specific schema or by assigning a database role which already contains all the needed permissions.

Cheers,

Christian
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.