Sql-server – How to configure a connection string in SSRS 2012 using named pipes

sql serversql-server-2012ssrs

I have SQL Sever 2012 and I want to create a data source that uses named pipes rather than TCP.

This is when I have tried:

Data Source=np:\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;Initial Catalog=cleverdataout

If I test the connection, I get an error message, with error code 25, indicating that the connection string is not valid.

Is there a way to use named pipes? Or am I forced to use TCP?

Best Answer

You'll need to indicate that you want to use the named pipes provider in the connection string.

Consider this VB.Net code that connects to my local SQL Server 2012 instance via Named Pipes:

Module Module1

    Sub Main()
        Dim cb As New SqlClient.SqlConnectionStringBuilder With {
            .DataSource = "\\.\pipe\MSSQL$MV2012\sql\query",
            .NetworkLibrary = "dbnmpntw ",
            .IntegratedSecurity = True
            }
        Using conn As New SqlClient.SqlConnection(cb.ConnectionString)
            conn.Open()
            Using cmd As SqlClient.SqlCommand = conn.CreateCommand
                cmd.CommandText = "SELECT ServerName = @@SERVERNAME;"
                cmd.CommandType = CommandType.Text
                Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader
                While reader.Read
                    Console.WriteLine(reader.GetString(reader.GetOrdinal("ServerName")))
                End While
            End Using
        End Using
    End Sub

End Module

The .NetWorkLibrary = "dbnmpntw" indicates that I'm going to use the Named Pipes Provider, as detailed in this Microsoft Docs page.

I validated that the pipe name, \\.\pipe\MSSQL$MV2012\sql\query, is correct by looking at the SQL Server Configuration Manager under "SQL Server Network Configuration -> Protocols for MV2012 -> Named Pipes Properties".

I'd say your connection string needs to be (I've wrapped the text simply for clarity):

Data Source=\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;
Network Library=dbnmpntw;
Initial Catalog=cleverdataout

FYI, the \\. piece indicates the name of the server where SQL Server is installed. In this example, that indicates the localhost server. If you are connecting via named pipes remotely, you'd need to replace the . with the name of the server. So, if the server was MySQLServer, then you'd need the pipe name to be: \\MySQLServer\pipe\MSSQL$MSSQLSERVER02\sql\query.

The MSSQL$MSSQLSERVER02 part of the named pipe in your question indicates that you are attempting to connect to a SQL Server Named Instance named MSSQLSERVER02. If your SQL Server is installed as the default instance, i.e. with no name, then you'd need to specify the pipe like:

\\MySQLServer\pipe\sql\query

Note, for this to work you'll need to enable the File and Printer Sharing (SMB-In) Windows firewall rule that allows inbound TCP traffic via port 445 at the server.

Related Question