Export (0) Print
Expand All
2 out of 7 rated this helpful - Rate this topic

Creating a Valid Connection String Using Named Pipes

Unless changed by the user, when the default instance of Microsoft SQL Server listens on the named pipes protocol, it uses \\.\pipe\sql\query as the pipe name. The period indicates that the computer is the local computer, pipe indicates that the connection is a named pipe, and sql\query is the name of the pipe. To connect to the default pipe, the alias must have \\<computer_name>\pipe\sql\query as the pipe name. If SQL Server has been configured to listen on a different pipe, the pipe name must use that pipe. For instance, if SQL Server is using \\.\pipe\unit\app as the pipe, the alias must use \\<computer_name>\pipe\unit\app as the pipe name.

To create a valid pipe name, you must:

  • Specify an Alias Name.

  • Select Named Pipes as the Protocol.

  • Enter the Pipe Name. Alternatively, you can leave Pipe Name blank and SQL Server Configuration Manager will complete the appropriate pipe name after you specify the Protocol and Server

  • Specify a Server. For a named instance you can provide a server name and instance name.

At the time of connection, the SQL Server Native Client component reads the server, protocol, and pipe name values from the registry for the specified alias name, and creates a pipe name in the format np:\\<computer_name>\pipe\<pipename> or np:\\<IPAddress>\pipe\<pipename>. For a named instance, the default pipe name is \\<computer_name>\pipe\MSSQL$<instance_name>\sql\query.

NoteNote

Microsoft Windows XP Service Pack 2 enables Windows Firewall, which closes port 445 by default. Because MicrosoftSQL Server communicates over port 445, you must reopen the port if SQL Server is configured to listen for incoming client connections using named pipes. For information on configuring a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online or review your firewall documentation.

When connecting to SQL Server running on the same computer as the client, you can use (local) as the server name. Using (local) is not encouraged because it leads to ambiguity; however it can be useful when the client is known to be running on the intended computer. For instance, when creating an application for mobile disconnected users, such as a sales force, where SQL Server will run on laptop computers and store project data, a client connecting to (local) would always connect to the SQL Server running on the laptop. The word localhost or a period (.) can be used in place of (local).

The following query will return the protocol used for the current connection.

SELECT net_transport 
FROM sys.dm_exec_connections 
WHERE session_id = @@SPID;

Connecting by server name to the default pipe:

Alias Name         <serveralias>
Pipe Name          <blank>
Protocol           Named Pipes
Server             <servername>

Connecting by IP Address to the default pipe:

Alias Name         <serveralias>
Pipe Name          <leave blank>
Protocol           Named Pipes
Server             <IPAddress>

Connecting by server name to a non-default pipe:

Alias Name         <serveralias>
Pipe Name          \\<servername>\pipe\unit\app
Protocol           Named Pipes
Server             <servername>

Connecting by server name to a named instance:

Alias Name         <serveralias>
Pipe Name          \\<servername>\pipe\MSSQL$<instancename>\SQL\query
Protocol           Named Pipes
Server             <servername>

Connecting to the local computer using localhost:

Alias Name         <serveralias>
Pipe Name          <blank>
Protocol           Named Pipes
Server             localhost

Connecting to the local computer using a period:

Alias Name         <serveralias>
Pipe Name          <left blank>
Protocol           Named Pipes
Server             .

NoteNote

To specify the network protocol as a sqlcmd parameter, see "How to: Connect to the Database Engine Using sqlcmd.exe" in SQL Server Books Online.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.