Accessing the M2M Database Using VBA.

A couple of weeks ago, someone sent me some VBA code which was giving them fits. I often receive requests like this and am glad to help when I can. I came across this bit of code:

Private Function fConn() As ADODB.Connection
Dim oCn As New ADODB.Connection
Dim sCnStr As String
    sCnStr ="uid=sa;pwd=HisPassword;" _
    & "driver={SQL Server};" _
    & "server=HisSQLServer;" _
    & "database=M2MData01;" _

The code above has the following obvious problems:

  1. This user is still using the SA login. This is a tremendous security risk.
  2. The password, database name, and server are directly identified in the code. The M2VEvents.Prj file can be read by text editors, and this is a recipe for disaster. I know there are other ways for an intruder to obtain this in the M2M environment, but I still feel it’s important to lessen those risks in any way we can.
  3. If you change your database login names or passwords, your VBA code will need to be changed as well.

I thought it would be a good idea to demonstrate the way I attach to the M2M Database, and I believe it is also the method currently used by the Technical Consultants at Made2Manage as well.

My current method involves creating a module in VB called “DefaultModule” and inside I place the following code:

Public Function GetCs1() As String
    Dim SQLUser As String
    Dim SQLPass As String
    Dim SQLServer As String
    Dim SQLDB As String
    SQLUser = Eval("FDecrypt(ALLTRIM(ocompany.fcsqluser), oSession.cEncryptKey)")
    SQLPass = Eval("FDecrypt(ALLTRIM(ocompany.fcsqlpass), oSession.cEncryptKey)")
    SQLServer = Trim(Eval("ocompany.fcsqlserver"))
    SQLDB = Trim(Eval("ocompany.fcsqldb"))
    GetCs1 = "DRIVER={SQL SERVER};DSN='';SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPass & ";DATABASE=" & SQLDB & ";Connection Timeout=120"

End Function

Notice that none of the information is hard coded, it is all obtained from the M2M environment. Therefore, it’s harder for an unauthorized person to extract it, and if you change your database login and password, the change is automatically handled by your code. However, for some inexplicable reason, M2M can drop the information between screens. To remedy this I include the following in the INIT event of each screen using VBA:

Public Function INIT() As Boolean

    If Trim(connstr) = "" Then
        connstr = GetCs1()
    End If
    INIT = True
End Function

This code is cleaner, more secure, and just plain easier to understand. In the next post, I will share a small project that shows how to draw controls on M2M forms as well as manipulate the database directly.

Anybody have a different method or anything else to add?

Related posts:

2 comments to Accessing the M2M Database Using VBA.

  • Scott

    David geve me the above code a while back and I have modified it to:

    CS = “DRIVER={SQL SERVER}” & _
    “;DSN=”” & _
    “;SERVER=” & Trim(Eval(“ocompany.fcsqlserver”)) & _
    “;UID=” & Eval(“FDecrypt(ALLTRIM(ocompany.fcsqluser), oSession.cEncryptKey)”) & _
    “;PWD=” & Eval(“FDecrypt(ALLTRIM(ocompany.fcsqlpass), oSession.cEncryptKey)”) & _
    “;DATABASE=” & Trim(Eval(“ocompany.fcsqldb”)) & _
    “;Connection Timeout=120”

    The formatting is a little off due to the length limit on the site but you should get the idea. This method requires less code and is more effiecient because it does not use nearly as many variables. I think that it may also be a little easier to understand and read although finding errors with it might be harder. That however should not be an issue since the code is simply cut and paste and should rarely if ever have to be altered.

  • Ray Collazo

    You were asking about how to decrypt the initials, username, and password from utuser. By jo, You found it!!

    Heres the select I constructed using the Fdecrypt function you showed.

    select FDecrypt(ALLTRIM(fcinitials), oSession.cEncryptKey) as initials, FDecrypt(ALLTRIM(fcuname), oSession.cEncryptKey) as username, FDecrypt(ALLTRIM(fcupass), oSession.cEncryptKey) as passwd from utuser into cursor tempcursor

    Have fun!

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>