.NET connecting to oracle problems with the connectionstring

Question

At the moment I'm trying to make a connection to a local server. Connecting via, say, TOAD works fine. When I try to connect using .NET I get ora-12154. Which puzzles me, since I'm using the connectionstring from my TNSNAMES.ora file:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myPC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

As follows:

private string connectionString = "Data Source=(DESCRIPTION ="
    +"    (ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = myPC)(PORT = 1521)))"
    +"    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE));"
    +"User Id=sys;Password=zsxyzabc;";

Any ideas?

APC · Accepted Answer · 2010-04-22 11:05:45Z

1

You are connecting as SYS. Therefor you need to include the SYSDBA privilege:

+"User Id=sys;Password=zsxyzabc;DBA Privilege=SYSDBA;";

It is bad practice to use the SYS account for regular application work, or even regular DBA work. But it is necessary sometimes, and when it is necessary we have to connect as sysdba.

edit

It is a subtle one: you have a missing bracket at the end of the string!

private string connectionString = "Data Source=(DESCRIPTION =" 
    +"    (ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = myPC)(PORT = 1521)))" 
    +"    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = XE)));" 
                                                                  ^

Having an IDE with bracket matching (I use TextPad) is a boon in these cases.

edited Apr 22, 2010 at 11:05

answered Apr 22, 2010 at 10:35

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oxymoron Over a year ago

I tried logging in as a 'normal' user as well, with the same result.

Oxymoron Over a year ago

Oh dear... VS doesn't show missing brackets in string, so I completely overlooked it. Thank you for being sharp ;)

Paddy · Accepted Answer · 2010-04-22 10:35:53Z

0

This is a great site for sorting out your connection string issues:

www.connectionstrings.com

answered Apr 22, 2010 at 10:35

Paddy

34k15 gold badges82 silver badges121 bronze badges

1 Comment

Oxymoron Over a year ago

The connectionstring is straight from the tnsnames file

akdora · Accepted Answer · 2014-03-14 08:06:10Z

0

You may only be able to do this with the Oracle's .NET provider. That provider does support a "DBA Privilege=SYSDBA" setting in your connection string.

Why are you login with SYS user? It's dangerous, you may create a new user and connect with it. You may want to give any privileges you want. Try also this one :

string connString = "User Id=sys; Password=zsxyzabc; Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myPC)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SID=xe)))";

answered Mar 14, 2014 at 8:06

akdora

9031 gold badge9 silver badges19 bronze badges

Collectives™ on Stack Overflow

.NET connecting to oracle problems with the connectionstring

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related