Choosing SQL Server's Network Protocol

As you configure SQL Server, the network protocols you choose affect how you integrate SQL Server with IIS. While SQL Server works with a large variety of protocols, TCP/IP Sockets, Named Pipes, and Multi-Protocol are the most common.

The choice of protocol depends on whether or not your enterprise application requires SQL Server's integrated security features. Integrated security lets the client use a single logon account instead of requiring both a Windows NT logon and also a SQL Server logon account. With integrated security, your application takes advantage of Windows NT security features, including user names, passwords, password aging, and domain-wide user administration. SQL Server integrated security permits access to your database based solely on the Windows NT validation. Once connected to the database, access to SQL objects (such as tables, views, and stored procedures) is controlled using standard SQL Server access permissions.

In order to use SQL Server's integrated security, you must choose either Named Pipes or Multi-Protocol with Named Pipes.

Note   You can configure the SQL Server network protocols using the SQL Server Client Configuration Utility.

TCP/IP Sockets

TCP/IP Sockets is a non-authenticated protocol. This means that connections are not authenticated, and connections and access levels are determined only by the user name and password provided during the connection. TCP/IP Sockets requires more administration than Named Pipes, since each user access permission must be defined at the SQL Server.

TCP/IP Sockets is a good choice for network protocol if your application does not require SQL Server's integrated security.

Named Pipes

Named Pipes is an authenticated protocol. This means that any time a user attempts to open a connection to the SQL Server via Named Pipes, the Windows NT authentication process occurs. This is important to remember because Named Pipes is also the default protocol when installing SQL Server. Named Pipes is required to support SQL Server's integrated security option. It's also worth noting that Named Pipes connects very fast – much faster than the other protocols.

Named Pipes is an excellent choice for network protocol if your application intends to use SQL Server's integrated security and does not need other protocols for remote connections.

Multi-Protocol

The Multi-Protocol option lets a client machine connect to any SQL Server machine using any Windows NT protocol. This provides an interesting advantage over Named Pipes: with Multi-Protocol, you can connect to different servers using different protocols (including Named Pipes) all at the same time. However, if your enterprise application will run on a homogeneous network configured with a single protocol, Multi-Protocol provides no benefit.

Because of the way server names are mapped to network addresses, Multi-Protocol can sometimes be a problem. Each network protocol that the Multi-Protocol option might use has a different approach to identifying the server name. The problem here is that a single machine could have one NetBIOS name and yet another WINS name. Depending on which protocol asks the question, the name resolution could be different. Of course, this problem does not exist where the two names are identical.

Multi-Protocol is a good choice for network protocol if your application must access remote servers using several different network protocols. Where the network protocol is Named Pipes, your application can use SQL Server's integrated security option, otherwise the client will need both a Windows NT and a SQL Server logon account. This requires extra administration to define SQL Server user logon accounts.