locked
How to capture login user name of SQLServer connection from network traffic? RRS feed

  • Question

  • I want to install a network traffic sniffer on my SQLServer server, to capture SQL query information and its login user info(password is not needed).

    Problem is that the connect packets are encrypted by TLS/SSL. By default, SQLServer generates self-issued certificate to encrypt connect packets. 
    To make it possible to decrypt TLS/SSL packets, I created a self-issued certificate by makecert tools(http://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx), and set it to SQLServer instance: 
    SQLServer configuration manager -> Protocols for MSSQLServer -> Properties -> certificate

    I captured network traffic by Microsoft Network Monitor, and succeeded to get SQL query sentences since data communication packets is not encrypted as default, but failed to get login user name because connect packets is encrypted by TLS/SSL.

    I thought it can be decrypted since I have the self-issued certification, I tried NmDecrypt expert of Network monitor, but it cannot accept .cer type certificate. It means the above efforts all failed?

    Is there any way to decrypt login user name from network traffic? Or is the scenario forbidden for security?

    Any suggestion are appreciated.


    developer of windows application

    Thursday, September 13, 2012 3:23 AM

Answers

  • I want to install a network traffic sniffer on my SQLServer server, to capture SQL query information and its login user info(password is not needed).

    Hello,

    Why that complicated; use the SQL Server Profiler and/or SQL Traces to capture it.


    Olaf Helper
    Blog Xing

    • Proposed as answer by Elmozamil Elamir Thursday, September 13, 2012 5:40 AM
    • Marked as answer by Shulei Chen Thursday, September 20, 2012 10:21 AM
    Thursday, September 13, 2012 4:07 AM

All replies

  • I want to install a network traffic sniffer on my SQLServer server, to capture SQL query information and its login user info(password is not needed).

    Hello,

    Why that complicated; use the SQL Server Profiler and/or SQL Traces to capture it.


    Olaf Helper
    Blog Xing

    • Proposed as answer by Elmozamil Elamir Thursday, September 13, 2012 5:40 AM
    • Marked as answer by Shulei Chen Thursday, September 20, 2012 10:21 AM
    Thursday, September 13, 2012 4:07 AM
  • Please, take a look at login audits.

    SSMS - connect to your server - Properties - Security - Login auditing - Both fail and successful logins

    It will generate an entry on Windows Event Logs (under Security) for every login to SQL.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Thursday, September 13, 2012 12:56 PM
  • I have same question

    I want to install a network traffic sniffer on my SQLServer server, to capture SQL query information and its login user info(password is needed).

    Thursday, December 3, 2015 12:34 PM
  • user info(password is needed).

    And wouldn't this be a GIGANTIC SECURITY problem if this were possible? No, you CANNOT gain access to the password.

    Thursday, December 3, 2015 1:40 PM
  • ...

    Is there any way to decrypt login user name from network traffic? Or is the scenario forbidden for security?

    ...

    I don't now if cracking passwords is allowed in China. In most countries there are laws that render such activity illegally. Now that may be different if it's the server you "own". But still for the very reason nobody will give you instructions on how to crack SSL in a public Microsoft Forum.

    If you want to find out who is accessing your SQL Server, you can do that using Extended Events. With Extended Events, being the successor of the old and deprecated Profile/SQL Trace you can also get the query texts easily.

    Auditing is a simple option if you are interested in login attempts and logouts in general.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform, MCM, MVP
    www.SarpedonQualityLab.com | www.SQL-Server-Master-Class.com

    Thursday, December 3, 2015 5:35 PM