Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am using VSTS 2008 + C# + ADO.Net + SQL Server 2008. When connecting from another remote machine, normally TCP/IP protocol is used by ADO.Net client to connect to SQL Server 2008. When connecting locally (ADO.Net client and SQL Server on the same machine), normally shared memory or named pipe is used.

My question is, besides the general rules, how to check exactly (e.g. from some SQL Server built-in commands/tools/store procedures?) what communication protocol is used by a specific connection?

thanks in advance, George

share|improve this question

2 Answers 2

up vote 2 down vote accepted

If you have the SPID of the process in question (from using something like Activity Monitor or sp_who or sp_who2), then you can perform this query on the server:

SELECT * 
FROM sys.dm_exec_connections 
WHERE session_id = (SPID);

replacing (SPID) with the actual SPID of your client process. Look at the net_transport column, that will tell you which protocol that process is using to communicate with SQL Server. If all you are interested in is the protocol, just use SELECT net_transport instead of SELECT *.

You could also filter your query by other columns, such as client_net_address which, for TCP connections, will be the IP address of your client machine. You could send this query via ADO.NET interfaces. SQL SMO might also have something.

Here's the MSDN reference page for sys.dm_exec_connections. Note that you will need VIEW SERVER STATE rights on the server to see more than just your current session.

You can also specify the transport protocol in the server directive of your connection string using the following syntax:

server=[protocol]:servername

Examples:

server=tcp:mysqlinstance1 (for TCP/IP)
server=np:mysqlinstance1 (for named pipes)
server=np:\\mysqlinstance1\pipe\pipename (for a specific pipe)
share|improve this answer
    
Thanks! How to get the SPID of current connection? Could I got it from ADO.Net client or from server? –  George2 Oct 22 '09 at 11:48
    
I have tried to execute SELECT * FROM sys.dm_exec_connections, it has net_transport column, but from the MSDN link, seems there is no such column described? Confused. Any comments? –  George2 Oct 22 '09 at 11:50
    
My bad, I linked to sys.dm_exec_sessions by accident. I fixed the link to point to the right page. –  squillman Oct 22 '09 at 13:22
1  
You might not even need to get the SPID. I've edited my answer with some other info. –  squillman Oct 22 '09 at 15:16
    
Thanks squillman, my question and real scenario is, from ADO.Net client side, in the connection string ADO.Net client is using, there is no protocol information. So, in this scenario I need to know how to match session_id or SPID (from rows in SELECT * FROM sys.dm_exec_connections) to a specific ADO.Net client application. Any ideas how to match session_id to a specific ADO.Net client application? –  George2 Oct 23 '09 at 4:55

As a side note: TCP/IP or named pipes are the transport layer protocol and tabular data stream is the communication (application layer) protocol in SQL Server.

http://en.wikipedia.org/wiki/Tabular%5FData%5FStream

share|improve this answer
1  
Not that this answers your question, but as a clarification. –  joeqwerty Oct 22 '09 at 12:08
    
I am asking for transport layer protocol, in more details, for 4 types of communication protocols, shared memory/named pipe/TCP IP/VIA, I want to know for a specific connection, which protocol is used. Any ideas or comments? –  George2 Oct 22 '09 at 12:18
1  
I just ran the query squillman posted on my SQL 2008 server and the net transport column is definitely there. Have you actually tried the query on your server? –  joeqwerty Oct 22 '09 at 12:43
1  
As a side note, shared memory is not a communication\network protocol so you will never see any non-local clients (network clients) connecting to SQL via shared memory. –  joeqwerty Oct 23 '09 at 1:38
    
Yes, I tried. And I want to learn more about the function and meaning about net_transport column. Are there any descirption (document) about the meaning of column net_transport? I did not find such information from MSDN. –  George2 Oct 23 '09 at 4:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.