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?