VS 2003 + opening a connection to SQL SERVER 2000 database

[Jon Delano]

Hey all

I am building a new computer.
I have Windows XP Pro with SP2 and all the updates.

I installed VS 2003.
Then copied a project from my old computer to the new one.

When I try and run the project .. it errors out saying it "sql server does
not exist or access denied"

The connection string is obviously the exact same as when it ran on the old
computer ... as all the files came from the old computer ... where the
project works just fine.
The MSSQL server is running on a seperate computer all together.

I can open query analyzer and access the database, no problem. On the new
machine.
I can even use Tools .. Connect to Database ... in VS 2003 IDE and that
works ...

Just in my vb.net code is it failing .. and again .. it work just fine on
the old development machine.


Any ideas ??

Thanks
Jon

 

[Rick]

what is your string connection?

 

[Greg Burns]

Is this a web app? What is does your connection string look like? Are you
connection to SQL using integrated security? Is it using trying to use your
local ASPNET account?

Greg

 

[Greg Burns]

Sorry for the bad grammar. :^) Let's try that again.

Is this a web app? What does your connection string look like? Are you
connecting to SQL using integrated security? Is it trying to use your local
ASPNET account?

 

[Jon Delano]

Hello Thanks all for the quick replies ...

Here is the connection string as it sits in my web config file :
<add key="RepositoryConnectionString"
value="server=server;uid=sa;pwd=password;database=ShepherdRepository;Connect
Timeout=120" />

Here is the code that is running ...

If Not IsPostBack Then

Dim cmdCheckRepositoryAvailability As New SqlCommand
Dim rdrCheckRepositoryAvailability As SqlDataReader
Dim Conn As SqlConnection

Try
' check the flag for repository availability
Dim SQL As String
Conn = New
SqlConnection(ConfigurationSettings.AppSettings("RepositoryConnectionString"))
Conn.Open()

SQL = "Select RepositoryAvailable from tblProperty"
cmdCheckRepositoryAvailability = New SqlCommand(SQL, Conn)
rdrCheckRepositoryAvailability =
cmdCheckRepositoryAvailability.ExecuteReader

If rdrCheckRepositoryAvailability.Read Then
If rdrCheckRepositoryAvailability("RepositoryAvailable")
= 0 Then
' its not available
txtUserName.Enabled = False
txtPassword.Enabled = False
btnLogin.Enabled = False

Label1.ForeColor = Color.Blue
Label1.Font.Bold = True
Label1.Font.Size = FontUnit.Medium
Label1.Text = "<center>The repository is unavailable
at this time.</center>"
End If
End If
Catch
txtUserName.Enabled = False
txtPassword.Enabled = False
btnLogin.Enabled = False

Label1.ForeColor = Color.DarkBlue
Label1.Font.Bold = True
Label1.Font.Size = FontUnit.Medium
Label1.Text = "<center>The repository is unavailable at this
time.</center>"
Finally

cmdCheckRepositoryAvailability.Dispose()
cmdCheckRepositoryAvailability = Nothing

If Not rdrCheckRepositoryAvailability Is Nothing Then
rdrCheckRepositoryAvailability.Close()
End If

rdrCheckRepositoryAvailability = Nothing

Conn.Close()
Conn = Nothing

End Try

This is a web application, I don't remember having to do anything special on
the old development machine to be able to connect to SQL Server.
As you can see I am using SQL Server Authentication (using sa is only in my
test network <grin>)

Thanks for any assistance, please let me know if there is anything else you
need.

Jon
(e-mail address removed)

 

[Greg Burns]

I was not expecting to see sa being used. That definately eliminates a lot
of my theories. :^)

Only thing I have left to offer is perhaps XP SP2's firewall???

Greg

 

[Jon Delano]

Oh yeah ... the server that sql server 2000 is running on is Windows 2000

 

[Jon Delano]

I checked that ... I have it off .. as I am using NAT and am behind a
router.
The server I am connecting to is behind the router as well.

This is so strange ...

 

[Greg Burns]

You say you are getting "sql server does not exist or access denied", but I
don't see where you are recording the error in your catch statement. How
can you tell what the error is from the code you posted?

Try
....
Catch ex as exception
trace.warn(ex.tostring)
...
Finally
....
End Try

Change your catch to something like that. You could, of course, put a
breakpoint on the catch statement itself and take a look ex's value.

Greg

 

[Jon Delano]

In VS 2003 ... I had changed the catch to catch ex as exception ...
Then put a break point in that section ... when it stopped there ... I did a
quick watch on ex ...

 

[Greg Burns]

Try some of these trouble shooting things.

http://support.microsoft.com/kb/827422

I would also verify with a watch that the value being read from the
web.config is what you think it is.

Greg

 

[Greg Burns]

http://support.microsoft.com/?kbid=328306

http://support.microsoft.com/kb/827422

http://support.microsoft.com/?kbid=888228

Greg

 

[Jon Delano]

Will do .. thanks ... and did a watch on that too :-D

I appreciate all your assistance.

Thank you

 

[Juan T. Llibre]

Jon,

Please review this KB, and see if it applies to your case :
http://support.microsoft.com/default.aspx?scid=kb;en-us;315159

If your ASP.NET worker process is running under the ASPNET account,
and your SQL server is using named pipes to receive connections,
there's a bug for the .Net Framework 1.1 and ASP.NET 1.1 :
the ASPNET account cannot establish a named pipes connection to the
SQL server, unless you do one of these two things :

1. Turn on impersonation for your ASP.NET application.

2. Use TCP/IP instead of the named pipes protocol to connect to the db.

To use TCP/IP, add "Network Library =dbmssocn" to your connection string.

"User ID=sa;Password=Password;Initial Catalog=ShepherdRepository;Data
Source=server;Connect Timeout=120;Network Library =dbmssocn"

If you use impersonation you don't have to do that, but remember
that the Application's user will be changed to IUSR_Machinename

( That's why using impersonation works,
because it doesn't use the ASPNET account any more ).

 

[Jon Delano]

It is certainly looking more like the ASPNET user ... I just created a new
project .. a Windows App .. worked just fine.
One button that created a connection .. and used the exact connection string
...

Then created a new web app ... with just the one button ..
that fails

I added the Network Library=dbmssocn to my connection string, still errors
out.

Will keep plugging away.

Thanks again for everything.

 

[Jon Delano]

I installed VS 2005 ... that works fine.
VS 2003 .. doesn't ... it has to be something with 1.1 .. no ?

 

[Greg Burns]

I installed VS 2005 ... that works fine.
VS 2003 .. doesn't ... it has to be something with 1.1 .. no ?

Well, VS 2005 web apps runs filesystem-based by default when developing.
Not even sure if it is using the ASPNET user in that context.

Do you have the same problem if you create you test project in VS 2005 using
IIS?

Greg

 

[Jon Delano]

I created a test project in VS 2005 .. its a web app .. and it connects to
the sql server, using the same connection string I use in VS 2003.
2005 works.. no problem.

I created a seperate test project in VS 2003 ... web app .. using the same
connection string ... it doesn't work.

Each is just a webform .. with a button .. when the button is clicked .. it
creates a connection .. closes the connection ... and thats it

 

[Jon Delano]

The continuing saga ...

Well just to make sure it wasn't an XP installation problem. I installed a
new copy of XP into a new folder.
I then reinstalled vs.2003 ... it exhibits the same problem ... doesn't find
my sql server .. when a test web project is run.

Only other thing I can come up with, is the vs 2002 was installed on my
current development machine prior to vs 2003 being installed.
Could that be something that might effect vs 2003 on the new machine, the
fact thet VS 2002 was never installed ?