Tuesday 22 July 2014

i want to sql against e1local

this is very handy to have…  Say you’ve created an environment in planner that you want to quickly add to system or vice versa.  Perhaps you added a bunch of data sources that you don’t want to add again for all of the different datasource locations.  Well, this is easier than you think!

Just change the sys password in the e1local database and login with the tnsnames natively.

To change the password, ensure that the sqlnet.ora (C:\oracle\E1Local\NETWORK\ADMIN)  file for e1local has the following:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

then use sqlplus in C:\oracle\E1Local\bin like:  sqlplus / as sysdba

then alter user sys identified by helloWorld ;

Then in SQL Developer, you can connect to this instance.

image

Do you’re SQL.

Use a dblink to see / alter data in your main instance.

create public database link "e1prod"
connect to JDE
identified by myPass
using 'e1prod';

Then when you want to log into e1 again, remember that you:

The Oracle database SYSTEM and SYS users' passwords MUST be encrypted strings for E1 to run.

So, run imagereconfigureMSDE from the system/bin32 dir of the dep server.

image

Note that you type in the oracle password you chose earlier, and the radio buttons I have above.

5 comments:

Shane Delon said...

Hi Shannon!

Here are a couple of additional notes for your readers:

1. A local "dba" Windows group must exist and the active directory account you are using must be in that group in order to use Windows authentication as described in your post.

2. Another method (and the one I normally use) is to use the MSDEReconfigure (run as administrator) to go both directions. Select the "encrypted" radio button on the left the first time and type a password (we'll use "JDE") on the right. This will allow you to sqlplus SYS/JDE@E1Local as sysdba. When you are done, use MSDEReconfigure to encrypt the password again.

While I'm there I always create a new user for the CNC staff to use and assign DBA privs so we don't have to go through this process again...so it looks like the following:

1. Run reconfiguremsde as administrator to change the password from encrypted to JDE

2. Bring up a command prompt as administrator

3. sqlplus SYS@E1local as sysdba

4. create user JDESUPPORT identified by;

5. grant SYSDBA to JDESUPPORT;

6. grant DBA to JDESUPPORT;
exit

7. Run reconfiguremsde as administrator to change the SYS password back from JDE to encrypted.

Shane said...

Oops, I keep forgetting the greater than and less than signs don't appear. Number for should look like this (the password replaced was enclosed in special characters):

4. create user JDESUPPORT identified by passwordreplaced

Sorry for the churn.

Shane said...

for=four. Wish I could edit these things. :-)

Shannon Moir said...

Hey Shane,

Thanks for your great additions to this post [and my post about speed data selection entry], it's really appreciated. I also like the first four letters of your name!

Keep up the comments please, I'm starting to feel that I'm not alone!

Shannon




Shane said...

Hehe, you aren't alone my friend! There are those of us out here who greatly appreciate (and relate to) your blog! :-)