Topic
6 replies Latest Post - ‏2013-07-29T21:05:32Z by markevans
canutri
canutri
302 Posts
ACCEPTED ANSWER

Pinned topic Using 2nd database connection using jndi

‏2013-07-26T12:02:11Z |

All,

Using EGL web service v8.5 deployed to WAS v6.1

I'm getting data from multiple database servers.  The primary database is configured in EGLbld, secondary databases are fetched using SQLLib.connect.  This works as needed.  However, due to security compliance, I need to ensure the database credentials are not exposed in plain text.  As such, SQLlib.connect("jdbc/secondDB", "myUser", "mySecret") is not permissible.  It's preferable to have developers only need to know the jndi name without knowing the db credentials for the target server.  The jndi resources are set by the WAS admin on the server and not in the applications.

In java, I would instantiate a Connection object using DataSource having been set by a jndi resource via the InitialContext.lookup() method.  Can this be done similarly with EGL?  Does J2EELib.getContext() provide similar functionality.  Although I wouldn't expect to create an EGL connection like this.

Alternately, can the user and password be encrypted and supplied to the SQLLib.connect() method?  If so, how would this be done?

Thank you,

Daron

Updated on 2013-07-26T12:47:24Z at 2013-07-26T12:47:24Z by canutri
  • JBASkeen
    JBASkeen
    131 Posts
    ACCEPTED ANSWER

    Re: Using 2nd database connection using jndi

    ‏2013-07-26T14:25:21Z  in response to canutri

    Daron,

    I had this same requirement and was able to get it working. We use DB2 and Microsoft SQL Server so we switch between them during a run unit. When we switch from the default connection, which is DB2 and is defined as the default in our web.xml for the target web project, we do not supply the credentials for the SQLlib.connect() method. Then when we switch back, we don't have to either.

    To make this work I defined the database on WAS as a Data Source. Then I created a J2C authorization entry which holds the user id and password. Then, in the Data Source, assign the authentication entry to the data source for container, component or both (whatever works for your scenario) and set Default Principle Mapping for the alias. You will need to define the Data Source JNDI reference in your web.xml also so that it can resolve the reference.

    So basically, at runtime and if I am not mistaken, the app simply uses the JNDI name to refer to the WAS-defined Data Source which contains the stored and encrypted credentials and you are good to go. This works for runtime and during development I used the same JNDI name so that the same statement can be used without having to consider the environment. I accomplish this by putting an equivalent JNDI entry into the Debug Build Descriptor Java Runtime Properties page which contains a hard link to the DB2 or DW servers.

    *Caveat: Since I am using MS SQL Server and Integrated Authentication, I do not have to supply a password during development when switching to my MS connection. If I was not using Integrated Authentication then I would have to have two statements: the dev statement would have to contain the user and password for the MS connection since there is no place to specify these properties in the Debug Build Descriptors java runtime properties. The runtime statement would not have the credentials.

    I hope this helps some but I believe there are multiple ways to skin this cat. I can provide more info if needed.

    James

    RBD 8.5.1, WAS 7.0.0.19

    • canutri
      canutri
      302 Posts
      ACCEPTED ANSWER

      Re: Using 2nd database connection using jndi

      ‏2013-07-26T15:09:42Z  in response to JBASkeen

      James,

      So within a single service part function, it would be possible to:

      1. fetch data from the primary DB2 for i datasource
      2. switch to secondary MS SQL data source (without providing credentials)
      3. disconnect from secondary data source (back to primary DB2 datasource)

      I've done this before, only in the past the secondary data source was connected using SQLLib.connect(jndi, user, password).  Since then we have stopped providing credentials in the application.  Data sources are now defined on the server with J2C authentication data as you describe above.  What I'm unclear about is how to switch to the secondary data source with passing credentials.  I dont see the connect method document with only the jndi reference as a single parameter).

      Updated on 2013-07-26T15:13:40Z at 2013-07-26T15:13:40Z by canutri
      • JBASkeen
        JBASkeen
        131 Posts
        ACCEPTED ANSWER

        Re: Using 2nd database connection using jndi

        ‏2013-07-26T16:19:35Z  in response to canutri

        Daron,

        When I use the method without the credentials you pass three parameters still but leave the user id and password blank. So it looks like this: SQLLib.connect("jdbc/myJNDI", "", ""). Then in your web.xml you have references for the JNDI which will be looked up during runtime on WAS and the credentials defined on WAS are used from the J2C entry, preventing you from having to pass them. So the tricky thing for this is getting your web.xml and web-bnd.xml setup right, and getting WAS setup right. Sounds like you got the WAS part done.

        James

        RBD 8.5.1, WAS 7.0.0.19

        • canutri
          canutri
          302 Posts
          ACCEPTED ANSWER

          Re: Using 2nd database connection using jndi

          ‏2013-07-26T18:43:03Z  in response to JBASkeen

          James,

          That's just brilliant!  I wouldn't have thought of passing blanks for the user id and password.

          I had to play with it in 3 different versions of the project and got it to work, but I don't know what was the silver bullet.  I'm going to keep at it until I understand this.  I'm sure I have some polluted environment variables left over from my attempts.

          Thanks for the help.

          • JBASkeen
            JBASkeen
            131 Posts
            ACCEPTED ANSWER

            Re: Using 2nd database connection using jndi

            ‏2013-07-26T18:48:27Z  in response to canutri

            No problem. I totally understand what you mean, I did the same thing since I didn't have the exact steps of what I needed to do. I was guessing the whole way. Had to go back one-by-one and cleanup my web.xml until it broke again and then I got the big picture and got it setup correctly.

            I'm glad this worked for you!

            James

            RBD 8.5.1, WAS 7

            • markevans
              markevans
              2611 Posts
              ACCEPTED ANSWER

              Re: Using 2nd database connection using jndi

              ‏2013-07-29T21:05:32Z  in response to JBASkeen

              FYI and for those who may want to do this in the future...the following information is in the EGL helps under the compatibility considerations of the Sqllib.connect() function.    Earlier it also states the userid and password fields are required to be present.

              Compatibility

              Table 1. Compatibility considerations for connect()
              Platform Issue
              COBOL generation All sqlLib.connect() parameters, with the exception of database, are ignored.
              Java generation The Tomcat J2EE server ignores the userID and password from the sqlLib.connect() function and uses the values from its server configuration.
              J2EE To default to the user ID and password associated with the data source (defined for the JNDI name), use blanks or the empty string ("") for userID and password.