problem connecting to my database behind a firewall

[Greg W via DotNetMonster.com]

hello all,
I have site that I just moved to a new host. It is a dedicated server that
sits behind a dedicated firewall. Most of the site uses classic ASP that are
working fine but I have some parts that I am converting to .NET that can't
seem to access the database.

The server is windows 2k3 and I am using SQL Server 2k.

In my web config file I have the following connection string:

<appSettings>
<add key="emailConn" value="workstat ion id=SAVAGE;packe t size=4096;user
id=xxx;data source=[ip];persist security info=True;initi al
catalog=email;p assword=xxx" />
</appSettings>

Here is the code where I am calling the connection and using it:

Dim txtUser As String = Request.Form("s trUserID")
Dim txtPW As String = Trim(Request.Fo rm("strPassword "))
Dim txtPWdb As String
Dim intAdmindb, intDirdb As Integer

Dim cmdPW As New SqlCommand("SEL ECT strPWD, intAdmin, intDirector
FROM dbo.prtlUser WHERE (strUID)='" & txtUser & "'")

Dim objCon As New SqlConnection(C onfigurationSet tings.AppSettin gs
("emailConn" ))

cmdPW.Connectio n = objCon
cmdPW.Connectio n.Open()

Dim readPW As SqlDataReader = cmdPW.ExecuteRe ader()
Do While readPW.Read()
txtPWdb = readPW(0)
intAdmindb = readPW(1)
intDirdb = readPW(2)
Loop
If txtPW = txtPWdb And txtPW <> "" Then
Dim strUIDs As String
Session("strUID s") = txtUser
Session("intAdm ins") = intAdmindb
Session("intDir s") = intDirdb
Response.Redire ct("default.asp x")
Else
Panel1.Visible = False
Panel2.Visible = True
End If
objCon.Close()

As you can see this is a log in page where some one enters a log in and pw
and it checks to see if it is a valid match.

In my classic ASP stuff, since I am behind the firewall instead of using the
external ip of the server I use the internal one 192.168.0.2. However, on
the new server, I have tried using the external IP (this worked with the old
server), the internal IP, and even setting the datasource as things like
localhost and (local). Everytime I get the following error:

SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.Data.Sql Client.SqlExcep tion: SQL Server does not
exist or access denied.

Source Error:

An unhandled exception was generated during the execution of the current web
request. Information regarding the origin and location of the exception can
be identified using the exception stack trace below.

At first I thought it might be a permissions error, but when I set the
connection string in the config file to the external IP, and I run this on my
local machine, it can connect to the database and run with no problem. I am
convinced it has something to do with the firewall, but I have no idea what
to do about it.

Any help would be greatly appreciated.

[Mariano Omar Rodriguez]

It seems to be a Networking problem. You need check if you want network
connection to the database server.

Mariano

"Greg W via DotNetMonster.c om" <fo***@DotNetMo nster.com> wrote in message
news:51******** ***@DotNetMonst er.com...

hello all,
I have site that I just moved to a new host. It is a dedicated server
that
sits behind a dedicated firewall. Most of the site uses classic ASP that
are
working fine but I have some parts that I am converting to .NET that can't
seem to access the database.

The server is windows 2k3 and I am using SQL Server 2k.

In my web config file I have the following connection string:

<appSettings>
<add key="emailConn" value="workstat ion id=SAVAGE;packe t size=4096;user
id=xxx;data source=[ip];persist security info=True;initi al
catalog=email;p assword=xxx" />
</appSettings>

Here is the code where I am calling the connection and using it:

Dim txtUser As String = Request.Form("s trUserID")
Dim txtPW As String = Trim(Request.Fo rm("strPassword "))
Dim txtPWdb As String
Dim intAdmindb, intDirdb As Integer

Dim cmdPW As New SqlCommand("SEL ECT strPWD, intAdmin,
intDirector
FROM dbo.prtlUser WHERE (strUID)='" & txtUser & "'")

Dim objCon As New
SqlConnection(C onfigurationSet tings.AppSettin gs
("emailConn" ))

cmdPW.Connectio n = objCon
cmdPW.Connectio n.Open()

Dim readPW As SqlDataReader = cmdPW.ExecuteRe ader()
Do While readPW.Read()
txtPWdb = readPW(0)
intAdmindb = readPW(1)
intDirdb = readPW(2)
Loop
If txtPW = txtPWdb And txtPW <> "" Then
Dim strUIDs As String
Session("strUID s") = txtUser
Session("intAdm ins") = intAdmindb
Session("intDir s") = intDirdb
Response.Redire ct("default.asp x")
Else
Panel1.Visible = False
Panel2.Visible = True
End If
objCon.Close()

As you can see this is a log in page where some one enters a log in and pw
and it checks to see if it is a valid match.

In my classic ASP stuff, since I am behind the firewall instead of using
the
external ip of the server I use the internal one 192.168.0.2. However, on
the new server, I have tried using the external IP (this worked with the
old
server), the internal IP, and even setting the datasource as things like
localhost and (local). Everytime I get the following error:

SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about
the error and where it originated in the code.

Exception Details: System.Data.Sql Client.SqlExcep tion: SQL Server does not
exist or access denied.

Source Error:

An unhandled exception was generated during the execution of the current
web
request. Information regarding the origin and location of the exception
can
be identified using the exception stack trace below.

At first I thought it might be a permissions error, but when I set the
connection string in the config file to the external IP, and I run this on
my
local machine, it can connect to the database and run with no problem. I
am
convinced it has something to do with the firewall, but I have no idea
what
to do about it.

Any help would be greatly appreciated.

[a22042]

Mariano Omar Rodriguez wrote:

*It seems to be a Networking problem. You need check if you want
network
connection to the database server.

Mariano

*

Im sorry, i don't follow. The SQL Server is running on the same
machine as the web server In classic ASP I can connect to the db with
no problem, it is just in .NET I have a problem.

--
a22042
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------

[Alan Samet]

I would start by just trying to get your application to connect to the
database. Try creating a new page with the following code, update your
ip, uid and password parameters. I have a feeling it's something simple
that you may be overlooking -- the first thing I'd recommend is that
you verify your connection string and your appsetting (I'm winging it
here, hopefully there's no syntax errors):

<%@ Page Language="VB" %>
<%@ Import Namespace="Syst em.Data.SqlClie nt" %>
<script runat=server>
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
With New
SqlConnection(" server=[ip];database=maste r;uid=xxx;passw ord=xxx;")
.Open()
.Close()
End With
End Sub
</script>

[Greg W via DotNetMonster.com]

OK, that worked, but I cant see why it wont work in the app. It worked fine
on the other server. And when I debug from my local machine (and use the
external ip) it works fine as well.

Alan Samet wrote:

I would start by just trying to get your application to connect to the
database. Try creating a new page with the following code, update your
ip, uid and password parameters. I have a feeling it's something simple
that you may be overlooking -- the first thing I'd recommend is that
you verify your connection string and your appsetting (I'm winging it
here, hopefully there's no syntax errors):

<%@ Page Language="VB" %>
<%@ Import Namespace="Syst em.Data.SqlClie nt" %>
<script runat=server>
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
With New
SqlConnection( "server=[ip];database=maste r;uid=xxx;passw ord=xxx;")
.Open()
.Close()
End With
End Sub
</script>

--
Message posted via http://www.dotnetmonster.com

[Alan Samet]

I'm wondering if it's a deployment issue. If you're using codebehinds,
have you deployed the compiled dll to the bin directory of the server?
Also, verify that your web.config is updated.

-Alan

[Greg W via DotNetMonster.com]

Oh crap, thank you very much, I thought I had coppied over the the bin folder,
but must have forgotten, that is exactly it.

Alan Samet wrote:

I'm wondering if it's a deployment issue. If you're using codebehinds,
have you deployed the compiled dll to the bin directory of the server?
Also, verify that your web.config is updated.

-Alan

--
Message posted via DotNetMonster.c om
http://www.dotnetmonster.com/Uwe/For...p-net/200507/1

[Alan Samet]

I thought that may've been it. I'd say that belongs near or at the top
of the list for common ASP.NET oversights that cause developers a lot
of frustration :)

-Alan