ASP .Net connection to Sql Server

[Dave Dudley]

Hi,
I have a new ASP .Net project that has been developed on our
development machine and connects to SQL Server 2000 on the same
machine. It is connecting via a connection string similiar to:

Data Source=[Machine Name];Initial Catalog=[Our
Catalog];uid=[Username];Password=[Password]
Everything works fine on this machine. I can change the the connection
string to our production SQL server and everything runs fine. The
production SQL server is also running SQL 2000. All machines are on
Windows 2000 with MDAC 2.8.
Our production web server is in its own zone in the firewall so their
isn't full network access, just a hole to the sql server. When I move
the site to the server I start to get the error below when trying to
access the sql server from the project.
All of the regular ASP connections work, both through named pipes and
ODBC but this one won't. I have read a ton of articles and newsgroup
postings about this now but haven't been able to resolve it and I am
ready to start pulling hair.
I have changed the processmodel to be the system account. I have added
"Network Library=dbmssocn" to the connection string. I have tried
impersonation, though since this isn't on the network I didn't expect
it to work. Reboots, restarts, nothing works.
Is there anything else that I am missing?
Thanks,
Dave Dudley
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.SqlClient.SqlException: 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.
Stack Trace:
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConn ection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +36
KnitPicks.SearchAll.Page_Load(Object sender, EventArgs e) in
C:\Documents and
Settings\Dudleyd\VSWebCache\cagdev_4251\SearchAll. aspx.vb:194
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

[Brad Roberts]

I use the same scenario with one of mine.

Using "dbmssocn" tells it to use the TCP/IP network driver, good.

It may be having a problem resolving the machine name, try using the IP of
the SQL box instead of the name.
"Dave Dudley" wrote:

Hi,
I have a new ASP .Net project that has been developed on our
development machine and connects to SQL Server 2000 on the same
machine. It is connecting via a connection string similiar to:

Data Source=[Machine Name];Initial Catalog=[Our
Catalog];uid=[Username];Password=[Password]
Everything works fine on this machine. I can change the the connection
string to our production SQL server and everything runs fine. The
production SQL server is also running SQL 2000. All machines are on
Windows 2000 with MDAC 2.8.
Our production web server is in its own zone in the firewall so their
isn't full network access, just a hole to the sql server. When I move
the site to the server I start to get the error below when trying to
access the sql server from the project.
All of the regular ASP connections work, both through named pipes and
ODBC but this one won't. I have read a ton of articles and newsgroup
postings about this now but haven't been able to resolve it and I am
ready to start pulling hair.
I have changed the processmodel to be the system account. I have added
"Network Library=dbmssocn" to the connection string. I have tried
impersonation, though since this isn't on the network I didn't expect
it to work. Reboots, restarts, nothing works.
Is there anything else that I am missing?
Thanks,
Dave Dudley
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.SqlClient.SqlException: 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.
Stack Trace:
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnectionString
options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConn ection connection,
ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable) +36
KnitPicks.SearchAll.Page_Load(Object sender, EventArgs e) in
C:\Documents and
Settings\Dudleyd\VSWebCache\cagdev_4251\SearchAll. aspx.vb:194
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

[Dave Dudley]

Thanks,

Since the web server is in the DMZ the only way I have access is by IP.
I have it set to IP and I even tried doing a host file entry and used
the name but that didn't work.

Dave

[bruce barker]

you want to use the tcp/ip library and standard security. you will need the
following info:

1) from the sql dba the tcp/ip port number the sqlserver is listing on
(default 1433, but should be changed)
2) from the fire wall people, the ipaddress of the sqlserver from the dmz
(usually they use nat translation and it will not match what you use from
your dev box
3) check that the port number the sql box is using is the opened port on the
firewall.

once you have the above info change the connection string to:
"server=ipaddess,portnumber"

-- bruce (sqlwork.com)
"Dave Dudley" <da*********@craftsamericana.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...
| Hi,
|
|
| I have a new ASP .Net project that has been developed on our
| development machine and connects to SQL Server 2000 on the same
| machine. It is connecting via a connection string similiar to:
|
|
|
| Data Source=[Machine Name];Initial Catalog=[Our
| Catalog];uid=[Username];Password=[Password]
| Everything works fine on this machine. I can change the the connection
| string to our production SQL server and everything runs fine. The
| production SQL server is also running SQL 2000. All machines are on
| Windows 2000 with MDAC 2.8.
|
|
| Our production web server is in its own zone in the firewall so their
| isn't full network access, just a hole to the sql server. When I move
| the site to the server I start to get the error below when trying to
| access the sql server from the project.
|
|
| All of the regular ASP connections work, both through named pipes and
| ODBC but this one won't. I have read a ton of articles and newsgroup
| postings about this now but haven't been able to resolve it and I am
| ready to start pulling hair.
|
|
| I have changed the processmodel to be the system account. I have added
| "Network Library=dbmssocn" to the connection string. I have tried
| impersonation, though since this isn't on the network I didn't expect
| it to work. Reboots, restarts, nothing works.
|
|
| Is there anything else that I am missing?
|
|
| Thanks,
|
|
| Dave Dudley
|
|
| 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.SqlClient.SqlException: 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.
|
|
| Stack Trace:
|
|
| [SqlException: SQL Server does not exist or access denied.]
| System.Data.SqlClient.ConnectionPool.GetConnection (Boolean&
| isInTransaction) +474
|
|
|
System.Data.SqlClient.SqlConnectionPoolManager.Get PooledConnection(SqlConnec
tionString
| options, Boolean& isInTransaction) +372
| System.Data.SqlClient.SqlConnection.Open() +384
| System.Data.Common.DbDataAdapter.QuietOpen(IDbConn ection connection,
| ConnectionState& originalState) +44
| System.Data.Common.DbDataAdapter.FillFromCommand(O bject data, Int32
| startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
| CommandBehavior behavior) +304
| System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
| startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
| CommandBehavior behavior) +77
| System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
| srcTable) +36
| KnitPicks.SearchAll.Page_Load(Object sender, EventArgs e) in
| C:\Documents and
|
|
| Settings\Dudleyd\VSWebCache\cagdev_4251\SearchAll. aspx.vb:194
| System.Web.UI.Control.OnLoad(EventArgs e) +67
| System.Web.UI.Control.LoadRecursive() +35
| System.Web.UI.Page.ProcessRequestMain() +750
|

[Dave Dudley]

This is so frustrating. I have the IP address set, the firewall is
open, and I know it is because my other connections still work through
ASP and client tools. I just don't see what would be holding it back.
Dave

[Brad Roberts]

Just a few thoughts to make sure all the bases get covered...

Do the other successful connections work using the "dbmssocn" Network
Library.?

Are you sure the SQL Server is configured for TCP/IP? (It will default to
another protocol if not. Like Named Pipes...)

Are you sure the SQL server accepts SQL Server Logins(vs NTLM)?

Can you get a ASP page on the web server to connect ok?

The firewall people can probably see what activity is coming from the web
server on the SQL Server port (what was it? 1433?). Can they be of help?

Can you access the SQL server log to possibly see something there?

"Dave Dudley" wrote:

This is so frustrating. I have the IP address set, the firewall is
open, and I know it is because my other connections still work through
ASP and client tools. I just don't see what would be holding it back.
Dave