Re: connect to named instance w/ non-default port

You create Aliases by using the SQL-Server Configuration Manager, under the
SQL Native Client Configuration. This way, you can the sql client precisely
which protocol with which parameters to be used.

Looking at the error message, it's strange to see that even with the np:
prefix, the SQL client still try to use the TCP protocol.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"John A Grandy" <johnagrandy-at-gmail-dot-com> wrote in message
news:%23zKs57TvJHA.5684@xxxxxxxxxxxxxxxxxxxxxxx
Neither of these connection strings works :

Data Source=np:<server name>\<instance name>;Initial Catalog=<db
name>;User
ID=<user id>;Password=<password>;

Data Source=tcp:<server name>\<instance name>;Initial Catalog=<db
name>;User
ID=<user id>;Password=<password>;

Always the same exception when attempt

System.Data.SqlClient.SqlConnection.Open()

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: TCP Provider, error: 0 - No connection could be made because
the target machine actively refused it.)


This error is misleading in that remote connections have definitely been
enabled ( as connecting from client via SSMS proves ).


"John A Grandy" <johnagrandy-at-gmail-dot-com> wrote in message
news:OpOw8rSvJHA.248@xxxxxxxxxxxxxxxxxxxxxxx
How would I go about creating an alias ?

As far as using a profiler and/or activity monitor : this diagnostic is
performed server-side , correct ? ( or is it also possible perform
client-side )

My .NET coding is the absolute bare-bones possible :

using System.Data.SqlClient;
SqlConnection connection = new SqlConnection( <connection string> );
connection.Open();

I have also written an SSIS package for this customer.

It uses nearly the same connection string , with the difference being
that the SQL Server Native Client provider is used instead of the .NET
Framework Data Provider for SQL Server

Provider=SQLNCLI.1;

At this time , I am not able to test this SSIS package within the
customer environment. Would you expect SSIS to behave differently than
my .NET implementation ?



"Sylvain Lafontaine" <sylvainlafontaine2009@xxxxxxxx> wrote in message
news:OlSz3XSvJHA.5684@xxxxxxxxxxxxxxxxxxxxxxx
Yes (never tried with .NET but it should work as well). You can also
try with tcp: ; however, as you have already tried by using the library
name as a parameter, I don't think that this will change anything.

Another possibility would be to create an alias. This way, you can
control exactly which protocol will be used with the right parameter(s);
independantly of the platform (SSMS, .NET, etc.) used.

Like as Andrew has suggest, you should use the profiler or the activity
monitor to discover what kind of protocol SSMS is using to connect to
the server.

Finally, your problem might stand in the .NET coding. Did you try to
connect using any other technology?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"John A Grandy" <johnagrandy-at-gmail-dot-com> wrote in message
news:e5ztlKSvJHA.1300@xxxxxxxxxxxxxxxxxxxxxxx
Just to verify , you are suggesting using the following connection
string :

Data Source=np:<server name>\<instance name>;Initial Catalog=<db
name>;User ID=<user id>;Password=<password>;



"John A Grandy" <johnagrandy-at-gmail-dot-com> wrote in message
news:uox9tDSvJHA.1504@xxxxxxxxxxxxxxxxxxxxxxx
Hello Sylvain, and thank for the response.

I will try definitely try your suggestion.

However, I have verified that the server is configured to accept
remote connections via both named pipes and tcp/ip. I have also
verified that both tcp/ip and named-pipes are configured as transport
protocols on the client , with tcp/ip at the top of the stack.


"Sylvain Lafontaine" <sylvainlafontaine2009@xxxxxxxx> wrote in message
news:eofIVHRvJHA.4324@xxxxxxxxxxxxxxxxxxxxxxx
I would say that SSMS is possibly using the Named Pipes protocol. Try
removing the Network Library parameter and add np: before the name of
the server.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
please)
Independent consultant and remote programming for Access and
SQL-Server (French)


"John A Grandy" <johnagrandy-at-gmail-dot-com> wrote in message
news:uDflmaKvJHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
Apparently for named instances which listen
on a non-default port ( i.e. other than 1433 ) , there is no need to
specify a port
number in the connection string ( ? )

If so, this is well and good because I am troubleshooting a network
where the Sql Server instance is reassigned a new listening port on
machine restart.

Indeed , I am able use SSMS to connect to such an instance without
specifying the port number.

However, via my WinForms .NET application using SqlConnection class
, the following connection strings do not work.


Data Source=<server name>\<instance name>;Network
Library=DBMSSOCN;Initial
Catalog=<db name>;User ID=<user id>;Password=<password>;

Data Source=<server name>\<instance name>,<port number>;Network
Library=DBMSSOCN;Initial Catalog=<db name>;User ID=<user
id>;Password=<password>;


I can connect to the server instance from the client via SSMS and
the Sql
Server login used in the connection string ( <user id>/<password> )



I am wondering : what exactly is providing this ability to resolve
the
connection request to the correct port number ?

Is it driver/provider dependent ?

Is it configuration dependent ?

Or should it "just work" out of the box for SqlConnection ? ( i.e.
I have some other problem going on )


Thanks.

















.

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I moved every table I was able to move to the SQL ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... communication between ODBC (OLEDB and Native Client, ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Resets
    ... If it were SQL that was falling short, ... The default backlog for SQL Server is 5. ... System.InvalidOperationException: Internal connection fatal error. ... From time to time, under heavy loads, we are getting resets at ...
    (microsoft.public.sqlserver.connect)
  • Re: provider: Named Pipes Provider, error: 40 - Could not open a c
    ... I have tried connecting to Sql Management Studio and it worked pretty much ... An error has occurred while establishing a connection to the server. ... under the default settings SQL Server does not allow remote connections. ... I have a sql server install / database on the web server. ...
    (microsoft.public.sqlserver.connect)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • .Net framework error
    ... Unfortunately the information is very limited and usually the report just ... The application is a front end for a SQL Server 2005 Express database, ... connection string in the app config file. ...
    (microsoft.public.dotnet.languages.vb)