Speaking to Sql Server, sniffing the TDS protocol

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













Previous post:Speaking to Sql Server, between Shared Memory, Named pipe and TCP/IP protocols

Comments

  1. Home ServicesApril 12, 2021 at 4:25 AM

    Customer behaviors, habits, and schedules are changing faster than ever. This evolution means that advertisers have to find new ways to meet changing consumer demands. That's why Google Ads has introduced a tool called Performance Planner that focuses on planning your ad spend.
    https://ppcexpo.com/blog/what-can-the-performance-planner-recommend

    ReplyDelete
  2. cloudministerApril 27, 2021 at 3:36 AM

    Thanks for this. I really like what you've posted here and wish you the best of luck with this blog and thanks for sharing. Server Management Services

    ReplyDelete
  3. Jack StarkOctober 6, 2021 at 2:28 AM

    Click Here
    Click Here
    Click Here
    Click Here
    Click Here
    Click Here

    ReplyDelete
  4. Jack StarkOctober 28, 2021 at 11:15 PM

    Click here
    Click here
    Click here
    Click here
    Click here
    Click here
    Click here

    ReplyDelete
  5. Jack StarkNovember 3, 2021 at 1:06 AM

    Click here

    ReplyDelete
  6. sarahjohnNovember 30, 2021 at 4:31 AM

    Click here

    ReplyDelete
  7. UnknownDecember 14, 2021 at 9:12 AM

    In my early 19s I was diagnosed of HERPES VIRUS 2 and I was sadden by the situation and so I kept on searching for a way to rid the virus but all my effort was to no avail, I almost gave in to the no cure attitude but at that frustrated time, I stumbled on a post like this about a doctor who have cure for different STDs/STIs and his email was given so I decided to give him a try still on a broken heart but he assured me that my virus will be gone only if I key in to his instructions which I did diligently because I was tired of suffering, To my greatest amusement, I began to see no symptoms of the virus within two(2) weeks of using the herbal concoction that was given to me by this great man, After a month and two weeks of treatment I decided to go for check up and for God so kind, there was no trace of the virus as I was tested negative of the virus, I also went for virus STDs test and all came out negative, To me it was a miracle and so I choose to share his contact so that through him others suffering from similar problem will be free, here is his email: droyamasolutiontemple@gmail.com, or WhatsApp number +2348108264684 also has the cure for HPV,CANCER,HPV,HIV/AIDS Thanks for taking time to read to the end.









    In my early 19s I was diagnosed of HERPES VIRUS 2 and I was sadden by the situation and so I kept on searching for a way to rid the virus but all my effort was to no avail, I almost gave in to the no cure attitude but at that frustrated time, I stumbled on a post like this about a doctor who have cure for different STDs/STIs and his email was given so I decided to give him a try still on a broken heart but he assured me that my virus will be gone only if I key in to his instructions which I did diligently because I was tired of suffering, To my greatest amusement, I began to see no symptoms of the virus within two(2) weeks of using the herbal concoction that was given to me by this great man, After a month and two weeks of treatment I decided to go for check up and for God so kind, there was no trace of the virus as I was tested negative of the virus, I also went for virus STDs test and all came out negative, To me it was a miracle and so I choose to share his contact so that through him others suffering from similar problem will be free, here is his email: droyamasolutiontemple@gmail.com, or WhatsApp number +2348108264684 also has the cure for HPV,CANCER,HPV,HIV/AIDS Thanks for taking time to read to the end.

    ReplyDelete
  8. Jack StarkDecember 16, 2021 at 2:46 AM

    Click here
    Click here
    Click here
    Click here
    Click here
    Click here
    Click here
    Click here
    Click here

    ReplyDelete
  9. appfinz technologiesAugust 9, 2022 at 3:45 AM


    Thanks for your valuable post, Really nice and very informative.
    website designing company in delhi

    ReplyDelete
  10. kingpure ss water tanksAugust 9, 2022 at 3:45 AM

    Very Nice Website, I really Appreciate your contents, this is very meaning for us.
    Stainless Steel Water Tank

    ReplyDelete
  11. kingpure ss water tanksAugust 9, 2022 at 3:48 AM

    Thank you for very useful information.
    Luxury Series Stainless Steel Water Tanks in India

    ReplyDelete
  12. AnonymousNovember 22, 2022 at 6:28 PM

    We must be ready to face our problems and find solution to them 7 months i was diagnosed with herpes and I was lonely and sad I wanted to be healed and did all I could but I could not be healed luckily a friend of mine directed me to a very kind and great DR Alli who helped me and health have been restored thank you so much DR Alli Email him via Allispellhelp1@gmail.com or whatsApp him on +2348100772528 you are a blessing to me.
    HE CAN ALSO CURE SICKNESS LIKE
    {1} Espergers
    {2] Hepatitis b
    {3} HIV/AIDS
    {4} DIABETES
    {5} EPILEPSY
    {6} BLOOD CANCER
    {7} HPV
    {8} ALS

    ReplyDelete
  13. AnonymousDecember 29, 2022 at 2:45 AM

    Very Nice Website, I really Appreciate your contents, this is very meaning for us.
    pinkroot

    ReplyDelete

Post a Comment

I Post più popolari

SQL Server, execution plan and the lazy spool (clearly explained)

Image
Hi Guys, Welcome back! In one of the last posts we talked about Table Spool . We had explained what Spool are for by telling the somewhat curious story of the halloween problem . Here we specifically talked about a type of spool called Eager Spool   You can look to the execution plan below where the eager Spool is used to avoid the halloween problem in classic query of the "salary increase".. i suggest Well, if the story of the eager spool intrigued you, today instead we will speak about another type of Spool, the Lazy Spool . The Lazy spool Let's take a small step back. So, what is a Spool operation?  As we have already understood a spool operation is simply a temporary storage of data . Data are read from a source table and stored inside a worktable in the Tempdb database . While an Eager Spool is used to prevent the Halloween problem, the Lazy Spool is instead used to store data that will later be needed again when running a query . So, when a Lazy spool is used?    I
Read more

SQL Server, datetime vs. datetime2

Image
Hi guys, This time, in this post we will speak about datetime and datetime2 datatypes. We will talk about the pros and cons. We will talk about all the differences between the two types of data. Write me in the comments which type of data do you prefer between the two. Enjoy the reading!     Datetime Vs. Datetime2 First of all, it must be said that the datatype datetime is certainly the best known native format for storing a date in SQL Server. However, Microsoft has also been introducing a new type of data for many years now, also used to manage dates. It is in fact from SQL server 2008 that it is present in datetime2 data type. This "new" type of data was born primarily to have higher accuracy . Let's in fact talk about ... precision ! Datetime Certainly the datetime data type is not very precise and certainly there are many cases in which its precision is simply not sufficient. Let's see it with an example. Open the SQL Management studio (SSMS) and write the fo
Read more

How to solve EXECUTE Permission denied on object 'sp_send_dbmail'

Image
Hi guys, Welcome back ! Today a light five minute post to talk about this error: EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'. ..or in italian language: Autorizzazione execute negata per l'oggetto 'sp_send_dbmail' del database 'msdb' con schema 'dbo' The sp_send_dbmail You know that this stored procedure is used to send a mail through SQL server. You know also that this stored procedure is contained into the MSDB system Databases. But if you get the error above? What can you do? How to solve the execute permission denied on sp_send_dbmail You should consider that: Your user must be in the MSDB database.  Your user must be also member of the DatabaseMailUserRole .  You can solve the problem in this way: 1) In the Object Explorer of the SQL Server management Studio, locate the Security / Account menu. Locate your user and do a double click on it. 2) In the opene
Read more