Solved

MS Access store SQL passwords in App

Posted on 2014-12-03
11
212 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
  • 3
  • 3
  • 2
  • +3
11 Comments
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
:)
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
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:
SimonAdept earned 250 total points
Comment Utility
+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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now