MS Access and hiding SQL Server Connection string

I'm building an app using MS Access as the front end and linking to SQL Server on the back end.  What I'm a little uncomfortable with is having the SQL Server credentials exposed in the code.

I'm wondering how you guys have dealt with this?
keschusterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You can distribute a .mde/accde file, which does not disclose the code and keep access to the accdb/mdb limited to trusted developers.

But to really lock things down, we use Visual Studio (vb.net) with SQL Server as a Back-End, with the connection strings/credentials stored in a separate encrypted configuration file.  Access is inherently less secure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
1. Use trusted connections instead, which relies on the users windows logon to authenticate with SQL.

2. Embed the username and password as part of the connect string for the table or ADO code and distribute as a MDE.   Someone might still glean the username password however by using a hex editor on the actual DB file.

Jim.
0
sharris_glascolCommented:
Can you post how you did this?  I am trying to do the same thing I am developing a training database where I am using Access Forms and my tables are linked to sql.  I have my pc connected via odbc connection but don't want to have to go around and connect all the pcs i install it on that way.  Can I embed some type of code in the database that looks at the sql server?
0
keschusterAuthor Commented:
This is the code I ended up with.  Created login form to select the server prod or dev -
Function DSNConnectTest(strSQLServe<wbr ></wbr>r As String, strSQLDatabase As String, strSQLUsername As String, strSQLPassword As String) As Boolean
 On Error GoTo ErrHandler:
    Dim cnn As ADODB.Connection
    Dim canConnect As Boolean


    Set cnn = New ADODB.Connection
    cnn.Provider = "SQLNCLI10" '==> Setting for SQL Server 2008
    cnn.Open "Data Source=" & strSQLServer & "; Database=" & strSQLDatabase & "; User ID=" & strSQLUsername & "; Password=" & strSQLPassword & ";"
    If cnn.state = adStateOpen Then
        gblConnectionString = "Data Source=" & strSQLServer & "; Database=" & strSQLDatabase & "; User ID=" & strSQLUsername & "; Password=" & strSQLPassword & ";"
        canConnect = True
        
        cnn.Close
    Else
        canConnect = False
        cnn.Close
    End If
    'MsgBox canConnect
    DSNConnectTest = canConnect
    Exit Function

ErrHandler:
    Resume Next
End Function

Function DSNConnect(strServer As String) As Boolean
    '//Name     :   DSNConnect
    '//Purpose  :   Used to Connect specific table from SQL Server
    '//Parameters
    '//     strSQLServer: Name of the SQL Server that you are linking to
    '//     strSQLDatabase: Name of the SQL Server database that you are linking to
    '//     strSQLUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
    '//     strSQLPassword: SQL Server user password
    
    
        Dim strSQLUsername As String
        Dim strSQLPassword As String
        Dim strSQLServer As String
        Dim strSQLDatabase As String
    
    '#########################<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>###
    'These are the connection setting to connect to SQL Server
    '#########################<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>###
       Select Case strServer
              


    '######  ########

              Case "Production"
                          strSQLDatabase = "MyDBName"
                          strSQLServer = "MyServer"
                          strSQLPassword = "ThePassWord"
                          strSQLUsername = "TheProdUserName"

              Case "Development"
                          strSQLDatabase = "MyDBName"
                          strSQLServer = "MyServer"
                          strSQLPassword = "ThePassword"
                          strSQLUsername = "TheDevUsername"

        End Select
        
            
    '#########################<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##
    'Add or modify the table you within to Link based on the connection params above
    'Add tables by copying the last CALL and modify the first and second params
    'Param 1 = the name of the table as it will be seen in MS Access
    'Param 2 = the name of the table as seen in SQL Server
    '#########################<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##########<wbr ></wbr>##
        If DSNConnectTest(strSQLServe<wbr ></wbr>r, strSQLDatabase, strSQLUsername, strSQLPassword) Then
            Call AttachDSNLessTable("tblAcc<wbr ></wbr>essLevels"<wbr ></wbr>, "tblAccessLevels", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblCla<wbr ></wbr>ims", "tblClaims", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblFie<wbr ></wbr>ldMap", "tblFieldMap", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblJur<wbr ></wbr>is", "tblJuris", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblLia<wbr ></wbr>bilityType<wbr ></wbr>s", "tblLiabilityTypes", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblMas<wbr ></wbr>ter", "tblMaster", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblNot<wbr ></wbr>es", "tblNotes", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblSta<wbr ></wbr>tes", "tblStates", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblUse<wbr ></wbr>rProfiles"<wbr ></wbr>, "tblUserProfiles", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblUCP<wbr ></wbr>AssetType"<wbr ></wbr>, "tblUCPAssetType", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblUCP<wbr ></wbr>AssetCateg<wbr ></wbr>ory", "tblUCPAssetCategory", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblNew<wbr ></wbr>AssetTypeC<wbr ></wbr>ount", "tblNewAssetTypeCount", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            Call AttachDSNLessTable("tblLog<wbr ></wbr>", "tblLog", strSQLServer, strSQLDatabase, strSQLUsername, strSQLPassword)
            
            DSNConnect = True              'MsgBox "SQL Server Has Been Relinked"
        
        Else
            
            DSNConnect = False           'MsgBox "Failed To Connect.  Contact Support."
        
        End If

End Function



'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocal<wbr ></wbr>TableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
    
    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete<wbr ></wbr> stLocalTableName
        End If
    Next
      
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(s<wbr ></wbr>tLocalTabl<wbr ></wbr>eName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append<wbr ></wbr> td
    
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
    
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

Open in new window

0
sharris_glascolCommented:
Ok I have a form that I put in this ado connection string to connect to a sql server but getting a active x can't create object at the Set Me. recordset = rs.  Here is my code where am I going wrong?

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   'Use the Access 10 and SQL Server OLEDB providers to
   'open the Connection
   'You will need to replace MySQLServer with the name
   'of a valid SQL Server
   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "GCERP"
      .Properties("User ID").Value = "sa"
      .Properties("Password").Value = "Gcdown1"
      .Properties("Initial Catalog").Value = "Gc_EMP_Data"
      .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "select * from Posistions"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.