ConnectionString SQL Server
by Sebastian Affakes
To explicit define the network protcol to use when connecting to an SQL Server gives you more control over your connection. This is a nifty guide for doing that.
Introduction
Specifying the network protocol isn't hard at all. Knowing about the underlying communication can improve performance and helps to avoid connection issues. Especially in a dev-prod issue situation. Shared Memory connections are great when it comes to performance but can be used only if the application and the database instance is located on the same machine. TCP/IP is often the best for a firewalled environment.
Example
This connection string exemplifies how the network specification can look like inside the connection string.
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=myUsername;Password=myPassword;"
"Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=myUsername;Password=myPassword;"
Network protocol codes
| Name | Network library |
|---|---|
| dbnmpntw | Named Pipes |
| dbmslpcn | Shared Memory (local machine connections only, might fail when moving to production...) |
| dbmssocn | Winsock TCP/IP |
| dbmsspxn | SPX/IPX |
| dbmsvinn | Banyan Vines |
| dbmsrpcn | Multi-Protocol (Windows RPC) |
| dbmsadsn | Apple Talk |
| dbmsgnet | VIA |
Important note
When connecting through the SQLOLEDB provider use the syntax "Network Library=dbmssocn" and
when connecting through the MSDASQL provider use the syntax "Network=dbmssocn".
when connecting through the MSDASQL provider use the syntax "Network=dbmssocn".
Summary
Using the most appropriate network protocol will improve your application and gives you, as a developer, insight in the underlying connection mechanism. It's definetely a developer-operations peace maker.