Error accessing Oracle database from a web service

[Jeff]

Has anyone had any luck accessing an Oracle database from a web service?

I have a C# DLL with various code to query an Oracle database. If I call
the methods in this DLL from a Windows Forms application everything works
just fine. However, if I call the methods in this DLL from a web service, I
receive the following error when the database connection is opened:

Error: ORA-12154: TNS:could not resolve service name

Does anyone know how to resolve this? It seems strange that it works when
called from a form but fails when called from a web service.

--- Thanks, Jeff

 

[Nicholas Paldino [.NET/C# MVP]]

Jeff,

By default, ASP.NET runs under a local system account named ASPNET,
which has no access to the network. You are probably failing because of
this. What you have to do is configure the web app so that it runs with the
user credentials of someone who can connect over the network to the
database.

This is why this works in a Windows Forms app, and not in a Web app,
because the user (you) that runs the Windows Forms app has rights to access
the network.

Hope this helps.

 

[Jeff]

By default, ASP.NET runs under a local system account named ASPNET,

which has no access to the network. You are probably failing because of
this. What you have to do is configure the web app so that it runs with the
user credentials of someone who can connect over the network to the
database.

Thanks for the info. but I'm afraid that didn't resolve my issue.

The odd thing is that my web service also makes calls to a MS SQL Server
without any problems. The logon information is embedded in the connection
string for both the Oracle connection as well as the MS SQL Server
connection. The only difference is that the MS SQL Server connection works
and the Oracle connection doesn't.

As a test I also added the "ASPNET" user to the "Local Administrators" group
but that had no effect.

Anyone have any other suggestions?

--- Thanks, Jeff

 

[Nicholas Paldino [.NET/C# MVP]]

Jeff,

Where is the SQL server located for your web service? Also, what are
the connection strings that you are using for each? You will need to
provide a little more information.

The fact that it runs in a Windows Forms application indicates that it
is a authorization issue of some sort (because the ASPNET application and
Windows Forms applications, if running the same code, differ in the security
context they are using).

Do not add the ASPNET account to the Local Administrators group. You
are just asking for trouble if an anonymous user hacks into the application,
because that user is now elevated to an administrator of the local machine,
and can cause some damage.

I would create a user on the network with limited rights (enough rights
to access the servers you need across the network) and then have the ASP.NET
application impersonate that user.

 

[Jeff]

See comments in-line below...

Where is the SQL server located for your web service? Also, what are
the connection strings that you are using for each? You will need to
provide a little more information.

The MS SQL Server and Oracle server are both located on separate servers
from my development PC. The connection strings are as follows (watch for
line wrap):

// Oracle connection string (User ID and PWD masked)
oOracleConn.ConnectionString = "Data Source=NESB;User
ID=xxxxxx;Password=xxxxxx";

// MS SQL Server connection string (User ID and PWD masked)
connectionString = "packet size=4096;User ID=xxxxxx;Password=xxxxxx;data
source=\"NNOMA-SQAPP01D\\NNOMASQAPP01D\";persist security info=False;initial
catalog=DailyPlan";

The fact that it runs in a Windows Forms application indicates that it
is a authorization issue of some sort (because the ASPNET application and
Windows Forms applications, if running the same code, differ in the security
context they are using).

I would agree; it just seems odd that MS SQL Server doesn't exhibit the same
issue.

Do not add the ASPNET account to the Local Administrators group. You
are just asking for trouble if an anonymous user hacks into the application,
because that user is now elevated to an administrator of the local machine,
and can cause some damage.

I only did this on my development PC as this is where I am also running IIS
and the web services for development/testing. Being that it didn't work
anyway, I've already removed it from the group.

I would create a user on the network with limited rights (enough rights
to access the servers you need across the network) and then have the ASP.NET
application impersonate that user.

What's the best (if not only) way to have an ASP.NET application impersonate
a user?

--- Thanks, Jeff

 

[Nicholas Paldino [.NET/C# MVP]]

Jeff,

There are two ways to impersonate a user in an ASP.NET application. The
first is with the web.config file. You can place an <identity> tag in the
<web.config> section and set the user that you want the requests to run
under.

The second would be to actually call the LogonUser API and then pass the
handle returned to the static Impersonation method on the WindowsIdentity
class.

 

[Jeff]

There are two ways to impersonate a user in an ASP.NET application.
The

first is with the web.config file. You can place an <identity> tag in the
<web.config> section and set the user that you want the requests to run
under.

Nicholas,

Thanks, the "identity" tag does the trick. I'm not too keen on placing an
ID/PWD in clear text but I noticed in the docs that you can encrypt the
values and put them in the registry.

Do you know if it's possible to grab a user's Identity from a Windows Forms
application (assuming they're logged onto a Windows NT domain) and somehow
pass that to the web service and use that? Since the users of this
application will be running a Windows Forms app via "no-touch" deployment,
it would be nice if I could just somehow pass their credentials along.

--- Thanks, Jeff