.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?

Answer 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.

Answer 2

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

www.connectionstrings.com