VB .net Service cannot connect to sql 2000

  • Thread starter Thread starter blurryimage
  • Start date Start date
B

blurryimage

Ok guys I have racked my brain on this one.

I have a vb .net service installed on a win2k server. I am trying to
get it to connect to sql 2000 box but I get this error:

An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)

I have remote connection turned on, on the sql2000. I have tried the
following connection strings

data source=<IP>;initial catalog=dfcdb;persist security info=False;user
id=user;password=PW;packet size=4096

data source=(local);initial catalog=dfcdb;persist security
info=False;user id=user;password=PW;packet size=4096

data source=tcp:<IP>;initial catalog=dfcdb;persist security
info=False;user id=user;password=PW;packet size=4096

data source=<IP>:1433;initial catalog=dfcdb;persist security
info=False;user id=user;password=PW;packet size=4096 (that port is
open)

The service is set to run under my log on.

I made a windows app that tests connection strings and the first 3
work. I tested the service on a local machine with a local sql 2000 and
it works like a champ.

Here is the .net code to open the connection

'1. Create a connection
Dim SQLString As String = My.Settings.SQLConnectionString
Dim cn As New SqlConnection(SQLString)

cn.Open() <-- I think it breaks here
'2. Create the command object, passing in the SQL string
Const selectString As String = "GetRules"
Dim cmd As SqlCommand
cmd = New SqlCommand(selectString, cn)
cmd.CommandType = CommandType.StoredProcedure

Dim reader As SqlDataReader = cmd.ExecuteReader()

please help I have looked everywhere
 
To be honest I really don't know much about firewalls and I think that
might be the problem. What would I need to set on it for it to work?
 
Just as a test to see if that is in fact the issue, just disable the
firewall entirely for a test - then re-enable. Once you've figured out if
that is the issue, you can do a little more research about how to allow just
the needed access through. Others know much more about this type of
security than I, so I don't want to potentially give dangerous advice.

For the quick test, if your running the firewall built into XP, you can get
to it via control panel.
 
Can you connect to the server from teh remote machine through either the
Server Explorer in VS or SQL Enterprise Manager?
 
I just ran another test and it does not make sense. I ran the service
from an outside the network machine using the public IP of the SQL
server and it runs fine. I dont understand why a pc not on the network
can connect but a service on the same machine cannt connect.

I am using internal IP in connection string for the locally ran service
and the external public IP for the service being ran outside the
network.

I dont have VS installed on the server to test its connection but I can
publish to it from VS on an external machine
 
A few things to look at:

Are you able to ping the IP address that you're using in your connection
string?
If unable to connect when running on local machine, try changing the IP in
your connection string to 127.0.0.1.
Also try changing datasource in connect string to "localhost"

I suspect that the local IP address your attempting to connect to may not be
correct. I'd double check that as well.
 
Thank you for your help on this.

I tried 127.0.0.1 and local and localhost with no luck. I also did an
ipconfig just to make sure it is the right address and it is.

I have a windows app that I made to test SQL connection strings It just
tells if the connection was made returning True or False. This program
(running as a windows app) works so I know the connection strings are
good. I run into the connection problem only when it is a windows
service.
 
I hate to say it but I'm running out of ideas. There is always the "call
microsoft" option - although probably a costly one. I did google and ran
accross a few issues that people have had, but most seem to be resolved via
SQL service packs. If I think of anything else, I'll post back. I'm also
watching this thread because now I'm both curious and stumped. Please post
the solution if you find one. Sorry I couldn't be more help -- good luck!!!
 
Just a few problems that I have ran into, it might help, and it might
not help.

1. Some web hosts, do not allow adhoc connections to databases. I'm
sure this is not your problem. But, I have experienced web hosting
that does not support this in the past.

2. I think I have received an error similiar to your when one of the
sql services on the server was not running. I think the service name
was "sql server browser". It was not running and caused that issue.

Trouble shooting:
You could check and see if you can connect to the sql server, using the
..net server explorer. If you can not connect to the server.

In the past when I had a problem and could not connect to a server, I
uninstalled sql and then re-installed it.
 
Back
Top