ERROR in libname connection in SAS EG and DI even though library was registered successfully in SMC

[tsndrct1234]

Hi SAS Community!

Need your advise in isolating the issue we encountered in our migration. So, we recently established connection/registered a new DB2 library using SAS Management Console. We can already see the tables migrated there however when testing the in SAS EG and SAS DI, we encountered an error:

What could be the issues here? Btw, I'm new to SAS MC and SAS EG so apologies if there are any incorrect terms. Really appreciate your help. Thank you!

[SASKiwi]

Did you use exactly the same details as stored in the SMC data library definition? The error is clearly username/password issue. Are you using an encoded password by any chance? If so it will start with: {SASnnn}

[tsndrct1234]

What I'm doing is I use the LIBNAME statement found on SAS Management Console:

Am I doing anything wrong? Please help me 😞

[Patrick]

@tsndrct1234 And you just copy/paste this into a SAS session. That looks right so I guess the library metadata already got invalid credentials. 

[tsndrct1234]

May I know how do I check this? Cause we tried re-establishing the connection and we can see the tables under that DB2 library so we assume it's working. This is the exact script I ran and caused the error I pasted on the original post. 

[Patrick]

---

@tsndrct1234 wrote:

May I know how do I check this? Cause we tried re-establishing the connection and we can see the tables under that DB2 library so we assume it's working. This is the exact script I ran and caused the error I pasted on the original post. 

---

You show us SAS log from an execution that clearly returns that the connection couldn't get established due to invalid credentials. Now you write you re-established the connection and it worked. HOW did you re-establish this connection? Running the exact same libname statement? Or via some other means?

And once more: If using a clear-text password ensure it's in single quotes to avoid any issues with special characters like ampersand that are valid characters for a DB2 password. 
https://www.ibm.com/docs/en/baw/19.x?topic=security-characters-that-are-valid-user-ids-passwords 

[tsndrct1234]

So sorry for the confusion here's an overview of what happened:

1. Copied the DB2 libname statement in SAS Management Console (assumed the connection is okay since I can see all the tables under DB2 library and you also said that it's correct)

2. Pasted the DB2 libname statement in SAS Enterprise Guide and SAS Data Integration Tool but then I got the error (screenshot of log on original post)

[Patrick]

---

@tsndrct1234 wrote:

So sorry for the confusion here's an overview of what happened:

1. Copied the DB2 libname statement in SAS Management Console (assumed the connection is okay since I can see all the tables under DB2 library and you also said that it's correct)

2. Pasted the DB2 libname statement in SAS Enterprise Guide and SAS Data Integration Tool but then I got the error (screenshot of log on original post)

---

SAS Management console doesn't actually establish the connection to DB2 unless you select to register/update tables. It normally just shows you what's in SAS Metadata. I would assume that you can see tables in SAS SMC because they have been registered in SAS Metadata as table metadata objects.

If above is true then the libname would have worked at one point in the past but since then the DB2 credentials have been changed.

To take SAS out of the picture and if you know the clear text password: Try to connect to the database directly via a DB client (like DBeaver or the DB2 native client). I would expect that this also doesn't work. ....or confirm the valid credentials with your SAS Admin/DB2 Admin.

[Patrick]

The error is clear: Either the user or the password are invalid.

First test: Run your libname statement directly via SAS EG/Studio. Have both the user and password in single quotes. The password can be clear text or SAS encoded.

And of course the credentials are not your SAS credentials but need to be the user/password defined on your DB2 server.