Solved

MS Access store SQL passwords in App

Posted on 2014-12-03
11
226 Views
Last Modified: 2014-12-12
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.
0
Comment
Question by:RDLFC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 26

Expert Comment

by:Nick67
ID: 40479361
<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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40479368
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.
0
 
LVL 57
ID: 40479384
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.
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40479449
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
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40479464
:)
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40479641
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
0
 
LVL 18

Accepted Solution

by:
Simon earned 250 total points
ID: 40479677
+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.
0
 

Author Comment

by:RDLFC
ID: 40482787
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
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40482963
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
0
 

Author Comment

by:RDLFC
ID: 40489352
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?
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 250 total points
ID: 40489524
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
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question