SQL Server 2000/ASP.NET 2.0 Connection String: Works with Cassini, not with IIS 5.1

[OJ]

Hi,
I am at a loss here, I have a c# asp.net website which connects to a
remote SQL Server 2000 db via a connection string. If I set the
website up in the filesystem and use the inbuilt cassini webserver in
VS2005 then everything is rosy. If I then set up the website on the
local IIS 5.1 the site no longer connects and returns 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) Error Source: .Net SqlClient Data
Provider

Has anyone any idea why this might be and what could be happening
here? I can connect through Enterprise Manager. Here is the connection
string...

<connectionStrings>
<add name="MyConnectionString" connectionString="Data
Source=ServerName;Initial Catalog=MyDB;Trusted_Connection=False;User
ID=user;Password=password;" providerName="SqlNetTiersProvider"/>
</connectionStrings

As you may have noticed, for a little extra confusion the site is
using a generated NetTiers layer. Like I said though, it all seems to
work for cassini.

Regards,
Oliver

 

[Juan T. Llibre]

re:
!>Like I said though, it all seems to work for cassini.

On your *local* development box, which has SQL Server 2005, probably.

re:
!> <connectionStrings>
!> <add name="MyConnectionString" connectionString="Data
!> Source=ServerName;Initial Catalog=MyDB;Trusted_Connection=False;User
!> ID=user;Password=password;" providerName="SqlNetTiersProvider"/>
!> </connectionStrings

Why do you need a providerName if you're connecting to SQL Server 2000 ?

Have you tried connecting to the *remote* SQL server 2000 via IP ?

Here's an example connection string using an IP, instead of a server name :

Data Source=190.190.190.190,1433;Network Library=DBMSSOCN;Initial Catalog=theDataBase;User
ID=Username;Password=Password;

That would make your web.config entry look like this :

<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=190.190.190.190,1433;
Network Library=DBMSSOCN;Initial Catalog=theDBName;User ID=Username;Password=Password;"/>
</connectionStrings

1433 is the standard port used by SQL Server for remote accesses.
Substitute the real IP for 190.190.190.190.

Try it...

 

[OJ]

re:
!>Like I said though, it all seems to work for cassini.

On your *local* development box, which has SQL Server 2005, probably.

re:
!> <connectionStrings>
!> <add name="MyConnectionString" connectionString="Data
!> Source=ServerName;Initial Catalog=MyDB;Trusted_Connection=False;User
!> ID=user;Password=password;" providerName="SqlNetTiersProvider"/>
!> </connectionStrings

Why do you need a providerName if you're connecting to SQL Server 2000 ?

Have you tried connecting to the *remote* SQL server 2000 via IP ?

Here's an example connection string using an IP, instead of a server name :

Data Source=190.190.190.190,1433;Network Library=DBMSSOCN;Initial Catalog=theDataBase;User
ID=Username;Password=Password;

That would make your web.config entry look like this :

<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=190.190.190.190,1433;
Network Library=DBMSSOCN;Initial Catalog=theDBName;User ID=Username;Password=Password;"/>
</connectionStrings

1433 is the standard port used by SQL Server for remote accesses.
Substitute the real IP for 190.190.190.190.

Try it...

Hi Juan,
thanks for the swift reply. I have altered the connection string which
now looks like this..(I also added the entry after it in the
web.config related to the nettiers provider). Now I get an error that
the host machine has actively refused the target regardless of whether
I run cassini or IIS.

<connectionStrings>
<add name="netTiersConnectionString" connectionString="Data
Source=172.x.x.x, 1433;Network Library=DBMSSOCN;Initial
Catalog=MyDB;Trusted_Connection=False;User ID=user;Password=password;"/ </connectionStrings>

<netTiersService defaultProvider="SqlNetTiersProvider">
<providers>
<add name="SqlNetTiersProvider"
type="BrandIQ.NetTiers.DAL.SqlClient.SqlNetTiersProvider,
BrandIQ.NetTiers.DAL.SqlClient"
connectionStringName="netTiersConnectionString"
useStoredProcedure="true"
providerInvariantName="System.Data.SqlClient"
entityFactoryType="BrandIQ.NetTiers.Entities.EntityFactory"
enableEntityTracking="true" enableMethodAuthorization="false"/>
</providers>
</netTiersService>


Error:
(provider: TCP Provider, error: 0 - No connection could be made
because the target machine actively refused it.)

I have set up IIS to not use the IUSR account but instead to use my
Network logon details. I thought this might help but it didn't.
My local box does have SQL 2005 Express on it but I haven't used it
for anything yet. I am using the full version of VS2005.

Any more thoughts?
Thanks,

OJ

 

[George Ter-Saakov]

Here is my 2 cents. Just a guess here. You have security problem.

By default SQL using NetPipes protocol. The problem with NetPipes is that
it's using NT authentication inherently. Even if you using
"Trusted_Connection=False" the machine you are trying to connect to with
NetPipes will refuse connection if it does know account. Thus you got a
scenario when Casini works and IIS does not.

You need to
1. add to your connection string "Network Library=DBMSSOCN" to tell it to
use TCP/IP to connect.
2. Also keep "Trusted_Connection=false;" in the connection string.
3. Make sure TCP/IP connection is available on SQL server. Right click on a
server in SQL Manager, Tab "General", "Network configuration" button. Add
it if it's not available.


George.

 

[OJ]

Here is my 2 cents. Just a guess here. You have security problem.

By default SQL using NetPipes protocol. The problem with NetPipes is that
it's using NT authentication inherently. Even if you using
"Trusted_Connection=False" the machine you are trying to connect to with
NetPipes will refuse connection if it does know account. Thus you got a
scenario when Casini works and IIS does not.

You need to
1. add to your connection string "Network Library=DBMSSOCN" to tell it to
use TCP/IP to connect.
2. Also keep "Trusted_Connection=false;" in the connection string.
3. Make sure TCP/IP connection is available on SQL server. Right click on a
server in SQL Manager, Tab "General", "Network configuration" button. Add
it if it's not available.

George.












- Show quoted text -

Hi George,
that makes sense. That is what I first thought! When I connect over
TCP/IP the firewall is refusing the connection (even though SQL Server
is setup for TCP/IP ports), and even when I configure IIS to use my
network account (as cassini does), it is still getting refused through
NamedPipes for the reason you have just given.

Time to open some ports!

Thanks
OJ

 

[Juan T. Llibre]

re:
!> Time to open some ports!

Indeed!