Setting up a sysdba connection for Oracle Express

Hi,

I've had several connections defined for our Oracle and Sql Server databases, for a while.

But I'm drawing a blank, when setting up a connection for Oracle Express 21c. Maybe it's just a hazy Monday morning, but I can't figure out how to set up a connection for sysdba. I already have a connection defined for a specific user in XE ; it's just the sysdba user I can't figure out.

Please choose OCI for the connection type / driver. This will give you Connect As option. One of the choices is SYSDBA

Thanks. What does the tnsnames entry look like, specifically for sysdba? I can't locate any examples of this.

The attached is on a separate sandbox desktop, that connects only to oracle databases.

	SysOp wrote:
	Please choose OCI for the connection type / driver. This will give you Connect As option. One of the choices is SYSDBA

In case this is still an issue, it's unrelated to SYSDBA, SYSDBA is just an authorization setting. ORA-12560 indicates that ti cannot find local instance with default name, you didn't specify the name in TNS drop-down. If the required TNS name isn't name, please take a look at https://community.oracle.com/tech/developers/discussion/4202789/receive-ora-12560-tns-protocol-adapter-error-with-xe-18c-windows if that is your case too. Or could be this one https://www.virtual-dba.com/blog/ora-12560-tns-protocol-adapter-error/

Hi Sysop,

I don't believe there's an actual problem. What I'm asking, is if anyone has a sample TNSNAMES entry for sysdba. I can fill out entries for a user of XE, as well as users for remote databases. But I can't figure out what to put in the tnsnames.ora file, to represent sysdba.

I'm just asking for a sample.

SysOp wrote:

In case this is still an issue, it's unrelated to SYSDBA, SYSDBA is just an authorization setting. ORA-12560 indicates that ti cannot find local instance with default name, you didn't specify the name in TNS drop-down. If the required TNS name isn't name, please take a look at https://community.oracle.com/tech/developers/discussion/4202789/receive-ora-12560-tns-protocol-adapter-error-with-xe-18c-windows if that is your case too. Or could be this one https://www.virtual-dba.com/blog/ora-12560-tns-protocol-adapter-error/

Tnsnames.ora is unrelated to sysdba

TNS (Transparent Network Substrate) is networking interface for peer-to-peer connectivity where you define named endpoints. Technically you can connect to Oracle without a TNS name, using server and port or connection string like
(ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 1515))
(CONNECT_DATA =(SID = TESTDB)) there is no user name or permissions in that string.

SYSDBA is your authorization, in simple words, permission to connect with SYS admin privileges. They are unrelated concepts. Hope this helps.

Yes, of course I understand that TNSNAMES is a convenience, and ultimately dispensable. But it's a big convenience, and I use it for all connections. Not just with Sql Assistant.

But this is the first time I installed a local database with admin priviliges, so I guess I thought that sysdba was sort of equivalent to a superuser, with the rights to add users and permissions. At its core (I thought), it was still just another kind of user.

So we can't type in, e.g. GRANTs for any user in the Sql Assistant editor, because there's no way to login as sysdba? I can only do queries as a regular user, but not as SYS?

In other words, if possible I'd like to type the same commands in the editor as sysdba, that are typed in sqlplus. But I only know how to connect as a user.

SysOp wrote:

Tnsnames.ora is unrelated to sysdba

TNS (Transparent Network Substrate) is networking interface for peer-to-peer connectivity where you define named endpoints. Technically you can connect to Oracle without a TNS name, using server and port or connection string like
(ADDRESS = (PROTOCOL = TCP)(HOST = test-server)(PORT = 1515))
(CONNECT_DATA =(SID = TESTDB)) there is no user name or permissions in that string.

SYSDBA is your authorization, in simple words, permission to connect with SYS admin privileges. They are unrelated concepts. Hope this helps.

If you are able to connect as a specific UserA without changing the state of Connect As dropdown which is the same as selecting Normal, but unable to connect with the SYSDBA selected, it means that UserA doesn't have SYSDBA privileges. As simple as that. What you need in that case is to connect as SYS, enter SYS user and their password and then select SYSDBA. Once you connect as SYS, grant UserA the SYSDBA privileges

GRANT sysdba TO UserA;

After that you should be able to connect as UserA with SYSDBA privilege (kind of makes UserA same as SYS). The initial passwords for SYS and SYSTEM users get selected during Oracle XE installation. If they are lost, you can recover them using the following method

1. Login to your windows computer as a user who is member of local Windows ORA_DBA group. If you are not, add yourself to the group.
2. Locate sqlnet.ora file, in that file change or add
sqlnet.authentication_services = (nts)
Start SQLplus without initial login as
sqlplus /nolog
3. Execute "connect / as sysdba" command without quotes
4. Execute "alter user sys identified by [new password];"
5. Restore sqlnet.authentication_services to previous state, and now you can login as SYS with SYSDBA permissions.

I'm NOT having a problem connecting as sysdba. That's easy to do, with SqlPlus. I just want to set up a connection that can be used in the Sql Assistant editor, instead of having to use SqlPlus each time.

I thought that there was a sample tnsnames entry for sysdba, but that doesn't seem to be available.

We can stop here, I'm wasting your time, because I'm not expressing the question clearly. Thanks for trying to help.

SysOp wrote:

If you are able to connect as a specific UserA without changing the state of Connect As dropdown which is the same as selecting Normal, but unable to connect with the SYSDBA selected, it means that UserA doesn't have SYSDBA privileges. As simple as that. What you need in that case is to connect as SYS, enter SYS user and their password and then select SYSDBA. Once you connect as SYS, grant UserA the SYSDBA privileges

GRANT sysdba TO UserA;

After that you should be able to connect as UserA with SYSDBA privilege (kind of makes UserA same as SYS). The initial passwords for SYS and SYSTEM users get selected during Oracle XE installation. If they are lost, you can recover them using the following method

1. Login to your windows computer as a user who is member of local Windows ORA_DBA group. If you are not, add yourself to the group.
2. Locate sqlnet.ora file, in that file change or add
sqlnet.authentication_services = (nts)
Start SQLplus without initial login as
sqlplus /nolog
3. Execute "connect / as sysdba" command without quotes
4. Execute "alter user sys identified by [new password];"
5. Restore sqlnet.authentication_services to previous state, and now you can login as SYS with SYSDBA permissions.

May I ask you what you enter in Server / TNS field when you attempt to connect from SQL Assistant? I see on your screenshot you left it blank for some reason. You need to tell it where to connect to. Something needs to be entered, a valid TNS name (as referenced in your local TNSNAMES.ORA file), or a valid TNS string.

Someone explained this, on another forum. All I have to do, is log in as a dba user, as sysdba.

Everything is fine, thanks sysop.

	SysOp wrote:
	May I ask you what you enter in Server / TNS field when you attempt to connect from SQL Assistant? I see on your screenshot you left it blank for some reason. You need to tell it where to connect to. Something needs to be entered, a valid TNS name (as referenced in your local TNSNAMES.ORA file), or a valid TNS string.

Thank you for the update. That's exactly what i meant before when i suggested to login as a user with SYSDBA permissions. Glad this has been sorted out.