asked on

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?

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jim Dettman (EE MVE)

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.

sharris_glascol

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?

keschuster

ASKER

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

sharris_glascol

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