keschuster
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?
I'm wondering how you guys have dealt with this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
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").Va lue = "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
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
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "GCERP"
.Properties("User ID").Value = "sa"
.Properties("Password").Va
.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
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.