Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Bypassing Oracle passwords

Posted on 2000-05-10
7
Medium Priority
?
357 Views
Last Modified: 2008-03-03
I have an Access database with link tables through ODBC to an Oracle database. Whenever a user runs a query that uses the link tables, they are prompted for the Oracle password.

A problem that I believe to be related (since neither was happening when I began working with this database) is that the error message, "Invalid procedure call or argument" pops up when I try to open the Linked Table Manager.

Any idea what the causes and/or solutions to these problems might be?

0
Comment
Question by:ckjim
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2798033
Hello ckjim,

Seems that linked tables have lost their connect username/password information. If you can't use linked table manager wizard, you could try refreshing links with code. Here's an example (this is air-code and I can't test this with Oracle DB right now, but the important thing is the idea) function. Copy it into module or form and execute with correct connect string. It will loop all linked ODBC tables and refresh connection.

Public Function RefreshODBCLinks(ConnectString As String)
Dim tdf As TableDef
   
    On Error GoTo LinkErr
   
    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes = dbAttachedODBC Then
            tdf.Connect = ConnectString
            tdf.RefreshLink
        End If
    Next
   
    Set tdf = Nothing
    RefreshODBCLinks = True
   
   
LinkExit:
    Exit Function
   
LinkErr:
    MsgBox Err.Description
    Resume LinkExit

End Function

Usage:
Success = RefreshODBCLinks("ODBC;DATABASE=database;UID=user;PWD=password;DSN=datasourcename")

Example:
Success = RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

Hope this helps,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2799054
I made some enchangements to previous code and tested if with ODBC linked Oracle tables. Here's the 2nd version of function which will recreate the link and saves username and password information to linked table.

Public Function RefreshODBCLinks(ConnectString As String)
Dim tdf As TableDef
Dim newtdf As TableDef
Dim tablename As String
     
    On Error Resume Next
     
    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes And dbAttachedODBC And Right(tdf.Name, 4) <> "_tmp" Then
            tablename = tdf.Name
            tdf.Name = tdf.Name & "_tmp"
            Set newtdf = CurrentDb.CreateTableDef(tablename, dbAttachSavePWD, tablename, ConnectString)
            CurrentDb.TableDefs.Append newtdf
            If Err.Number = 0 Then
                Debug.Print "Table " & tablename & " relinked."

                ' delete old link
                CurrentDb.TableDefs.Delete tablename & "_tmp"
            Else
           
                ' errors encountered, restore original link
                tdf.Name = tablename
                Err.Number = 0
            End If
        End If
    Next
     
    Set tdf = Nothing
     
End Function

Regards,
paasky
0
 

Expert Comment

by:Sladey
ID: 2800104
I would presume you checked the save password box when you attached the tables? - If not re-attach and save the password

One other solution I used on Windows 95 was when the system DSN was activated on the users PC was to edit the registry with the Oracle Pasword, so the were not asked for it.

Start Regedit

HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBC.INI
Select the DSN xxxxxx
Select the PWD field and add the password?

Close Regedit

I had a similar problem with the Linked Table Manager on one database, but no others are affected.



0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ckjim
ID: 2801467
paasky: Thanks for all the code, but I don't know where to put it. I haven't been able to find any startup sequence, although I know it has to be somewhere. Can you tell me where to look for that?

Sladey: You were right about the check box; I figured it out about 2 minutes after writing the question. As for editing the registry, there are too many machines accessing the database to go about it like that. Unless I'm misunderstanding (that's likely), it seems like I'd have to go to each machine and change its registry.

Still no luck figuring out why I can't use my Linked Table Manager for this database either.

0
 
LVL 10

Accepted Solution

by:
paasky earned 300 total points
ID: 2801504
ckjim, copy the above code into clipboard, then select Modules page from the database window and press New button. Paste the function into Module window and save it using name such as "General functions".

To run it immediatly, select from menu View | Debug Window (or press Ctrl+G) and enter to appearing box:

? RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

(remember to change the DSN and other connect information to match your enviroment)

You can also add a macro which executes this function. Set the macro action to RunCode and enter the Function name field

RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

HTH,
Paasky
0
 

Author Comment

by:ckjim
ID: 2801922
Thanks for all the help.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2801935
I hope my suggestion was useful. Good luck with your project!

Best regards,
Paasky
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question