Connect as SYSDBA in Oracle

[pradana]

Hi

Is there any chance I can connect as SYSDBA in Oracle? I'm creating a Web Setup project. I tried to add 'DBA Privilege=SYSDBA' to AdoCxnAdditional column of ISSQLDBMetaData table through Direct Editor, but it returns me 'Invalid connection attribute'. Logging in as other users works fine to me (sure, bcoz they're not SYSDBA). Any idea?

Thanks in advance

[hidenori]

Since the ODBC Driver for Oracle provided from Microsoft does not support the functionality, you need to use the ODBC Driver for Oracle provided from Oracle. Please follow the steps below to change InstallShield using an ODBC driver provided from Oralce:

[LIST=1]

Go to the ISSQLDBMetaData table in the Direct Editor.

Change the column values as follows:
AdoDriverName: {Oracle in instantclient11_1}
AdoCxnServer: DBQ=
ISAttributes: 6

Rebuild your setp.

Note that the ODBC driver name may be different from "{Oracle in instantclient11_1}" on your system. Check the Microsoft Data Access - ODBC Administrator that can be launched from the Control Panel to find out what you have on your system.

Once it is done, you will be able to connect as SYSDBA if you type "AS SYSDBA" after your password such as "mypwd AS SYSDBA" in the Password field on the SQLLogin dialog. If you want to provide a dropdown comobox that has three options; Normal, SYSOPER, and SYSDBA instead of typing the keyword in the password filed, try this:

[LIST=1]

Add a ComboBox control to the SQLLogin dialog in the Dialog Editor.

Set "CONNECTAS" to the Property setting:

Add the following 3 items:
Text: "Normal", Value: "Normal"
Text: "SYSOPER", Value: " AS SYSOPER"
Text: "SYSDBA", Value: " AS SYSDBA"

Add the following event to the Next button of the SQLLogin dialog:
Event: [IS_SQLSERVER_PASSWORD]
Arguemnt: [IS_SQLSERVER_PASSWORD][CONNECTAS]
Condition: CONNECTAS<>"Normal"

Make sure that the event you added in the previous step is sequenced before the event calling the ISSQLServerValidate action.

Rebuild your setp.


Hope that helps.

[pradana]

How can I obtain/install {Oracle in instantclient11_1}? I only have {Microsoft ODBC for Oracle} in ODBC section in Control Panel.

The latest version I found in Oracle website only Oracle ODBC Drivers for Oracle 10.2
http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/utilsoft.html

Thanks.

[hidenori]

I used the Instant Client Package - ODBC for my test. The Oracle ODBC Drivers for Oracle 10.2 should work as well.

[pradana]

Oh really cool...It works! :cool: And now I can use InstantClient 11 instead of asking the user to install InstantClient 10 as you recommended at my other thread: http://community.installshield.com/showthread.php?t=184359&page=2

Thanks 🙂

[pradana]

One question: Is it possible to include this ODBC driver as a part of my installation?

[hidenori]

I think that you need to create a MSI package that will install the ODBC driver files and execute odbc_install.exe. Then create a setup prerequisite to run the MSI package.

[pradana]

Is it possible to run odbc_uninstall.exe just before the uninstallation process removes all files? How could I configure a custom action to run during uninstall?

[hidenori]

You need to set "REMOVE=ALL" to the Conditions setting for your action.

[pradana]

OK I just played around with the registry instead of running odbc_install.exe and odbc_uninstall.exe and it works.

Thanks.

[nileshrathodn]

hidenori wrote:
Since the ODBC Driver for Oracle provided from Microsoft does not support the functionality, you need to use the ODBC Driver for Oracle provided from Oracle. Please follow the steps below to change InstallShield using an ODBC driver provided from Oralce:

[LIST=1]

Go to the ISSQLDBMetaData table in the Direct Editor.

Change the column values as follows:
AdoDriverName: {Oracle in instantclient11_1}
AdoCxnServer: DBQ=
ISAttributes: 6

Rebuild your setp.

Note that the ODBC driver name may be different from "{Oracle in instantclient11_1}" on your system. Check the Microsoft Data Access - ODBC Administrator that can be launched from the Control Panel to find out what you have on your system.

Once it is done, you will be able to connect as SYSDBA if you type "AS SYSDBA" after your password such as "mypwd AS SYSDBA" in the Password field on the SQLLogin dialog. If you want to provide a dropdown comobox that has three options; Normal, SYSOPER, and SYSDBA instead of typing the keyword in the password filed, try this:

[LIST=1]

Add a ComboBox control to the SQLLogin dialog in the Dialog Editor.

Set "CONNECTAS" to the Property setting:

Add the following 3 items:
Text: "Normal", Value: "Normal"
Text: "SYSOPER", Value: " AS SYSOPER"
Text: "SYSDBA", Value: " AS SYSDBA"

Add the following event to the Next button of the SQLLogin dialog:
Event: [IS_SQLSERVER_PASSWORD]
Arguemnt: [IS_SQLSERVER_PASSWORD][CONNECTAS]
Condition: CONNECTAS<>"Normal"

Make sure that the event you added in the previous step is sequenced before the event calling the ISSQLServerValidate action.

Rebuild your setp.


Hope that helps.

Hi,
Can you guide me how to do the same thing in installanywhere??

Thanks in advance,
Nilesh Rathod.

[nileshrathodn]

pradana wrote:
How can I obtain/install {Oracle in instantclient11_1}? I only have {Microsoft ODBC for Oracle} in ODBC section in Control Panel.

The latest version I found in Oracle website only Oracle ODBC Drivers for Oracle 10.2
http://www.oracle.com/technology/software/tech/windows/odbc/htdocs/utilsoft.html

Thanks.

Hi,
Can you guide me how to connect as sysdba in oracle in installanywhere??