Connect to SQL Server from ASP.Net and through firewall

We have a development SQL Server 2000 (running on Win2K SP4) machine that we
want to move into our DMZ and access it via the internet and our internal L
AN. We have opened the firewall to allow our applications on the LAN to conn
ect to SQL server in the DM
Z.
We connect to SQL Server using the .Net SQLConnection class and using a SQL
Server username and password (i.e. sa/password). Also we connect to SQL Serv
er from WinForm and ASP.Net applications using an alias defined using Client
Network Utility.
The problem comes when we try to connect to SQL from the LAN from an ASP.Net
application. In that case I get "SQL server does not exist..." error. Conne
cting from a WinForm application works fine.
So to summarise: I run the same connection code (and connection string) on t
he same client machine to connect to a SQL Server 2000 database via a firewa
ll. The WinForm application works fine, the ASP.Net application throws an er
ror. The connection code an
d connection string are identical.
Can you suggest why this is happening? We have traced the firewall traffic a
nd nothing appears to be blocked.
The connection code follows: (we use an alias for a server name)
'----
Public Shared Function CreateConnection(ByVal aServerName As String, ByVal a
DatabaseName As String, ByVal aUserName As String, ByVal aPassword As String
) As System.Data.SqlClient.SqlConnection
Dim connectionString As String
Dim connection As System.Data.SqlClient.SqlConnection
connectionString = "server=" + aServerName + ";database=" + aDatabaseName +
";user id=" + aUserName + ";password=" + aPassword
connection = New System.Data.SqlClient.SqlConnection(connectionString)
connection.Open()
Return connection
End Function 'CreateSession
Thanks,
Scott Simms.Hi Scott,
I learned you ran the same connection code (and connection string) on the
same client machine to connect to a SQL Server 2000 database via a
firewall. The WinForm application works fine, but the ASP.Net application
throws an error.
This problem is strange. Please make sure the server name is correct and
the user account used in the connection string is listed in the SQL Server
logins list.
In Query Analyzer, you can use the following sql statements to check the
logins list on the destination server.
Use master
Select * from syslogins
Please help collect the following information so that I can narrow down
this problem.
1. Please try to create a sample connection web project using the
connection string directly without variables. Please make sure the server
name and user account/password is correct. Did the same problem persist?
2. How did you configure the alias? Did you use named pipes? If so, Please
configure a TCP/IP alias and forcing the connection string to use TCP/IP.
To do this, add the following attribute to the connection string:
"Network Library =dbmssocn"
What's the result?
3. If it is possible, please try the standard SQL client tools, such as
Query Analyzer, connecting to the SQL Server via firewall using the same
sql account. Did the same problem occur again?
I also found the following articles for your reference.
315159 BUG: Named Pipes Do Not Work When Worker Process Runs Under ASPNET
http://support.microsoft.com/?id=315159
328306 INF: Potential Causes of the "SQL Server Does Not Exist or Access
http://support.microsoft.com/?id=328306
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello,
How is the issue going on your side? Please try to change the process
acount from "machine" to "SYSTEM" in the machine.config 's <processModel>
element. Does the same problem persist?
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hello,
How is the issue going on your side? Let us know if you need further
assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Yuan,
We have very simlar issues ast Scott's in our company. Our web server is ins
ide our firewall. And our custom SQL server is inside their firewall. But th
e network folks told us that both sides was opened. Our custom side opened
port 1433. But I just can n
ot connection. Here is the connection string:Password=xxxx;Persist Security
Info=True;User ID=xxx;Initial Catalog=DBNAME;Data Source=IDaddress;Network L
ibrary=dbmssocn
Can you help?