SQLConnection.Open() in a WebService

[Stephen]

I'm experiencing a strange problem that has me baffled. I created a
webservice with a webmethod that connects to a remote MS SQL Server.
It works fine when connecting to one server (running SQL Server 7).
But it can't connect to another server (running SQL Server 2000). The
SQLConnection.Open() call results in a long pause, then the error
"could not connect to server". What's odd is that if I put the exact
same SQLConnection code into a new Windows application project it
works with either server.

What could be making the SQL connection fail when it's in a webservice
compared to a Windows app? And why only with the one server?

Any help would be greatly appreciated!

[Mohamoss]

Hi stephen
Just a note before hitting the issue , you should be using SqlConnection
with SQL server 2000 and an OleDbConnection with SQL server 7. ( I guess
you already have that , if not then try to have SqlConnection with the 2000
as this may be the problem )
Another thing you may be looking at is the credentials of the user that
access the database. If you are using windows authentication , then mostly
this is the source of the problem. You said the connection worked form
windows app but not from the service. The windows application by default
run under the credentials of the logged on user . whoever this is no the
default behavior of the service.
Normally windows services run under an account that has minimum privileges
on the system. Try to run the service under a user account that you are
logged on with , as this mostly is the cause of the problem. To do so open
the services window ( control panel ' Administered tools ' services .
right click your service and select properties , then select the logon tab
and choose this account ( you many need to enter the username and the
password ).. hope that helps

Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC

[Steve Kearney]

Thanks for the reply Mohamed. I'm using SqlConnection with the SQL 2000
server. The login is a user defined on the SQL Server:

SqlConnection mySQLConnection = new SqlConnection();
mySQLConnection.ConnectionString="data source=192.168.30.44;
uid=rocky;pwd=raccoon;database=myDatabase";
mySQLConnection.Open();

This code works fine in the Windows app with either SQL Server 7 or
2000. It also works fine in the webservice app with one server, which
happens to be SQL Server 7. It does not work with the other server,
running SQL Server 2000. I can connect to either database using
Enterprise Manager.

I looked in the Services control panel and do not see my webservice
there. I'm a little confused by your suggestion -- I didn't think
webservices were the same as other services.

Still stuck :-/

-Stephen
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mohamoss]

Hi Steve
ahaaa , sorry I thought your using windows service so all what I said about
how to make it run under your account would not valid . However, still
the source of the problem would be the same . either services ( web ,
windows ) work with a minimum privileges account . however it is a
different story if you you want to change that for the web service . if
you want to impersonate user with your web service ( so it would have
enough privileges to connect ) you would need to pass to its proxy class (
the one that you are using for reference from your application ) a
credentials object . if you want to pass the credentials of the user of the
application , you would pass the default credentials object . Take a look
at that snippet I once had
if(chkDefault.Checked){

s.Credentials = System.Net.CredentialCache.DefaultCredentials;

}
else{
//WindowsImpersonationContext impersonatedUser =
WindowsIdentity.GetCurrent().Impersonate();

//System.Net.NetworkCredential myCred1
=System.Net.CredentialCache.DefaultCredentials;
System.Net.NetworkCredential myCred = (System.Net.NetworkCredential)
System.Net.CredentialCache.DefaultCredentials;//
System.Net.NetworkCredential(txtuserid.Text,txtpas sword.Text,txtdomain.Text)
;
System.Net.CredentialCache myCache = new System.Net.CredentialCache();
myCache.Add(new Uri(s.Url), txttype.Text, myCred);

MessageBox.Show(WindowsIdentity.GetCurrent().Name) ;
MessageBox.Show(WindowsIdentity.GetCurrent().IsAno nymous.ToString());
MessageBox.Show(System.Threading.Thread.CurrentPri ncipal.Identity.IsAuthenti
cated.ToString());
s.Credentials = myCache;
}
You might need to read about the System.Net.CredentialCache a bit on MSDN

Mohamed Mahfouz
MEA Developer Support Center
ITworx on behalf of Microsoft EMEA GTSC