1 2 Previous Next 17 Replies Latest reply on Oct 22, 2013 8:29 PM by GlennS_3

    use encrypted essbase password in maxl script and load data from sql db

    655432
      Hello,

      I'm using encryped passwords in MaxL-scripts.
      Almost everything works: I can call calculation scripts, load master data from sql database, load data from txt-files.
      But I always get an error if I want to load data from sql database.
      The MaxL-script to load data looks like this:

      login $1 identified by $2 on $3;
      iferror 'CommandFailed';

      alter database "$4"."$5" set active alias_table "Import";

      import database "$4"."$5" data
      connect as "$6" identified by "$7" using server rules_file "B_DATA.rul" on error write to "$8";
      iferror 'CommandFailed';

      logout;
      exit;

      define label 'CommandFailed';
      exit;

      The MaxL-logfile looks like this:
      ===================
      Essbase MaxL Shell 64-bit - Release 9.3.1 (ESB9.3.1.0.0B181)
      Copyright (c) 2000, 2007, Oracle and/or its affiliates.
      All rights reserved.

      MAXL> login user identified by $key 111111 on server;

      +OK/INFO - 1051034 - Logging in user [****].+
      OK/INFO - 1051035 - Last login on Tuesday, February 02, 2010 11:15:17 AM.
      OK/INFO - 1241001 - Logged in to Essbase.

      MAXL> alter database "APP"."DB" set active alias_table "Import";

      OK/INFO - 1013273 - Database APP.DB altered.

      MAXL> import database "APP"."DB" data
      +2> connect as "dbuser" identified by "dbpwd" using server rules_file "B_DATA.rul" on error write to "/project/batch/log/load_data.err";+

      ERROR - 1242021 - (2) Syntax error near ['" identified by identified by "'].


      MaxL Shell completed


      Can anybody help ????
      Is it possible to encrypt also the password of the SQL-user ???

      best regards
      Nicole
        • 1. Re: use encrypted essbase password in maxl script and load data from sql db
          SoL
          Hi,

          I think it's possible to parametrize the password of the SQL-user too.

          Cameroon's popular *.png* file does just this. I'd want him to attach his' rather than I attaching his' :)


          - Natesh
          • 2. Re: use encrypted essbase password in maxl script and load data from sql db
            JohnGoodwin
            You can encrypt the usernames and passwords used in a maxl script (from version 9.3), this will also encrypt the SQL username and password.

            You need to run the following.

            essmsh -gk

            This will generate your public and private keys

            Then use the following

            essmsh -E c:\folder\script.mxl <REPLACE WITH PUBLIC KEY>

            so could be :- essmsh -E c:\temp\dataload.mxl 5223,1299021247

            This will create a new maxl script called dataload.mxls that will have the account information encrypted.

            To call the script you need to include the PRIVATE KEY - essmsh -D c:\temp\dataload.mxls 964450103,1299021247

            More information available at :- http://download.oracle.com/docs/cd/E12825_01/epm.111/esb_techref/maxl_invoke_encrypt.htm

            Cheers

            John
            http://john-goodwin.blogspot.com/
            • 3. Re: use encrypted essbase password in maxl script and load data from sql db
              655432
              Thanks a lot, but I know how to decrypt amd encrypt the password.
              I have got several scripts that work.
              The error occurs if I want to load data (see first message). Load master data works without error.

              best regards
              Nicole
              • 4. Re: use encrypted essbase password in maxl script and load data from sql db
                CL
                Cameroon's popular .png file does just this.
                ^^It is? I am guessing you're talking about me -- there's a Chris Cameron who posted in the past on Network54, and for all I know this board is frequented by an army of Camerons hiding behind their board handles. I hope they're all male, I don't need any more reminders about how this name is trending female.

                This one? http://1.bp.blogspot.com/_KBL50_o60_U/Su3BM8OSuzI/AAAAAAAAAFA/nFD5B_Ydb80/s1600-h/MaxL_code.PNG

                Here's the whole post on [ODI and MaxL|http://camerons-blog-for-essbase-hackers.blogspot.com/2009/11/essbase-and-odi-better-way.html]. You can ignore ODI and just focus on the MaxL scripting bit (although I strongly recommend everyone who uses ODI at all to at least think about my approach -- it was painful yet enjoyable to throw away my years of scripting code). And now I get annoyed when a client doesn't use ODI and I have to revert to my code.

                Regards,

                Cameron Lackpour

                P.S. FWIW, I do not believe you can encrypt the SQL password. Pass it as a parameter? Absolutely. It is a security hole -- you may also want to think about turning spooling off and then back on to avoid writing that to the file, although this seems a bit of overkill.
                • 5. Re: use encrypted essbase password in maxl script and load data from sql db
                  JohnGoodwin
                  CL wrote:
                  P.S. FWIW, I do not believe you can encrypt the SQL password.
                  Are you saying the maxl encrypt does not encrypt the sql username and password as it does for me or is this about encrypting a parameter.

                  Cheers

                  John
                  http://john-goodwin.blogspot.com/
                  • 6. Re: use encrypted essbase password in maxl script and load data from sql db
                    CL
                    Both.

                    What would be the point of encrypting a parameter? It would have to be encrypted at the source, and given the way MaxL parameters work, it would then be wrong (bozotheclown = !@#$FEEG -- I just made up an encryption process that totally doesn't work). If the code passed !@#$FEEG it would fail on the target side because SQL Server/Oracle/whatever wouldn't know what that is, at least within the context of an import statement.

                    And I think that only the Essbase username, password, and servername can be encrypted using MaxL. I will throw out the caveat that there is an ability to encrypt text strings (can't remember the format but it's in there) but I could never figure out how to use that against a text file. I did ask on Network54 (and maybe here?) and got no help on it. If you've figured this out I would be really interested to hear how you did it. And of course it would answer the OP's question.

                    Regards,

                    Cameron Lackpour
                    • 7. Re: use encrypted essbase password in maxl script and load data from sql db
                      655432
                      Thanks a lot, but the decryption/encryption of the SQL password is not the main problem.

                      Her again the explanation of the problem...
                      I use a encrypted essbase password and want to load data with the following maxl script:

                      MAXL-script
                      =======
                      login $1 $2 on $3;
                      iferror 'CommandFailed';

                      alter database "$4"."$5" set active alias_table "Import";

                      import database "$4"."$5" data
                      connect as "$6" identified by $7 using server rules_file "B_DATA.rul" on error write to "$8";
                      iferror 'CommandFailed';

                      logout;
                      exit;

                      define label 'CommandFailed';
                      exit;


                      The script is called through a shell-script and all parameters are correct and in the right order.
                      The following error occures:
                      ==================

                      Essbase MaxL Shell - Release 9.3.1 (ESB9.3.1.0.0B181)
                      Copyright (c) 2000, 2007, Oracle and/or its affiliates.
                      All rights reserved.

                      MAXL> login admin identified by $key 323943535929548093728382233891151942 on serverxy;

                      +OK/INFO - 1051034 - Logging in user [admin].+
                      OK/INFO - 1051035 - Last login on Tuesday, February 02, 2010 2:01:39 PM.
                      OK/INFO - 1241001 - Logged in to Essbase.

                      MAXL> alter database "XXX_TST"."TST" set active alias_table "Import";

                      OK/INFO - 1013273 - Database XXX_TST.TST altered.

                      MAXL> import database "XXX_TST"."TST" data
                      +2> connect as "sa" identified by password using server rules_file "B_DATA.rul" on error write to "D:/tmp/_load_data.err";+

                      ERROR - 1242021 - (2) Syntax error near \['" identified by identified by password using server rules_file "'].


                      MaxL Shell completed


                      Can anybody help ???? I think iit's a bug of the "import database <db> *data*..."-command.
                      A similar script to load master data does work (command: "import database <db> *dimensions*...") !


                      best regards
                      Nicole
                      • 8. Re: use encrypted essbase password in maxl script and load data from sql db
                        JohnGoodwin
                        Maybe I am missing something completely but here is the import statement after using maxl encrypt

                        import database "Sample"."Basic" data connect as $key 2854087560778724638025718258904134064140 identified by $key 1207659460023868356098914220202719697530 using server rules_file "LDDATA....

                        Ok this is based on an orginal maxl script with the username and password fixed but it still encrypting.

                        Cheers

                        John
                        http://john-goodwin.blogspot.com/
                        • 9. Re: use encrypted essbase password in maxl script and load data from sql db
                          655432
                          This is very interesting.

                          I use an encrypted password for essbase. To load master data from sql database I can use the regular sql user and sql password (I do not have to encrypt them). But to load data I HAVE to encrypt the sql user and sql password to make the maxl script work.

                          Thanks a lot - now it work's.
                          But I still think it's a bug or at least an "undocumented feature" ;-)


                          best regards,
                          Nicole
                          • 10. Re: use encrypted essbase password in maxl script and load data from sql db
                            CL
                            What am I doing wrong?

                            Unencrypted
                            login hypadmin password on demo1113 ;
                            
                            import database Sample.Basic connect as "sa" identified by "password" using server rules_file dData preserve all data on error write to "c:\\temp\\error.txt" ;
                            
                            exit ;
                            Getting the keys:
                            C:\Documents and Settings\Administrator>essmsh -gk
                            
                             Essbase MaxL Shell - Release 11.1.1 (ESB11.1.1.3.0B126)
                             Copyright (c) 2000, 2009, Oracle and/or its affiliates.
                             All rights reserved.
                            
                            
                             Public Key for Encryption: 15681,184539169
                             Private Key for Decryption: 169023681,184539169
                            
                              MaxL Shell completed
                            Encrypting the code:
                            C:\Temp>essmsh -E jgoodwin.msh 15681,184539169
                            
                             Essbase MaxL Shell - Release 11.1.1 (ESB11.1.1.3.0B126)
                             Copyright (c) 2000, 2009, Oracle and/or its affiliates.
                             All rights reserved.
                            
                            
                              MaxL Shell completed
                            Result:
                            login $key 422580061746270421247995090515063010 $key 895394371185237050739734161247995090 on demo1113;;
                            
                            import database Sample.Basic connect as "sa" identified by "password" using server rules_file dData preserve all data on error write to "c:\\temp\\error.txt" ;
                            
                            exit ;
                            Is it something in the way I called the connect statement? Did you somehow use the DATA statement to do the encryption of the SQL part?

                            Regards,

                            Cameron Lackpour
                            • 11. Re: use encrypted essbase password in maxl script and load data from sql db
                              JohnGoodwin
                              I just tried it on data load statement as that was the question was aimed at, the example I encrypted was.

                              import database "Sample"."Basic" data connect as "sqluser" identified by "password" using server rules_file "LDDATAl" on error write to "/loaderror/load_data.err";

                              Cheers

                              John
                              http://john-goodwin.blogspot.com/
                              • 12. Re: use encrypted essbase password in maxl script and load data from sql db
                                GlennS_3
                                I know this is picky, but don't you need the keyword "Data" in your import statement?

                                Import database Sample.Basic DATA
                                Could the staement be getting confused?
                                • 13. Re: use encrypted essbase password in maxl script and load data from sql db
                                  CL
                                  Ouch.

                                  Glenn (and John), you are right, it was the missing DATA statement in my import. Not to be confused with MaxL's DATA encryption keyword.

                                  Hmm, lessee, it's been two or three years that I thought it couldn't be done. (Insert eyeroll with whistling effect as OTN doesn't support this most needed emoticon). If only a dope slap emoticon was available.

                                  Thanks to both of you. :)

                                  Regards,

                                  Cameron Lackpour
                                  • 14. Re: use encrypted essbase password in maxl script and load data from sql db
                                    655432
                                    Hello again,

                                    now the script to load data runs without error (essbase password, sql user and sql password have to be encrypted !).
                                    The script to update dimensions doesn't run if essbase password, sql user and sql password are encrypted - it only runs if the sql user and sql password are given in plain text.

                                    MaxL-script to lad dimensions:
                                    ==================
                                    login $1 identified by $2 on $3;
                                    iferror 'CommandFailed';

                                    import database "$4"."$5" dimensions
                                    connect as $6 identified by $7 using server rules_file "S_TAR_L.rul",
                                    connect as $6 identified by $7 using server rules_file "S_TAR_K.rul"
                                    preserve all data on error append to "$8";
                                    iferror 'CommandFailed';

                                    logout;
                                    exit;

                                    define label 'CommandFailed';
                                    exit;

                                    Logfile (using encrypted data):
                                    ===================
                                    Essbase MaxL Shell 64-bit - Release 9.3.1 (ESB9.3.1.0.0B181)
                                    Copyright (c) 2000, 2007, Oracle and/or its affiliates.
                                    All rights reserved.

                                    MAXL> login user identified by $key 085720372819053872317872472602390510660295332 on serverxy;

                                    +OK/INFO - 1051034 - Logging in user [user].+
                                    OK/INFO - 1051035 - Last login on Tuesday, February 02, 2010 4:18:55 PM.
                                    OK/INFO - 1241001 - Logged in to Essbase.

                                    MAXL> import database "APP"."DB" dimensions
                                    +2> connect as $key 795940032459594823375385003857539044 identified by $key 085720372819053872317872472602390510660295332 using server rules_file "S_TAR_L.rul",+
                                    +3> connect as $key 795940032459594823375385003857539044 identified by $key 085720372819053872317872472602390510660295332 using server rules_file "S_TAR_K.rul"+
                                    +4> preserve all data on error append to "/project/batch/log/1000_update_otl.err";+

                                    ERROR - 1242021 - (2) Syntax error near ['$'].


                                    MaxL Shell completed


                                    Logfile (using plain text for sql user and sql password):
                                    ==================================
                                    Essbase MaxL Shell 64-bit - Release 9.3.1 (ESB9.3.1.0.0B181)
                                    Copyright (c) 2000, 2007, Oracle and/or its affiliates.
                                    All rights reserved.

                                    MAXL> login user identified by $key 085720372819053872317872472602390510660295332 on serverxy;

                                    +OK/INFO - 1051034 - Logging in user [user].+
                                    OK/INFO - 1051035 - Last login on Tuesday, February 02, 2010 5:01:40 PM.
                                    OK/INFO - 1241001 - Logged in to Essbase.

                                    MAXL> import database "APP"."DB" dimensions
                                    +2> connect as sqluser identified by sqlpwd using server rules_file "S_TAR_L.rul",+
                                    +3> connect as sqluser identified by sqlpwd using server rules_file "S_TAR_K.rul"+
                                    +4> preserve all data on error append to "/project/batch/log/1000_update_otl.err";+

                                    +OK/INFO - 1053012 - Object [DB] is locked by user [user].+
                                    +. . .+
                                    OK/INFO - 1007067 - Total Restructure Elapsed Time : [0.21] seconds.
                                    +OK/INFO - 1053013 - Object [DB] unlocked by user [user].+
                                    OK/INFO - 1241113 - Database import completed ['APP'.'DB'].

                                    MAXL> logout;

                                    User user is logged out


                                    MaxL Shell completed




                                    Can anybody help ????

                                    Nicole
                                    1 2 Previous Next