Hello friends!
If yesterday you read my post and found it definitely boring (for the topic of course and not for how it was written to the author 😁) well today I promise you that the topic will be much more interesting!
Let's talk about Sniffing, let's talk about going to see what happens when we "log in" or when we send a Query to SQL Server.
We will do this by taking a look to the little known tabular data stream (TDS) protocol built on top of the TCP/IP protocol.
Ready!
The TDS protocol
What is the Tabular data stream (TDS) protocol?
The TDS is the protocol used by the SQL Server client Net-Library to communicates with SQL Server.
TDS is specific to SQL Server and it is a low-level protocol that specifies both commands and data in a specific arrangement.
TDS encapsulate the TCP/IP protocol.
Sniffing TDS packets you can read from the network the requests that your application do to the SQL Server and read the answer.
How to Sniff data from the network
It is actually really easy sniff data because are available many programs able to do that. I personally use Wireshark but also microsoft have a dedicated utility.
So what are you waiting? Download and install Wireshark here
Then execute WireShark:
Since WireShark supports the TDS protocol just enter the string "tds" in the filter and then press the shark fin button!
Every TDS packet will be sniffed immediately
What happens during a connection and during a query.
Note that we are sniffing data to learn how SQL Server works and not to read private data since this is a prohibited activity! so do not try this in a production environment.
Today infact i will show you briefly what happens during the log-in and when you are sending a T-SQL command to the SQL engine.
So start Wireshark, filter the TDS packet then start the SSMS and do the log in:
This is the output of the wireshark once pressed the "connect button":
Note that:
Each action (e.g. do the login) is translated into a series of request where each request has a response.
For example the client send a TDS7 pre-login request, SQL server respond with a Response.
Or you send a SQL Batch and SQL Server give you a response.
But I don't want to go into any detail on how the login procedure works.
Now let's do something more interesting.
From the SSMS the a simple select statement and see how this command is sent to SQL server.
This is the output of wireshark:
What is interesting?
Each command you send is translated into two pair of SQL Batch - Response
Why?
Because for each command you send you need to ask which SPID you are referring to.
SELECT @@SPID is the first SQL Batch
The second SQL Batch contain your statement.
Also in this case we do not go into any detail of this protocol, which however is freely available.
However, it is important to note that
If data are not encrypted (as in many management software!) anyone can intercept your data on the network because on the network ... they transit in clear text
That's all for today my friends!
And please show this blog to your SQL addicted friends!
Luca
