SQL Server does not exist or access denied

[RKay]

I have a Win2k server running SQL Server 2000. On that box I built a
working web service that pulls data from the database. One of the
services available simply accepts an ado.net connection string to test
if a connection can be made to the local server. I can call that web
service from any box on my network, and with the correct connection
string, I can establish a connection to the database.

However, when I copy the exact same service to a Windows 2003 server
on my network, and bring up that same service in ie6 on the server and
use the same connection string that worked everytime on every box, I
get a "SQL Server does not exist or access denied" error message which
the service sends back if it gets a System.Data.SqlClient.SqlException
error.

I've played with some of the security settings in IIS 6 but cant get
the service to work.

Any ideas or suggestions would be greatly appreciated.

Rob

 

[Patrick Olurotimi Ige]

HI Rkay..
Can you post how you are connecting to the DB in WIN2003 server?

And are u sure you have ASPNET acct in ur WIN2003K server
Patrick

 

[Kevin Spencer]

Sounds like a network issue, possibly a firewall? SQL Server talks by
default on port 1433.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[RKay]

HI Rkay..
Can you post how you are connecting to the DB in WIN2003 server?

And are u sure you have ASPNET acct in ur WIN2003K server
Patrick

here it is ...

[WebMethod]
public string Connect(string s)
{
string sReturn = string.Empty ;
System.Data.SqlClient.SqlConnection conn = new
System.Data.SqlClient.SqlConnection() ;
try
{
conn.ConnectionString = s ;
conn.Open() ;
sReturn = "The Connection worked!" ;
}
catch(System.Data.SqlClient.SqlException sex)
{
sReturn = sex.Message ;
}
catch(System.Exception ex)
{
sReturn = ex.Message ;
}
finally
{
conn.Close() ;
}
return sReturn ;
}

and my connection string is "server=athlon800 ; uid=ptadmin ; password
= Sunny15April ; database=mytestdb"

A simple windows form application that has the same code works just
fine on any box I try ... from my Win2003 server connecting to my
Win2k server that runs SQL Server 2000 it also works fine, but when I
try to run the web service with this code from my Win2003 box to the
Win2k/SQL box, thats when I get the "SQL Server does not exist or
access denied" message.

Thank you very much for your help Patrick ... it is very much
appreciated.

Rob

 

[RKay]

Thanks Kevin ... I can access the SQL Server db running on my win2k
server from any box using a Windows Form app no problem ... it fails
when I try to use a web service running on a Win2003 server to connect
into that win2k server running sql server ... and I dont have a
firewall on my win2k server. But I agree its some odd network issue
.... i just cant figure out what.

Any additional suggestions would be greatly appreciated.

Rob

 

[Kevin Spencer]

It may not be a network issue. What does your connection string look like?
Are you using a Trusted Connection? An exectuable runs under the local
System account. By default, ASP.Net runs under an underpriveleged account.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[RKay]

Thanks Kevin.

My connection string is "server=athlon800 ; uid=ptadmin ; password
= Sunny15April ; database=mytestdb"
How do i set up a trusted connection?

Rob

 

[Kevin Spencer]

Hi RKay,

I wasn't suggesting that you use a Trusted Connection. I asked if you were.
According to your Connection String, you are not. So, my next question is,
how is your SQL Server configured? Does it use Windows Authentication or
Mixed? If it doesn't use mixed authentication, you are forced to use a
Windows account to log in. If that's the case, it wouldn't matter what User
ID and Password were in the Connection String. It would try to log in as the
ASP.Net account, under which it is running. With Windows Auth only, this
could cause a problem, since by default the ASP.Net account has no SQL
Server permissions.

If indeed your SQL Server is configured to use Windows only authentication,
you would need to go into SQL Server and grant the necessary priveleges to
the account under which ASP.Net is running.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.

 

[Patrick Olurotimi Ige]

RKay,
As Kevin adviced if u are using integrated Authentication which that
means Windows Auth.
Then try using this connection string below(if ur SQLSERVER is
configured to use only Windows Auth then you need to go into SQL Server
and grant the necessary priveleges to
the account under which ASP.Net is running.
:-
"Data Source=YourSQLServerName;Initial Catalog=DatabaseName;Integrated
Security=SSPI;"

If not then include ur username and pwd as adviced in ur connection
string.
Hope this helps
Patrick

 

[RKay]

I'm using mixed authentication. Tried Integrated Security=SSPI; ...
same response.

Dropping the connection string into a simple windows form app that
tests a ADO.NET connection string works just fine ... the fail point
is when I run a web service on a win2003 box that tries to connect to
SQL Server running on a Win 2k box.

Rob

 

[Patrick Olurotimi Ige]

Rkay if i undesrtand you well
u are running web service on a win2003 box and connecting to another SQL
server running on W2k Server?
How have u configured the IIS and the SQL server correctly u are
connecting to?
Do you have access to the IIS Server?
Are they aslo using Mixed Auth?
Patrick

 

[RKay]

The box setup is correct ... win2003 box with a web service trying to
connect to a win2k box running sql server 2000. I am using mixed
authentication on the SQL Server box. I can connect to sql server from
the win2003 box using a simple windows form app that accepts a
connection sring and then attempts a connection. The fail point is
iis 6.0 on the win2003 box tries to connect to sql server on the
win2000 box. And yes i beleive it is configured properly, but then
again maybe not ...? ... exactly what do i need to configure re this?

Thank you very much for your reply Patrick... it is much appreciated.

Rob

 

[Doug McLellan]

Hello,

We just had a virtually identical problem and someone on our team
figured it out.

When you run a regular desktop app, it's running under your account
credentials, i.e. a domain user account.

However, when you run a web app, it runs by deafult under the server's
local machine account (ASPNET account usually). in windows 2003,
unlike 2000, sql requests coming from another computer without
domain-level credentials are summarily rejected! they don't ever get
passed to the sql server.

We solved this by changing the web server to run under a domain-level
account. You set this in the web.config file. you can add the tag
<identity impersonate="true"/> so that IIS runs under the credentials of
the user who's logged in and accessing it. (You may also have to go to
your website in IIS manager, and under "directory security", disallow
anonymous access.) You can probably also make set web.config so that
IIS runs under some particular domain account, but i'm not sure what the
exact syntax is.

Hope this helps -- I can attest that this problem is a huge drag.