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 上一个 下一个