Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228

    Unanswered: Login issues with ADO

    I'm currently using Sybase ASA with an Access 2k frontend. I have a common userbase on both Access and ASA (a seperate Access app does that for me) so the user just needs to log in once (in Access) and the Access automatically connects with the given user/pass to the ODBC data sorce (TryJetAuth registry key). Now I'm calling a bunch of stored procedures via ADO. The problem I have is, I can't tell Access to use the Jet login (or at least I can't find a way ). Currently I have a seperate user account for that and the user/pass is stored in a module, but I don't really feel happy about that as some of the stored procedures shouldn't really be called by everyone able to log into the Access frontend and of course there's the security aspect in storing the user/pass in clear somewhere in the frontend (where everyone can see it with a bit of effort).

    So my question is: Is there any way to either retrieve the password of the current user or can I get Access to use the Jet login information on the ADO connection as it does on linked tables?

    Thanks in advance

    Michael Apel

  2. #2
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27

    ADO & Backend

    Hi,

    did you define / create a separate ADO connection object to retrieve information from your back end DB or are you using linked tables / pass through queries?

    Here is how I work with it:
    First I create an ADO connection which you use for the user ot authenticate. I have several pass trough queries of which the ODBC connection string is empty.
    After the user is logged on to the backend DB, I use the connection string of my ADO connection for every pass through query.


    Public con as new ADODB.connection

    Public Function LogOn(strName As String, strPaswoord As String, strDatabase As String) As Boolean
    Dim i As Integer
    Dim mConnectionstring as string

    On Error GoTo ErrorHandler

    LogOn = False
    mConnectionString = "DRIVER={Microsoft ODBC for Oracle};" & _
    "UID=" & mUserName & ";PWD=" & mPassword & ";SERVER=" & mDB & ";"

    con.ConnectionString = mConnectionString
    con.Open

    For i = 0 To CurrentProject.Application.CurrentDb.QueryDefs.Cou nt - 1
    CurrentProject.Application.CurrentDb.QueryDefs(i). Properties("Connect").Value = _
    "ODBC;" & mConnectionString
    Next i

    LogOn = True

    Exit Function
    ErrorHandler:
    LogOn = False
    msgbox Err.Number
    End Function


    hope this helps,

    anna

  3. #3
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    First of all thx for the quick reply, but it's not exactly what I was looking for.

    Let me try to explain my situation a bit further.

    I'm using a seperate ADO connection to run pass-through queries and call stored procedures as I still have some linked Access tables in my frontend, so my default workspace is still Jet .

    I wanted to use an ADO connection as it seemed as a much "cleaner" solution to me over ODBC. I also actually noticed ADO connecting a lot faster than ODBC and I can't just keep a connection open as we only have limited concurrent seats so I don't wanna spoil those for nothing. I would also prefer if the user wouldn't have to login twice. When using ODBC I can tell Access to use the Jet auth. to log in, but I haven't found a way to do the same for ADO. Getting rid of Jet auth. completely and implementing my own would be an option but that would be a quite big change in the frontend plus I don't have control over the rights o nthe linked Access tables. So what I wanted to do is either get Access to use Jet auth. info to connect to my ADO source or retrieve the password of the current logged in user.

    Regards

    Michael Apel

  4. #4
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    Hi Michael,

    The reason I use the above procedure is because it is, as far as I know, not possible to use the Acces-Jet authentication in ADO and vice versa.

    I don't think it is possible to set these parameters for a linked (pass trhough) table. Because you don't use Access as a BackEnd DB, your tables are not actually linked tables but also "PASS-THROUGH".

    I haven't been able to set the security parameters for ech table and each different user. That is why I suggested to use Pass Through queries that you use a if it were tables (SELECT * FROM ...)

    anna

  5. #5
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    mh, now you are confusing me

    I actually use both Sybase and Access as a backend db (I'd love to have not). The access tables are linked (it's not even possible to create them as ODBC pass-through tables from my knolege). The Syabase ones are pass-through. Those work fine using jet auth. If it weren't for those few Access linked tables I could get rid of jet and use my own user management. Heck, I should get rid of Access and write my own app with MFC , but I think I'm dreamin' now....

    cheers

    Michael

  6. #6
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    I think I am confused

    you are using ADO to connect to your Sybase table using a connectionstring and passing user and password.

    anna

  7. #7
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Yes, exactly. And just by looking at my code again I saw another big reason to use ADO, I'm using async queries:

    Code:
            If conADODB.State And adStateOpen > 0 Then
                conADODB.Cancel
                strSQL = "some sql"
                isRefreshing = False
                conADODB.Execute strSQL, , adAsyncExecute
            Else
                isRefreshing = False
                conADODB.Provider = adoProvider
                conADODB.ConnectionString = adoConnect
                conADODB.Open , adoUser, adoPass, adAsyncConnect
            End If
    and
    Code:
    Private Sub conADODB_ConnectComplete(ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)
    
    Dim strSQL As String
    
    Select Case adStatus
    Case adStatusOK
        strSQL = "some sql"
        conADODB.Cancel
        conADODB.Execute strSQL, , adAsyncExecute
    Case adStatusErrorsOccurred
    If Not pError.Number = adoErrorUserAborted Then
        Me.TextDatensatzanzahl = "#Fehler#"
        Call adoError(pError.Description, conADODB.Errors)
    End If
    End Select
    
    End Sub
    
    
    
    Private Sub conADODB_ExecuteComplete(ByVal RecordsAffected As Long, _
    ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pCommand As ADODB.Command, _
    ByVal pRecordset As ADODB.Recordset, _
    ByVal pConnection As ADODB.Connection)
    If Not isRefreshing Then
        Select Case adStatus
        Case adStatusOK
            Me.TextDatensatzanzahl = pRecordset.Fields(0)
        Case adStatusErrorsOccurred
        If Not pError.Number = adoErrorUserAborted Then
            Me.txtPrecision = "#Error#"
            Call adoError(pError.Description, conADODB.Errors)
        End If
        End Select
        conADODB.Cancel
        conADODB.Close
    End If
    End Sub

    does this clear things up?
    Last edited by Apel; 04-02-02 at 07:35.

  8. #8
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27

    another thought

    Why do you want to use linked tables?
    I suggest you use ADO for Access tables as well.
    This is definately better for security.

    anna

  9. #9
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Mhh, sure but how to use. Make all forms unbound and code everthing related to data yourself? Or is there a simple way?

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Apel,

    Can you tell me about the TryJetAuth registry key? I've not had reason to use it (at least, so I think), so I know nothing about it. Can you let me know its function?

    What is the problem storing the user name and password in a module? I assume you are distributing an .mde or, at least, a secured .mdb. Although not as secure as some things, I have never heard of users disassembling an .mde to gain access to a password. I suppose it could happen, but I would think that this is a remote concern.

    Let me know about that TryJetAuth key . . . I'm interested.

    Thanks.

  11. #11
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    It's not that big documented but on by default anyway
    http://www.microsoft.com/technet/tre...y/dat401ef.asp
    has has a line for it.

    It just tells Access to first try the Jet User/Pass from Jet auth. to connect to the data source if none is given in the connection string. If that fails it will promt the user for one. If you don't want that I guess you should disable this as it's a performance hit when connecting (and the first login attemp fails) as well as your users might not like their passwords being sent to the data source.

    Now to my problem:

    Actually the security isn't that big of a problem as of right now. But it can be seen that we need it soon. The frontend is currently deployed as secured mdb.
    It probably won't be too big of a security risk to store the user/pass in the frontend directly.

    The things bugging me are:

    1. I would have one common user for all of the users of the db. I need to set common permissions on all of 'em which is limiting me.

    2. When I code something I wanna do it the clean way. Even if it works it does give me a very bad feeling. I've never been a friend of workarounds.

    3. You won't need to disassemble the db to get to the user/pass. It'll stand in clear somewhere in the file. Constants are usually stored in a certain block of the file (although I don't know how Access handles it, don't really thinks it's different with it though) so it will be easy to find. If someone wants to get it he can with little effort and a bit of knolege. Not that I'm worried someone does it but I still feel very bad about it.

    4. There are some dbs being shared with our customers (currently as one way replicas, but online would be much nicer, wouldn't it). And I don't feel even worse then when storing passwords in the file. This doesn't really apply to the Access problem as it won't be an Access frontend then anymore.

    If it were me I wouldn't use Access at all (as stated above already) but I don't have the choice.

    Great forums btw,

    cheers

    Michael
    (you see me feeling at home here already?)

  12. #12
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Apel,

    Thanks.

    I've seen that article before, but never spent the time to digest it.

    Concerning issue one . . . why not use a group (or, in SQL Server lingo, 'role') in common with all these users such that you don't have to set the permissions on the individuals, just the group. If everyone logs in with the same user identification, you loose a certain level of accountability, so maintaining individual users, but setting permissions on a group level, is the way to go.

    Concerning two, three and four . . . I understand. Sometimes you have to accept these limitations. If the concern about decompiling, to reveal the passwords, is a real concern then, what I've done in the past is to store the user identification and password in the registry and read and write to those registry settings with a little encryption/decryption routine. Unless you are really concerned about this, it is more trouble than it is worth, but it is an option. I haven't investigated it, but I assume that that is they way SQL Enterprise Manager works. When you register a server, it asks you whether, or not, you want to require authentication each time you use the program. If you say no, it must record that user identification and password somewhere, I assume the registry.

  13. #13
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    228
    Sorry for my late reply, was quite busy last week.

    Mhh, yes, good idea with the groups. I had multiple groups but none with the common permissions in which everybody is. Anyway there's the prob of having a single user able to perform evererything I execute from VBA code.

    And yes, I will probably keep it stored in the code looking at the cost/result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •