Keeping UID and PWD out of an ADO connection string in an ODBC DSN-less Database and a DAO cached connection?

Question

I have used Ben Clothier's suggestion from his Office Blog Power Tip (http://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/) to create a DSN-less connection with cached credentials so that users' UID and PWD aren't saved, or required multiple times, when working in the Access interface. Have others done this? If so, what has been your approach when you need to use an ADO connection instead of DOA to reach SQL from Access via VBA? How do you open a adodb connection without having to provide the User ID and Password again, or having to put it in the code? (I'm using Access 2013 frontend, SQL 2008 R2 backend, SQL Server Security) Thanks in advance!

My Cached Connection code works like this:

Public Function InitConnect(strUserName As String, strPassword As String) As Boolean
' Description:  Is called in the application’s startup 
'               to ensure that Access has a cached connection
'               for all other ODBC objects’ use.

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim strConnection As String


    strConnection = "ODBC;DRIVER=sql server;" & _
             "SERVER=******;" & _
             "APP=Microsoft Office 2010;" & _
             "DATABASE=******;" & _
             "Network=DBMSSOCN;"

    Set dbs = DBEngine(0)(0)
    Set qdf = dbs.CreateQueryDef("")
    With qdf
        .Connect = strConnection & _
             "UID=" & strUserName & ";" & _
             "PWD=" & strPassword & ";"
        .SQL = "Select Current_User;"

        Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)

   End With
    InitConnect = True

ExitProcedure:
    On Error Resume Next
        Set rst = Nothing
        Set qdf = Nothing
        Set dbs = Nothing
    Exit Function
End Function

Then when I need to access data I can do this (Note the UID and PWD are not required):

Dim dbs As DAO.Database 
Set dbs = OpenDatabase("", False, False, "ODBC;DRIVER=sql server;SERVER=*****;APP=Microsoft Office 2010;DATABASE=*****;Network=DBMSSOCN")

I can also set the ODBC connection to pass-through queries as well in Access or VBA. But these connections work only when the connection string is IDENTICAL to what was originally used in my Cached Connection code. So, when I need an ADODB connection (as it seems sometimes ADO is needed?), the string obviously isn't going to be identical.
For Example:

 Dim cn As New ADODB.Connection
 cn.Open "Provider = sqloledb;Data Source=*same as "SERVER"*;Initial Catalog=*same as "DATABASE"*;User Id=****;Password=****"

This type of connection only works if I supply a User Id and Password. How can I write it so that I don't need them? ~Thanks!

Answer 1

Although ACCESS has some weak points regarding security, you can do few things to minimize the risks. One of them would be compile the DB to ACCDE. This way VBA is compiled and not visible.

You can create a public function that returns a string

Public Function GET_CONNECTION_STRING() as STRING
' construct your connection string here with server name and password
    GET_CONNECTION_STRING = "DRIVER={" & Driver & "};PORT=" & mPort & ";DATABASE=" & mDatabase & ";SERVER={" & mServer & "};UID=" & mUser & ";PWD={" & mPassword & "};"
End Function

then create an AutoExe macro that runs when the application is opened. in your AutoExe perform refreshing links to your linked tables. something similar to what you have.

For Each tdf In db.TableDefs
   If tdf.connect <> vbNullString Then
       tdf.connect = GET_CONNECTION_STRING & ";TABLE=" & tdf.name
       tdf.RefreshLink
    End If
Next tdf

you can do the same for existing pass through queries:

For Each myQuerydef In MyDB.QueryDefs
    If Left(myQuerydef.connect, 4) = "ODBC" Then
        myQuerydef.connect = "ODBC;" & GET_CONNECTION_STRING
        myQuerydef.Close
    End If
   Next

in addition you can have some other public functions to get current logged in username. something like

public function getCrruserID() as int
   'check your public variable crr_user_id if its empty redirect to login
   if nz(crr_user_id,0) = 0 then
     'go to login and save the user id after successful login
   else
      getCrruserID = crr_user_id
   end if
end function

use simple DAO to execute sql code like

dim db as DAO.Database
set db = currentdb
dim rs as Dao.Recordset
set rs = db.openrecordset("select something from your linked table")

or

db.execute "update command", dbfailonerror

to your last question. if you save something in memory it will be destroyed once your application is closed.

EDIT: if you have more than 50 linked tables it might be not a good idea to refresh them at every startup. Instead you can create a Local table containing your [local_Appversion, isFreshInstall] and some other variables as per your need. Every time your user receives an update the freshInstall will be true and code your App to connect and refresh all tables. (just to make sure client will get uninterrupted connection)

so in your autoExe code: if its freshInstall then connect and refreshlinks if not just set the connectionString. (usually a splash screen after login to perform this action) After successful connection just update the local isFreshInstall value to false for a quicker start next time.

you can also have a dedicated menu where user can click and refresh links manually.(in case if the connection get dropped) something like

if your organisation has a domain you can allow trusted connection using windows login name good luck.