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

Discussion in 'ASP .Net' started by OJ, May 14, 2007.

  1. OJ

    OJ Guest

    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
     
    OJ, May 14, 2007
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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, May 14, 2007
    #2
    1. Advertising

  3. OJ

    OJ Guest

    On 14 May, 15:00, "Juan T. Llibre" <> 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.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
     
    OJ, May 14, 2007
    #3
  4. 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" <> wrote in message
    news:...
    > On 14 May, 15:00, "Juan T. Llibre" <> 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.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, May 14, 2007
    #4
  5. OJ

    OJ Guest

    On 14 May, 16:07, "George Ter-Saakov" <> 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" <> wrote in message
    >
    > news:...
    >
    >
    >
    > > On 14 May, 15:00, "Juan T. Llibre" <> 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.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- 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
     
    OJ, May 14, 2007
    #5
  6. 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" <> wrote in message
    news:...
    > On 14 May, 16:07, "George Ter-Saakov" <> 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" <> wrote in message
    >>
    >> news:...
    >>
    >>
    >>
    >> > On 14 May, 15:00, "Juan T. Llibre" <> 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.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- 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, May 14, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Les Caudle
    Replies:
    2
    Views:
    1,733
    Les Caudle
    Sep 19, 2006
  2. John Kennedy
    Replies:
    5
    Views:
    244
    Carl Prothman [MVP]
    Aug 14, 2003
  3. dpomt
    Replies:
    2
    Views:
    892
    Steven Cheng[MSFT]
    Nov 6, 2006
  4. John Kennedy
    Replies:
    5
    Views:
    454
    Carl Prothman [MVP]
    Aug 14, 2003
  5. emc
    Replies:
    1
    Views:
    204
Loading...

Share This Page