UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Persistent Connection With Encrypted Backend, Access 2013    
 
   
fitzdesignz
post Jul 22 2019, 03:54 PM
Post#1



Posts: 67
Joined: 14-June 18



I have a split access DB with a mod that fires at launch to establish a persistent connection.

I just recently encrypted my Back-end and re-linked the tables. Now the persistent connection fails with run-time error 3031 - Not a valid password.

Do I really need to put the PWD= in the connection string? Doesn't this create a security issue with the password written in the code?

This is a medical application so security is at the forefront...

I'm using the following code:

https://www.fmsinc.com/microsoftaccess/Perf...edDatabase.html

CODE
Sub OpenAllDatabases(pfInit As Boolean)
  ' Open a handle to all databases and keep it open during the entire time the application runs.
  ' Params  : pfInit   TRUE to initialize (call when application starts)
  '                    FALSE to close (call when application ends)
  ' Source  : Total Visual SourceBook

  Dim x As Integer
  Dim strName As String
  Dim strMsg As String

  ' Maximum number of back end databases to link
  Const cintMaxDatabases As Integer = 2

  ' List of databases kept in a static array so we can close them later
  Static dbsOpen() As DAO.Database

  If pfInit Then
    ReDim dbsOpen(1 To cintMaxDatabases)
    For x = 1 To cintMaxDatabases
      ' Specify your back end databases
      Select Case x
        Case 1:
          strName = "H:\folder\Backend1.mdb"
        Case 2:
          strName = "H:\folder\Backend2.mdb"
      End Select
      strMsg = ""

      On Error Resume Next
      Set dbsOpen(x) = OpenDatabase(strName)
      If Err.Number > 0 Then
        strMsg = "Trouble opening database: " & strName & vbCrLf & _
                 "Make sure the drive is available." & vbCrLf & _
                 "Error: " & Err.Description & " (" & Err.Number & ")"
      End If

      On Error GoTo 0
      If strMsg <> "" Then
        MsgBox strMsg
        Exit For
      End If
    Next x
  Else
    On Error Resume Next
    For x = 1 To cintMaxDatabases
      dbsOpen(x).Close
    Next x
  End If
End Sub

This post has been edited by fitzdesignz: Jul 22 2019, 04:01 PM
Go to the top of the page
 
theDBguy
post Jul 22 2019, 04:06 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,078
Joined: 19-June 07
From: SunnySandyEggo


Hi. To answer your question, yes, the connection string needs to have the password included. If this is for an extremely sensitive information, then perhaps Access is the wrong container for your data. Is the option to use SQL Server available to you?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
isladogs
post Jul 22 2019, 04:13 PM
Post#3


UtterAccess VIP
Posts: 1,557
Joined: 4-June 18
From: Somerset, UK


Agree with the suggestion to use SQL Server.
However, you might find these articles useful: Improve Security in Access Databases

--------------------
Go to the top of the page
 
fitzdesignz
post Jul 22 2019, 04:15 PM
Post#4



Posts: 67
Joined: 14-June 18



Okay,
In the mean time are you help direct me where I would put the PWD= in this code. so far my efforts have failed.

I'm taking a gander here... maybe I could store the password in a HASHED field and call it???
Go to the top of the page
 
fitzdesignz
post Jul 22 2019, 04:26 PM
Post#5



Posts: 67
Joined: 14-June 18



I got it

CODE
Set dbsOpen(x) = OpenDatabase(strName, False, False, "MS Access;PWD=password")


Thanks for your input and help.

What are your thoughts about storing the Password in a Hashed field? I don't need NSA level security but something better then having the password written in the code...
Go to the top of the page
 
theDBguy
post Jul 22 2019, 04:37 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,078
Joined: 19-June 07
From: SunnySandyEggo


Hi. Glad to hear you got it sorted out. Again, though, if you're worried about leaking patient information, why would you still use Access?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
WildBird
post Jul 22 2019, 04:44 PM
Post#7


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


As has been said, SQL is better than Access of course, but to enable you to have a bit of security through obscurity, you can do something like this.

Create a new database. have one table in it, name the table tluFileVer, and have 2 columns FileVerID, FileVer.

You can write code, or find some that will populate this with a list of random words. Basically, fill it with possible passwords. More the better. 1 of these will be the password you will use.

Hide the table in the database.

Now, link to this table. Hide it in the navigation. In file explorer, hide the database.

Then you can have a bit of code like this.

CODE
Function GetFileVer(ByVal intVersion As Integer) As String
'Date:          Wednesday, 13 March 2019 9:10:56 AM
'Author:        Stephen Cooper
'Email:         XXXXX@xxx.com
'Ph:
'In parameters
'Output
'Description:   Will return the value for the corresponding file version from the file ver lookup table
'Calls:
'Notes:
'Example:

On Error GoTo HandleError:

GetFileVer = ""

Dim intMouseType As Integer
Dim strErrorMsg As String
Dim varReturn As Variant
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

intMouseType = Screen.MousePointer

DoCmd.Hourglass True

Set db = CurrentDb

strSQL = "SELECT FileVer FROM tluFileVer "
strSQL = strSQL & "WHERE FIleVerID = " & intVersion

Set rst = db.OpenRecordset(strSQL)

If Not rst.EOF And Not rst.BOF Then
    GetFileVer = rst.Fields("FileVer").Value
End If 'Not rst.EOF And Not rst.BOF

ExitHere:
On Error Resume Next
'Close all recordsets etc here
varReturn = SysCmd(acSysCmdClearStatus)
Screen.MousePointer = intMouseType
db.Close
Set db = Nothing
Exit Function

HandleError:
Select Case Err.Number
Case Else
    'LogError "GetFileVer|" & CurrentProject.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    GetFileVer = ""
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select

End Function


Replace
CODE
Set dbsOpen(x) = OpenDatabase(strName, False, False, "MS Access;PWD=password")


CODE
dim strFileVer as string
strFilever = GetFileVer(15)

Set dbsOpen(x) = OpenDatabase(strName, False, False, "MS Access;PWD=" & strFilever)


Far from perfect, but is another layer of obscurity.

Means you need to find 2 files, and it is written in plain code. I avoid using the word password if possible.

Above example assumes you have at least 15 records in the other database.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
GroverParkGeorge
post Jul 23 2019, 10:46 AM
Post#8


UA Admin
Posts: 35,532
Joined: 20-June 02
From: Newcastle, WA


The best analogy I have heard regarding security is that it’s sort of like putting your valuables in a safe. It seems secure, but if you put that safe on the front lawn, all the bad guy has to do is load it in a pickup truck and haul it away to break at their leisure.

That’s MS Access.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
WildBird
post Jul 23 2019, 04:18 PM
Post#9


UtterAccess VIP
Posts: 3,594
Joined: 19-August 03
From: Auckland, Little Australia


Ha, I like that analogy George.

The example I showed at least has the password not stored in the file, it requires someone to have 2 files, so if someone drives off with the safe, they will have to come back and find another one. Slows them down a bit.

As an 'alarm', I put code in the other database storing the passwords that emails me if it is opened.

I take my home security training from watching Home Alone :-)


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
isladogs
post Jul 24 2019, 02:06 AM
Post#10


UtterAccess VIP
Posts: 1,557
Joined: 4-June 18
From: Somerset, UK


Excellent analogy George. I'm going to steal that....

@fitzdesignz
You might also be interested in this example database Encrypted Split No Strings Database I created in response to a similar question. As the title says, in addition to encrypting the BE file with password, the BE data is encrypted using the RC4 cipher so if the BE is 'stolen' it is unreadable.

There are no permanent links to the BE so no connection strings.
However it is fully functional for (authorised) end users.
Decrypted versions of the BE tables are linked on demand in code when forms and reports are loaded so forms are fully editable.

Together with other security measures described in the same article, its about as secure as a split Access dB can ever be.
The downside is that unbound forms are required so development time is significantly increased.
Have a look and see if the idea is any use to you.

--------------------
Go to the top of the page
 
DanielPineault
post Jul 24 2019, 05:16 AM
Post#11


UtterAccess VIP
Posts: 6,774
Joined: 30-June 11



The password is required to open the BE since you have secured it. That said, Access is not secure enough to house sensitive information and you need to switch to a more secure, server based, RDMS.

As for your current issue, simply create a bound form to a bogus table that you create for this purpose, and open it in hidden mode at the startup of your database. This way you don't need to store the password in the code. See: http://www.devhut.net/2012/09/29/ms-access...split-database/

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd August 2019 - 03:20 PM