locked
If there are some features to track user's login and logoff activities for Azure Sql Database? RRS feed

  • Question

  • I saw that there is a audit feature for azure sql database. I've checked this feature and found that the audit log have users login, login fail and any query operations records. But i didn't find any logoff activities from audit log. If it possible to view or track user's logoff activities?
    Friday, May 22, 2020 9:55 AM

Answers

  • Hi, Thank you for your replly.

        My case is we will add the extended event session on Azure Sql Database. So, about Azure Sql Database, we can only use queries to implement it. Actually, i am not very good at it. So, if you can provide some guideness links or docs for this topic. Much appreciate for it. 

    Hi,

    Microsoft provides a very short tutorial for Extended Event in this page:
    https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server

    It is not the best tutorial in my opinion, but can help you to create your first Extended Event monitoring.

    I searched Google and unfortunately I did not like any of the tutorials which I found and checked. All these tutorials focuses on using the SSMS GUI, which in my opinion is not a good way to learn and understand what the GUI actually do for you. You must understand what is and how tro use Extended Event before solving specific issue using copy/paste of code which you do not understand. You must find a tutorial which focus on transact SQL demonstration in my opinion.

    I cannot write and add in a forum thread a full tutorial, and there are many online :-)

    What I can do is to provide you a full step-by-step demonstration of your scenario and includes the full solution which you are looking for. It is recommended to first learn about Extended Event before you go top my post

    Here is a full scenario and solution:

    Azure SQL Database - Audit LOGOUT using Extended Events


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, May 28, 2020 10:21 AM

All replies

  • I saw that there is a audit feature for azure sql database. I've checked this feature and found that the audit log have users login, login fail and any query operations records. But i didn't find any logoff activities from audit log. If it possible to view or track user's logoff activities?

    Good day Leon,

    You can use Extended Events and monitor the event: sqlserver.logout

    According to the documentation it is supported in Azure Database

    https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/audit-logout-event-class


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Friday, May 22, 2020 10:12 AM
  • Hello, thanks for your quick response. I am trying to create new extended event session by follow the officila document, but i can not find the entrance. 
    Monday, May 25, 2020 10:42 AM
  • Hello, thanks for your quick response. I am trying to create new extended event session by follow the officila document, but i can not find the entrance. 

    hi,

    what do you mean by "entrance"? Do you mean the event name?

    I highly recommend to create first on-premises for test and once it is working well do the same in the Azure. Make sure you use queries and not wizards 


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, May 25, 2020 11:06 AM
  • Hi, Thank you for your replly.

        My case is we will add the extended event session on Azure Sql Database. So, about Azure Sql Database, we can only use queries to implement it. Actually, i am not very good at it. So, if you can provide some guideness links or docs for this topic. Much appreciate for it. 

    Wednesday, May 27, 2020 8:19 AM
  • Here is a link to the documentation for enabling Auditing on your database.

    Once you have the audit log, here is the documentation to help you query the log.

    Wednesday, May 27, 2020 1:01 PM
  • Hi, Thank you for your replly.

        My case is we will add the extended event session on Azure Sql Database. So, about Azure Sql Database, we can only use queries to implement it. Actually, i am not very good at it. So, if you can provide some guideness links or docs for this topic. Much appreciate for it. 

    Hi,

    Microsoft provides a very short tutorial for Extended Event in this page:
    https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server

    It is not the best tutorial in my opinion, but can help you to create your first Extended Event monitoring.

    I searched Google and unfortunately I did not like any of the tutorials which I found and checked. All these tutorials focuses on using the SSMS GUI, which in my opinion is not a good way to learn and understand what the GUI actually do for you. You must understand what is and how tro use Extended Event before solving specific issue using copy/paste of code which you do not understand. You must find a tutorial which focus on transact SQL demonstration in my opinion.

    I cannot write and add in a forum thread a full tutorial, and there are many online :-)

    What I can do is to provide you a full step-by-step demonstration of your scenario and includes the full solution which you are looking for. It is recommended to first learn about Extended Event before you go top my post

    Here is a full scenario and solution:

    Azure SQL Database - Audit LOGOUT using Extended Events


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Thursday, May 28, 2020 10:21 AM
  • Hi, Thanks so much. Currently, I have already updated the audit policy by REST Api to track user's logout activity. But i don't know why i still can not see any logout event from the Audit logs. By the way, i use azure blob storage to store the audit log.
    Friday, May 29, 2020 10:15 AM
  • Hi, Thanks so much. Currently, I have already updated the audit policy by REST Api to track user's logout activity. But i don't know why i still can not see any logout event from the Audit logs. By the way, i use azure blob storage to store the audit log.
    I have no idea what you did obviously since we cannot read minds. Therefore, I cannot know what is not working as your expectation based o he information you provided here. You will need to provide more information and make some effort if you want others to have the understanding of your current scenario.

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, May 30, 2020 1:20 AM
  • Hello, Thanks for your information. I've tried by follow this Link(Azure SQL Database - Audit LOGOUT using Extended Events) and it turn out to be worked. Thanks so much.

    Monday, June 1, 2020 9:26 AM