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...

<connectionStri ngs>
<add name="MyConnect ionString" connectionStrin g="Data
Source=ServerNa me;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
ID=user;Passwor d=password;" providerName="S qlNetTiersProvi der"/>
</connectionStrin gs

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:
!<connectionStr ings>
!<add name="MyConnect ionString" connectionStrin g="Data
!Source=ServerN ame;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
!ID=user;Passwo rd=password;" providerName="S qlNetTiersProvi der"/>
!</connectionStrin gs

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;Ne twork Library=DBMSSOC N;Initial Catalog=theData Base;User
ID=Username;Pas sword=Password;

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

<connectionStri ngs>
<add name="MyConnect ionString" connectionStrin g="Data Source=190.190. 190.190,1433;
Network Library=DBMSSOC N;Initial Catalog=theDBNa me;User ID=Username;Pas sword=Password; "/>
</connectionStrin gs

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

Try it...


Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
=============== =============== ========
"OJ" <of****@gmail.c omwrote in message
news:11******** **************@ l77g2000hsb.goo glegroups.com.. .

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...

<connectionStri ngs>
<add name="MyConnect ionString" connectionStrin g="Data
Source=ServerNa me;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
ID=user;Passwor d=password;" providerName="S qlNetTiersProvi der"/>
</connectionStrin gs

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

[OJ]

On 14 May, 15:00, "Juan T. Llibre" <nomailrepl...@ nowhere.comwrot e:

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

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

re:
!<connectionStr ings>
!<add name="MyConnect ionString" connectionStrin g="Data
!Source=ServerN ame;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
!ID=user;Passwo rd=password;" providerName="S qlNetTiersProvi der"/>
!</connectionStrin gs

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;Ne twork Library=DBMSSOC N;Initial Catalog=theData Base;User
ID=Username;Pas sword=Password;

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

<connectionStri ngs>
<add name="MyConnect ionString" connectionStrin g="Data Source=190.190. 190.190,1433;
Network Library=DBMSSOC N;Initial Catalog=theDBNa me;User ID=Username;Pas sword=Password; "/>
</connectionStrin gs

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.

<connectionStri ngs>
<add name="netTiersC onnectionString " connectionStrin g="Data
Source=172.x.x. x, 1433;Network Library=DBMSSOC N;Initial
Catalog=MyDB;Tr usted_Connectio n=False;User ID=user;Passwor d=password;"/

>

</connectionStrin gs>

<netTiersServic e defaultProvider ="SqlNetTiersPr ovider">
<providers>
<add name="SqlNetTie rsProvider"
type="BrandIQ.N etTiers.DAL.Sql Client.SqlNetTi ersProvider,
BrandIQ.NetTier s.DAL.SqlClient "
connectionStrin gName="netTiers ConnectionStrin g"
useStoredProced ure="true"
providerInvaria ntName="System. Data.SqlClient"
entityFactoryTy pe="BrandIQ.Net Tiers.Entities. EntityFactory"
enableEntityTra cking="true" enableMethodAut horization="fal se"/>
</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_Connec tion=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=DBMSSOC N" to tell it to
use TCP/IP to connect.
2. Also keep "Trusted_Connec tion=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" <of****@gmail.c omwrote in message
news:11******** **************@ e51g2000hsg.goo glegroups.com.. .

On 14 May, 15:00, "Juan T. Llibre" <nomailrepl...@ nowhere.comwrot e:

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

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

re:
!<connectionSt rings>
!<add name="MyConnect ionString" connectionStrin g="Data
!Source=Server Name;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
!ID=user;Passw ord=password;" providerName="S qlNetTiersProvi der"/>
!</connectionStrin gs

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;Ne twork Library=DBMSSOC N;Initial
Catalog=theDat aBase;User
ID=Username;Pa ssword=Password ;

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

<connectionStr ings>
<add name="MyConnect ionString" connectionStrin g="Data
Source=190.190 .190.190,1433;
Network Library=DBMSSOC N;Initial Catalog=theDBNa me;User
ID=Username;Pa ssword=Password ;"/>
</connectionStrin gs

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.

<connectionStri ngs>
<add name="netTiersC onnectionString " connectionStrin g="Data
Source=172.x.x. x, 1433;Network Library=DBMSSOC N;Initial
Catalog=MyDB;Tr usted_Connectio n=False;User ID=user;Passwor d=password;"/

>>

</connectionStrin gs>

<netTiersServic e defaultProvider ="SqlNetTiersPr ovider">
<providers>
<add name="SqlNetTie rsProvider"
type="BrandIQ.N etTiers.DAL.Sql Client.SqlNetTi ersProvider,
BrandIQ.NetTier s.DAL.SqlClient "
connectionStrin gName="netTiers ConnectionStrin g"
useStoredProced ure="true"
providerInvaria ntName="System. Data.SqlClient"
entityFactoryTy pe="BrandIQ.Net Tiers.Entities. EntityFactory"
enableEntityTra cking="true" enableMethodAut horization="fal se"/>
</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

[OJ]

On 14 May, 16:07, "George Ter-Saakov" <[e-mail] wrote:

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_Connec tion=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=DBMSSOC N" to tell it to
use TCP/IP to connect.
2. Also keep "Trusted_Connec tion=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" <ofe...@gmail.c omwrote in message

news:11******** **************@ e51g2000hsg.goo glegroups.com.. .

On 14 May, 15:00, "Juan T. Llibre" <nomailrepl...@ nowhere.comwrot e:

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

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

re:
!<connectionStr ings>
!<add name="MyConnect ionString" connectionStrin g="Data
!Source=ServerN ame;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
!ID=user;Passwo rd=password;" providerName="S qlNetTiersProvi der"/>
!</connectionStrin gs

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;Ne twork Library=DBMSSOC N;Initial
Catalog=theData Base;User
ID=Username;Pas sword=Password;

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

<connectionStri ngs>
<add name="MyConnect ionString" connectionStrin g="Data
Source=190.190. 190.190,1433;
Network Library=DBMSSOC N;Initial Catalog=theDBNa me;User
ID=Username;Pas sword=Password; "/>
</connectionStrin gs

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.

<connectionStri ngs>
<add name="netTiersC onnectionString " connectionStrin g="Data
Source=172.x.x. x, 1433;Network Library=DBMSSOC N;Initial
Catalog=MyDB;Tr usted_Connectio n=False;User ID=user;Passwor d=password;"/

</connectionStrin gs>

<netTiersServic e defaultProvider ="SqlNetTiersPr ovider">
<providers>
<add name="SqlNetTie rsProvider"
type="BrandIQ.N etTiers.DAL.Sql Client.SqlNetTi ersProvider,
BrandIQ.NetTier s.DAL.SqlClient "
connectionStrin gName="netTiers ConnectionStrin g"
useStoredProced ure="true"
providerInvaria ntName="System. Data.SqlClient"
entityFactoryTy pe="BrandIQ.Net Tiers.Entities. EntityFactory"
enableEntityTra cking="true" enableMethodAut horization="fal se"/>
</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- Hide quoted text -

- 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!


Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
=============== =============== ========
"OJ" <of****@gmail.c omwrote in message
news:11******** **************@ k79g2000hse.goo glegroups.com.. .

On 14 May, 16:07, "George Ter-Saakov" <[e-mail] wrote:

>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_Conne ction=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=DBMSSOC N" to tell it to
use TCP/IP to connect.
2. Also keep "Trusted_Connec tion=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" <ofe...@gmail.c omwrote in message

news:11******* *************** @e51g2000hsg.go oglegroups.com. ..

On 14 May, 15:00, "Juan T. Llibre" <nomailrepl...@ nowhere.comwrot e:
re:
!>Like I said though, it all seems to work for cassini.

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

>re:
!<connectionSt rings>
!<add name="MyConnect ionString" connectionStrin g="Data
!Source=Server Name;Initial Catalog=MyDB;Tr usted_Connectio n=False;User
!ID=user;Passw ord=password;" providerName="S qlNetTiersProvi der"/>
!</connectionStrin gs

>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;Ne twork Library=DBMSSOC N;Initial
Catalog=theDat aBase;User
ID=Username;Pa ssword=Password ;

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

><connectionStr ings>
<add name="MyConnect ionString" connectionStrin g="Data
Source=190.190 .190.190,1433;
Network Library=DBMSSOC N;Initial Catalog=theDBNa me;User
ID=Username;Pa ssword=Password ;"/>
</connectionStrin gs

>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.

<connectionStri ngs>
<add name="netTiersC onnectionString " connectionStrin g="Data
Source=172.x.x. x, 1433;Network Library=DBMSSOC N;Initial
Catalog=MyDB;Tr usted_Connectio n=False;User ID=user;Passwor d=password;"/

</connectionStrin gs>

<netTiersServic e defaultProvider ="SqlNetTiersPr ovider">
<providers>
<add name="SqlNetTie rsProvider"
type="BrandIQ.N etTiers.DAL.Sql Client.SqlNetTi ersProvider,
BrandIQ.NetTier s.DAL.SqlClient "
connectionStrin gName="netTiers ConnectionStrin g"
useStoredProced ure="true"
providerInvaria ntName="System. Data.SqlClient"
entityFactoryTy pe="BrandIQ.Net Tiers.Entities. EntityFactory"
enableEntityTra cking="true" enableMethodAut horization="fal se"/>
</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- Hide quoted text -

- 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