Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > SQL Server 2000/ASP.NET 2.0 Connection String: Works with Cassini, not with IIS 5.1

Reply
Thread Tools

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

 
 
OJ
Guest
Posts: n/a
 
      05-14-2007
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

 
Reply With Quote
 
 
 
 
Juan T. Llibre
Guest
Posts: n/a
 
      05-14-2007
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...






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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.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...
>
> <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
>



 
Reply With Quote
 
 
 
 
OJ
Guest
Posts: n/a
 
      05-14-2007
On 14 May, 15:00, "Juan T. Llibre" <(E-Mail Removed)> wrote:
> 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.SqlNetTiersPr ovider,
BrandIQ.NetTiers.DAL.SqlClient"
connectionStringName="netTiersConnectionString"
useStoredProcedure="true"
providerInvariantName="System.Data.SqlClient"
entityFactoryType="BrandIQ.NetTiers.Entities.Entit yFactory"
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

 
Reply With Quote
 
George Ter-Saakov
Guest
Posts: n/a
 
      05-14-2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> On 14 May, 15:00, "Juan T. Llibre" <(E-Mail Removed)> wrote:
>> 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.SqlNetTiersPr ovider,
> BrandIQ.NetTiers.DAL.SqlClient"
> connectionStringName="netTiersConnectionString"
> useStoredProcedure="true"
> providerInvariantName="System.Data.SqlClient"
> entityFactoryType="BrandIQ.NetTiers.Entities.Entit yFactory"
> 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
>



 
Reply With Quote
 
OJ
Guest
Posts: n/a
 
      05-14-2007
On 14 May, 16:07, "George Ter-Saakov" <(E-Mail Removed)> 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_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" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed) oups.com...
>
>
>
> > On 14 May, 15:00, "Juan T. Llibre" <(E-Mail Removed)> wrote:
> >> 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.SqlNetTiersPr ovider,
> > BrandIQ.NetTiers.DAL.SqlClient"
> > connectionStringName="netTiersConnectionString"
> > useStoredProcedure="true"
> > providerInvariantName="System.Data.SqlClient"
> > entityFactoryType="BrandIQ.NetTiers.Entities.Entit yFactory"
> > 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- 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

 
Reply With Quote
 
Juan T. Llibre
Guest
Posts: n/a
 
      05-14-2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) oups.com...
> On 14 May, 16:07, "George Ter-Saakov" <(E-Mail Removed)> 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_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" <(E-Mail Removed)> wrote in message
>>
>> news:(E-Mail Removed) oups.com...
>>
>>
>>
>> > On 14 May, 15:00, "Juan T. Llibre" <(E-Mail Removed)> wrote:
>> >> 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.SqlNetTiersPr ovider,
>> > BrandIQ.NetTiers.DAL.SqlClient"
>> > connectionStringName="netTiersConnectionString"
>> > useStoredProcedure="true"
>> > providerInvariantName="System.Data.SqlClient"
>> > entityFactoryType="BrandIQ.NetTiers.Entities.Entit yFactory"
>> > 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- 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
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Building SQL connection string in code-behind file. Works on my machine, not on server. Brett ASP .Net 8 07-30-2008 02:01 PM
Server.MapPath() works differently on IIS 6.0 compared to IIS 5.0 Elie Grouchko ASP General 7 01-04-2005 10:52 AM
Connection to SQL Server works in VB.NET, not ASP.NET Roy Lawson ASP .Net 2 02-27-2004 05:28 AM
SQL Server connection string works with IIS 5.1 but not IIS 6 in ASP.NET John Kennedy ASP .Net Web Services 5 08-14-2003 12:15 AM
SQL Server connection string works with IIS 5.1 but not IIS 6 in ASP.NET John Kennedy ASP .Net Security 5 08-14-2003 12:15 AM



Advertisments