AS400 Authentication Error from Excel

After resetting the AS/400 password and verifying that the profile names and passwords match on both systems, try rebooting the Windows machine.  Using the Windows userid and password is fine as long as the passwords are maintained in sync.  It is a good idea to change the passwords at the same time, and then reboot the Windows machine, since the user ID and password is cached.

It is less likely, but be aware that other mechanisms can be used to cache an AS/400 user ID and password on a Windows machine, for example, using the CWBLOGON utility that comes with IBM Client Access.  If this mechanism is in use (it usually runs from a login script or a startup registry entry, or similar user-defined mechanism), you'll need to find the mechanism used to cache the password and change it or remove it.

- Gary Patterson

Check out my EE profile:  https://www.experts-exchange.com/M_4382324.html

Minor note... You can run [CWBLOGON serverName /u userName /c], [CWBLOGON serverName /c] or simply [CWBLOGON /c] if you want to clear cached Client Access passwords. It can come in handy if you simply want to re-enter credentials for Ops Nav for example.

Not guaranteed, but often worth the minimal effort of typing on a Windows command line.

Tom

A minor note to Tom's minor note...

Handy for the duration of a Windows logon session, but won't solve the persistent problem if there is a logon script or registry entry configured to run the CWBLOGON utility each time a user logs on, which is the (relatively uncommon) case I'm concerned with in my post above.

- Gary

Thank you both for your advice.

@Gary: Is there a certain title that the registry entry might have that would be saving the information that I could look for?

I'll try clearing the cache when I can get in touch with the user, and let you guys know the result

There are lots of places in the registry that can be used to launch a program automatically:

http://www.bleepingcomputer.com/tutorials/tutorial44.html

- Gary Patterson

Hi all,

Regretfully, clearing the cache did not solve the problem, even temporarily.

Any other thoughts?

Change the connection to use a specific profile, and then provide the user's AS/400 as the profile, and "prompt if needed".  Each time the user logs on, they will be prompted once on first connection to the system,for a password.  Subsequent connections should be automatic until the user logs on again.

- Gary

Thank you Gary, I will try that tomorrow.

I finally was able to get in touch with the user to try and test the settings.

The settings appear to default to: Use Windows ID, Prompt if necessary, and use the same security as iSeries navigator, even after I'd change them administratively to try other things (such as no default id or specify the user' ID)

I did clear the passwords store in the cache, but it did not change much.

I've attached another file which I believe is a new error message that we haven't seen before since this issue occurred.

I do appreciate all your time and help Gary and everyone.
ss-error.bmp

Well, that's a classic "Bad Password" message.  A blank password was provided. (Password length=0) on an ODBC connection attempt.  Is there a profile called "user" that is somehow being used?  This message usualy lists and actual user name, not hte word "user", which leads me to believe that this is the profile being used in the connection attempt.  

IBM also has some references for this error message (CWBSY002) that may apply to you:

http://www-01.ibm.com/support/docview.wss?uid=nas1bd86e9d7a4fa8f3586256f3b0041c2a0
http://www-01.ibm.com/support/docview.wss?uid=nas14900f22587d32ed486256bdf0063a62f

- Gary Patterson

I did see that it set the length=0 and wasn't sure if that was something related to the problem (maybe the input? I saw him put the password in and there were characters in the password box) and looking at the message a little closer it looks like there's an extra space after user. There shouldn't be any profile called "user" in the system anywhere.

I had tried using the Windows ID (which it puts his username in the field already), specifying the user name (same thing), and not having a default ID (where we typed in the ID manually). I believe the error message was the same each time, or at least similar.

Thank you for the information Gary, I will take it and see where I can go from there. Will report back soon, hopefully.

Bear in mind that ODBC connections can be configured in code (Excel, for example, has VBA as it's internal macro language, and it is possible to initiate an ODBC connection in VBA without using a pre-configured ODBC data source.)

I'm starting the think that something like this happened (I''m just guessing here, but it seems to fit your symptoms):

Someone creates an Excel spreadsheet, and embeds a macro, complete with an ODBC connection string containing a generic or shared user profile ("USER", perhaps).  Maybe this user profile"USER" was initially configured on the AS/400 with a blank password.

Time passes, and your Excel user happily uses the spreadsheet.

Then, one day, the IT auditors strike!  "Here is a user profile with no password set!" they cry in alarm!  The AS/400 security administrator is aghast "How could that have happened!  That is a violation of our carefully crafted security policy!  I will fix that problem this instant!"  And disables the profile or sets a password that meets your organizations password complexity requirements, and quietly breaking any and all end user tools like this Excel spreadsheet that rely on this insecure user ID.

Now the user starts getting "Invalid User ID" or "Invalid Password" every time they use the spreadsheet, since it just keeps trying to connect with this now-bad user id and password.  Fortunately, the ODBC connection in this particular case is configured to "Prompt As Needed", so the user is shown a user ID and password box.

They enter their own, valid user ID and password, and viola!, it works.

They shut down the spreadsheet, happy that they were able to resolve the problem.

Until the next time they open the spreadsheet, and the same sequence happens all over again.

Check the spreadsheet and see if there are any likely macros in the spreadsheet.  IF you find some likely code, post it (blank out user ids, passwords, and anything confidential, oc course, and post it here.

An easy fix is to just let the user continue to log on using the appropriate credentials.  This is the safest and easiest thing to do any way, and explain that security policies have changed (if that is the case, of course).  Of course, similar problems are going to then crop up everywhere that this now-disabled user ID appears.

There are lots of things we can do from a troubleshooting standpoint, but I think that if you'll look at the macros in the spreadshhet in question, odds are you may find a winner there...

- Gary Patterson

Check out my EE profile:  https://www.experts-exchange.com/M_4382324.html

Just for grins, look and see if there is an AS/400 user profile called USER:

WRKUSRPRF USER from a green-screen session.

BTW, this would also explain the other error that that you posted above, except this time the macro or program was configured for "Do not prompt" on the ODBC connection string, which results in an internal error.

- Gary

That was a nice story Gary, gave me a few laughs. I wish that was the case though.

I just checked, but we don't have any users named USER.

I'm not sure how these spreadsheets are configured, but there are a couple of users that seem to have personalized ones they use, as is such in this case. There was an instance of this previously, where the solution was simply a password reset and they were good to go.

For what it's worth, I made sure that his AS400 and Windows passwords were synchronized. I'll see if I can check the macro settings and find something out.

Bear in mind that the profile could have been deleted instead of disabled or password-changed, so the absence of a "USER" user profile is not necessarily a guarantee.  Open the spreadsheet and take a look at any macros..

- Gary

Here are the macros from the spreadsheet. Just looking through them myself, I didn't see anything related to any sort of log in or authentication. Either way, here they are in case you're curious about them.

I'm speaking with someone about hunting down our "USER" user, so we can tell if that's the issue or not.


Sub sort()
'
' sort Macro
'

'
    Range("B2:X2500").Select
    Selection.sort Key1:=Range("B3"), Order1:=xlAscending, Key2:=Range("F3") _
        , Order2:=xlAscending, Key3:=Range("C3"), Order3:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("C1").Select
End Sub
Sub Fixit()
'
' Fixit Macro
'

'
    Range("P3:U3").Select
    ActiveWindow.SmallScroll ToRight:=5
    Application.CutCopyMode = False
    Selection.Copy
    Range("P4:U2500").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.SmallScroll ToRight:=4
    ActiveWindow.ScrollRow = 3
    ActiveWindow.ScrollColumn = 7
    Range("F3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F4:F2500").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.ScrollRow = 3
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B4:B2500").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.ScrollRow = 3
    Range("C1").Select
    Application.CutCopyMode = False
End Sub
Sub Fixit2()
'
' Fixit2 Macro
'

'
    Sheets("QOH WH31").Select
    Range("D2").Select
    Selection.Copy
    Range("D3:D4031").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
    Sheets("Action List").Select
    ActiveWindow.SmallScroll Down:=-2
    Range("C1").Select
End Sub



Sub sven1()
'
' sven1 Macro
'

'
    Range("B3").Select
    Selection.Copy
    Range("B4:B2000").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.ScrollRow = 3
    Range("F3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F4:F2000").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    ActiveWindow.ScrollRow = 3
    ActiveWindow.SmallScroll ToRight:=1
    Range("P3:U3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P4:U2000").Select
    Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("C1").Select
End Sub
Sub sven2()
'
' sven2 Macro
'

'
    ActiveWindow.SmallScroll Down:=-2
    Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.sort Key1:=Range("B3"), Order1:=xlAscending, Key2:=Range("E3") _
        , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
    Range("C1").Select
End Sub
Sub sven3()
'
' sven3 Macro
'

'
    Rows("3:2000").Select
    Range("B3").Activate
    Selection.RowHeight = 25
    Range("C1").Select
    ActiveWindow.ScrollRow = 3
End Sub

Nope, no ODBC code in here.  How do they initiate the file transfer to or from the AS/400?  There could be embedded queries, or they could be using the AS/400 Excel Add-In, or maybe running another process or program to transfer the data.

- Gary

There is the add-in for Excel. The only two options I see are transfer to/from iSeries. I'm not exactly sure where the transfer (and I suppose ultimately when the credentials come in?) is happening, since it comes up when refreshing the data for the spreadsheet.

Is there somewhere in Excel I can check the settings for add-in?

The IBM Client Access Excel Add-in rides on top of the Client Access File Transfer functionality.  Each time you do a transfer, you can either use a wizard to guide you through creating a new transfer request, or you can select an existing transfer request that is saved as a document containing all of the parameters for the transfer request (including SQL, data transformation parameters, and Connection settings, including an option for configuring what type of security to use.)

Odds are there is an existing transfer request with specific credentials of some sort saved in it.

Have the user demonstrate click-by-click the upload or download process that they are using (post screen snaps here if you like), but the key is to see if there is an existing transfer request that is being accessed.  If so, you can use the Data Transfer application to open this transfer request document and edit the security settings (don't forget to save afterward!).

Watch the user perform the transfer (or look at the documentation of they have a "cheat sheet", and see if they are selecting an existing saved transfer request.  Make a note of the name and location of the transfer request, and then use the Client Access Data Transfer To or Data Transfer From application to open and edit the transfer request's Connection settings.

Start - iSeries Access for Windows (or Client Access or System I Access or whatever you version is called) - Data Transfer To / From.
File - Open - (navigate to existing transfer request, and double-click to select it)
Once opened, select File- Properties - (Connection tab), and edit the connection settings.

- Gary Patterson

Found something interesting here Gary. When I followed your directions there, I got a new error, which is below in the attachment. The details say to contact IBM. What do you think?
internal-error.bmp

Before we go further with all this, let's make sure you are running current, patched versions of the relevant products:

What OS release and version are you running on your AS/400?


What OS cume and Database group PTF level are you running on your AS/400?


Finally, make sure you are running the right version of Client Access (at least the same level as the AS/400 OS), and that it is patched to the latest service pack:

- Gary Patterson

Sorry about taking so long to get back to you Gary, been a busy week.

I will check that information when I get the chance. I'm not sure if we would be running the most up to date version or not due to policy, but I will get back as soon as I can.

Thank you

Something to also check is to see if the data source is set up to point to the right Library as well as if that file is populated. The objects could have been moved as well. It seems like that error above is saying that object is empty.  If the Object has been moved then you'll have to modify your ODBC connection to point to the new library.  

Gary,

it appears that LICPGM is not found in the library, and I am not authorized to use WRKPTFGRP. Are there other ways to check that I'm not seeing?