Need to hard-code userid & pwd in connection string?

[Richard Black]

Hi,

I have a data project which requests a SQL server user name and password
every time it is opened, for security. However, when I write any code, I
need to specify the same user name and password in the connection string
making it visible to anyone who can get hold of the database. Are there any
variables similar to CurrentUser that I can use instead? (CurrentUser
returns "admin" as this is the project login, but not the server login.)

I am aware that I could use the ADE format to save the completed project,
however this project is likely to be distributed as an ADP file rather than
an ADE - it will likely require continuing development and customisation at
individual sites.

Any help is greatly appreciated,

Richard Black

 

[Graham R Seach]

Richard,

You can store the data in a user-defined database property, or you can store
them in the registry. In either case, if you're concerned about someone
reading the data, you can always encrypt it.

The following is a procedure you can use to store/read user-defined database
properties.

Public Function GetSetCustProp(strPropName As String, _
Optional strValue As String = "") As String
Dim prop As Property
Dim db As Database

On Error Resume Next

Set db = CurrentDb

'Attempt to get or set the property's value.
'If it doesn't exist, an error 3270 "Property not found" will occur.
If strValue = "" Then
GetSetVersion = db.Containers(1)(3).Properties(strPropName)
Else
db.Containers(1)(3).Properties(strPropName) = strValue
End If

If Err <> 0 Then
'The property doesn't exist.
On Error GoTo Proc_Err

If strValue = "" Then
'Since we're not setting the version, return nothing.
GetSetCustProp = ""
GoTo Proc_Exit
Else
'The property doesn't exist, create it.
Set prop = db.CreateProperty(strPropName, dbText, strValue)

'Append it to the collection
db.Containers(1)(3).Properties.Append prop

'Now read the property
GetSetCustProp = db.Containers(1)(3).Properties(strPropName)
End If
End If

Proc_Exit:
'Clean up
Set prop = Nothing
Set db = Nothing
Exit Function

Proc_Err:
DoCmd.Beep
MsgBox "Error " & Err.Number & _
vbCrLf & vbCrLf & Err.Description, _
vbOKOnly + vbExclamation, "Could not get/set property"
Resume Proc_Exit
End Function

To save values to the Registry, use the SaveSetting() and GetSetting()
functions. Check Acess Help for details.

If you want to encrypt the data, there are any number of encryption
algorithms available on the Internet.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

The best way to accomplish this is to store the information in the registry.
The Access Developers Handbook has functions for reading and writing
informations to and from the registry. The password should be encrypted.

 

[Tham Pek Yoong]

Sorry...I think I can't help u!