Lesson Learned #344:Managed Instance needs permissions to access Azure Active Directory.

This article is contributed. See the original author and article here.

Today, we worked on a service request that our customer got the following error message : Managed Instance needs permissions to access Azure Active Directory. You need to be a ‘Company Administrator’ or a ‘Global Administrator’ to grant ‘Read’ permissions to the Managed Instance.


 


Azure SQL Managed Instance needs permissions to read Azure AD to successfully accomplish tasks such as authentication of users through security group membership or creation of new users. For this to work, we need to grant the Azure SQL Managed Instance permission to read Azure AD.


 


We can do this using the Azure portal or PowerShell. This operation can only be executed by Global Administrator or a Privileged Role Administrator in Azure AD.


 


You can assign the Directory Readers role to a group in Azure AD. The group owners can then add the managed instance identity as a member of this group, which would allow you to provision an Azure AD admin for the SQL Managed Instance. That means you need to have Global Administrator or Privileged Role Administrator access to provide the read permission to the SQL MI.


 


Directory Reader role


 


In order to assign the Directory Readers role to an identity, a user with Global Administrator or Privileged Role Administrator permissions is needed. Users who often manage or deploy SQL Database, SQL Managed Instance, or Azure Synapse may not have access to these highly privileged roles. This can often cause complications for users that create unplanned Azure SQL resources, or need help from highly privileged role members that are often inaccessible in large organizations.
For SQL Managed Instance, the Directory Readers role must be assigned to managed instance identity before you can set up an Azure AD admin for the managed instance.
 
Assigning the Directory Readers role to the server identity isn’t required for SQL Database or Azure Synapse when setting up an Azure AD admin for the logical server. However, to enable an Azure AD object creation in SQL Database or Azure Synapse on behalf of an Azure AD application, the Directory Readers role is required. If the role isn’t assigned to the SQL logical server identity, creating Azure AD users in Azure SQL will fail. For more information, see Azure Active Directory service principal with Azure SQL.
 
Supported Article: https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-directory-readers-role?view=azuresql#assigning-the-directory-readers-role

Don’t let change pass you by! Get started with Change Tracking in your SQL Database | Data Exposed

This article is contributed. See the original author and article here.

Many database administrators ask questions like “What rows have changed for a table?” and “How has that row changed in that table?”. Change Tracking is a lightweight solution built right into the SQL Database that gives you the ability to query for data that has changed over time. In this episode of Data Exposed, join Anna Hoffman and Brian Spendolini as we explore this powerful feature of the database. Learn how to enable Change Tracking in your database, what are the best uses cases, and how it can save you massive amounts of time and effort over developing custom, one-off solutions.


 


Watch on Data Exposed


 


Resources:



 


View/share our latest episodes on Microsoft Learn and YouTube!

IMPORTANT: Support for Office 2013 has ended

This article is contributed. See the original author and article here.

As mentioned in previous posts, 11/11/2021 and on 11/15/2022, Office 2013 reached the end of the Extended Support lifecycle on April 11, 2023. Continuing to use Office 2013 could increase your organization’s exposure to security risks, impact your ability to meet compliance obligations, and/or affect end user productivity.


 


Additionally, support for other Microsoft Office products is also coming to an end in the next months. Please review the following list and act before the end of the product’s lifecycle:



  • Office 2019 for Mac reaches end of support on October 10, 2023. This means Office 2019 for Mac will no longer receive security updates, bug fixes, technical support, or online technical content support.

  • Connecting Office 2016 and Office 2019 to Microsoft 365 reaches end of support on October 10, 2023. After this end date we won’t block these Office versions from connecting to Microsoft 365 services if they are kept up to date. But after October 10, 2023, improvements to Microsoft 365 services will no longer be tested with these Office versions, so, users could experience performance or reliability issues. Read more about this in our Microsoft Learn article.


 


If you’re running a version affected by any of the end of support dates, we recommend upgrading to Microsoft 365 E3, which comes with Microsoft 365 Apps – the apps you’re familiar with (e.g., Word, Excel, PowerPoint, Outlook, etc.). It falls under the Modern Lifecycle Policy, so it’s continuously supported.


 


Here are some resources to help plan the move:



 


Please visit our Office End of Support community for more information and resources about end of support for Office.


 


Thanks again for being a Microsoft customer!

Public Preview: Authenticator Lite (in Outlook)

Public Preview: Authenticator Lite (in Outlook)

This article is contributed. See the original author and article here.

Two years ago, we shared that “It’s Time to Hang Up on Phone Transports for Authentication.” Today, we’re adding the public preview of Authenticator Lite to the tools we are offering to help you move from text message (SMS) and voice-based authentication. Our priority is getting every user to sign in with modern strong authentication – passwordless, hardened against phishing, easy to use and adaptable to evolving attacks. 


 


Our top recommendation for modern strong authentication is the Authenticator, which offers the most robust security features, updated the most frequently, for free. Microsoft Authenticator app has over 100 million users worldwide who trust it as a secure and easy way to authenticate, making it the most popular way to sign in with strong authentication in Azure.  


 


Because modern strong authentication is so important, we’re making it even more accessible by embedding it right into the Outlook client! We call this embedded experience Authenticator Lite – and we’re excited to announce it is now in public preview! For users that haven’t yet downloaded Authenticator, they can now complete MFA for their work or school account for free using the Outlook app on their iOS or Android devices. Users can approve authentication requests and receive TOTP codes, bringing the security of Authenticator to a convenient location while simplifying users’ move off phone transports for authentication. 


 


During public preview, admins can choose to enable or disable this capability for a group of users or to leave the feature in a Microsoft managed state. Enabling a group for Authenticator Lite is possible from the Entra portal via the Authenticator configuration page.  It’s also possible to enable the feature through MS Graph.


 


SHDriggers_0-1681480846426.png


 


 


Authenticator Lite, as the name suggests, will extend a subset of the Authenticator’s capabilities into Outlook. Each verification notification will include a number matching prompt and biometric or pin verification if enabled on the device. More information on the Authenticator Lite notification configurations can be found here. 


 


Once enabled for Authenticator Lite, users on the latest version of Outlook without the Authenticator app will be prompted to register Outlook as an MFA method when they launch the app on their device.  


 


SHDriggers_1-1681480846433.png


 


 


Once users are registered, during their next authentication, users will be prompted to authenticate using a push notification in their Outlook app.  


 


SHDriggers_2-1681480846456.png


 


 


Registered users will also have access to a TOTP code found in their Outlook settings under Authenticator.  


 


SHDriggers_3-1681480846461.png


 


 


For more information on enabling this feature for your users, see here. Rollout to support this feature in Outlook is currently underway. 


 


This feature will roll out to tenants in the state Microsoft managed. For the duration of public preview, leaving the feature set to ‘Microsoft managed’ will have no impact on your users and the feature will remain turned off unless you explicitly change the state to enabled. In late April 2023, we will remove preview tags and enter general availability. On May 26, 2023, if the feature is left set to ‘Microsoft managed,’ your tenant will be enabled for Authenticator Lite by Microsoft. If you do not wish for this feature to be enabled on May 26, set the state to ‘disabled’ or assign users to include and exclude groups prior to May 26 


 


We hope you and your users enjoy this new feature, and, as always, please let us know of any questions or feedback by leaving comments down below or reaching out to us at aka.ms/AzureADFeedback. 
 


Regards, 


Alex Weinert


VP Director of Identity Security, Microsoft   


Microsoft Identity Division 


 


 


Learn more about Microsoft identity:  


Enrich your advanced hunting experience using network layer signals from Zeek

Enrich your advanced hunting experience using network layer signals from Zeek

This article is contributed. See the original author and article here.

In our previous blog about hunting for network signatures in Microsoft 365 Defender, we described how we used device discovery capabilities to capture some network event information in deeper detail and expose them in advanced hunting with the NetworkSignatureInspected action type. Since then we have made several developments, the most significant being the integration with Zeek. This release has expanded what is possible for generating network detections across Microsoft Defender for Endpoint. That announcement, shared examples of detections created for PrintNightmare and NTLM password spraying attempts.


 


Today, we would like to share a variety of Zeek-based events in advanced hunting that will help you expand your investigation, hunting, and detection capabilities for identifying and addressing network-layer anomalies across HTTP, SSH and ICMP protocols. Using the new Zeek events, we will demonstrate how to perform network threat hunting while also covering some of the MITRE ATT&CK Matrix.


 


Note: As the integration with Zeek continues to mature, more action types will gradually be released over time. With the Zeek integration only supported on Windows devices, these action types will surface for connections to and from Windows device.


 


To identify these action types in your tenant, look for the value ConnectionInspected in the ActionType field of the DeviceNetworkEvents table of advanced hunting. The extra information is stored in the AdditionalFields column as a JSON data structure and has the commonly known Zeek fields per event, which can be parsed. These field names are identical to those that Zeek uses, which are documented on Zeek’s site. You can also check the Schema Reference flyout page on the advanced hunting pages to check for any new action types that were recently released.


 


Link to query


DeviceNetworkEvents


| where ActionType contains ‘ConnectionInspected’


| distinct ActionType


 


The result of this query looks something like this:


 


cventour_0-1681377541830.png


Figure 1 – Sample result upon checking for ConnectionInspected in the ActionType table


 


The format of the action type will follow the [Protocol_Name]ConnectionInspected standard.


 


Inspecting HTTP connections


 


The HttpConnectionInspected action type contains extra information about HTTP connections, inbound or outbound. In cases where you click on an event of the HttpConnectionInspected action type, the page flyout will parse the additional fields and present them in a  format like the example below:


 


cventour_1-1681378349897.png


 


Figure 2 – Sample result of an HttpConnectionInspected action type


 


Below, you will find a complete list of fields that this action type can expose and the respective descriptions:


 


























































Field Name



Description



direction



The direction of the conversation relevant to the Microsoft Defender for Endpoint-onboarded device, where the values are either ‘In’ or ‘Out’



host



The host header content



method



The HTTP method requested



request_body_len



Length of the HTTP message body in bytes



response_body_len



Length of the HTTP response body in bytes



status_code



The HTTP response code



status_msg



The full text message of the response



tags



A set of indicators of various attributes discovered and related to a particular request/response pair.



trans_depth



Represents the pipelined depth into the connection of the request/response transaction



uri



The complete URI that was requested



user_agent



The user_agent header of the request



version



The HTTP version used



 


Let’s look at a few examples of using the HttpConnectionInspected action type. In the first example, you want to look for rare user agents in the environment to identify potentially suspicious outbound web requests and cover the “T1071.001: (Application Layer Protocol) Web Protocols” technique.


 


Link to query


// Identify rare User Agent strings used in http conversations


DeviceNetworkEvents


| where ActionType == ‘HttpConnectionInspected’


| extend json = todynamic(AdditionalFields)


| extend direction = tostring(json.direction), user_agent = tostring(json.user_agent)


| where direction == ‘Out’


| summarize Devices = dcount(DeviceId) by user_agent


| sort by Devices asc


 


Suppose you have identified a suspicious-looking user-agent named “TrickXYZ 1.0” and need to determine which user/process/commandline combination had initiated that connection.  Currently, the HttpConnectionInspected events, as with all Zeek-related action types, do not contain that information, so you must execute a follow-up query by joining with events from  ConnectionEstablished action type. Here’s an example of a follow-up query:


 


Link to query


// Identify usage of a suspicious user agent


DeviceNetworkEvents


| where Timestamp > ago(1h) and ActionType == “HttpConnectionInspected”


| extend json = todynamic(AdditionalFields)


| extend user_agent = tostring(json.user_agent)


| where user_agent == “TrickXYZ”


| project ActionType,AdditionalFields, LocalIP,LocalPort,RemoteIP,RemotePort, TimeKey = bin(Timestamp, 5m)


| join kind = inner (


DeviceNetworkEvents


| where Timestamp > ago(1h) and ActionType == “ConnectionSuccess”


| extend TimeKey = bin(Timestamp, 5m)) on LocalIP,RemoteIP,LocalPort,TimeKey


| project DeviceId, ActionType, AdditionalFields, LocalIP,LocalPort,RemoteIP,RemotePort , InitiatingProcessId,InitiatingProcessFileName,TimeKey


 


In another example, let’s look for file downloads from HTTP, particularly files of executable and compressed file extensions to cover the “T1105: Ingress tool transfer” technique:


 


Link to query


// Detect file downloads


DeviceNetworkEvents


| where ActionType == ‘HttpConnectionInspected’


| extend json = todynamic(AdditionalFields)


| extend direction= tostring(json.direction), user_agent=tostring(json.user_agent), uri=tostring(json.uri)


| where uri matches regex @”.(?:dll|exe|zip|7z|ps1|ps|bat|sh)$”


 


The new HTTP action type will unlock a variety of possibilities for detection on this protocol. We  look forward to seeing the queries you come up with by sharing your contributions with the community.


 


Looking at SSH connections


 


The SshConnectionInspected action type will display information on SSH connections. While decrypting the entire SSH traffic is not possible, the cleartext part of the SSH session initiation can provide valuable insights. Let’s look at the data found in the AdditionalFields section.


 


cventour_0-1681379880041.png


Figure 3 – Screenshot of additional fields that SshConnectionInspected generates.


 


The fields depend on the activity that was observed. Some of these fields might not appear depending on the connection. For example, if the client disconnected before completing the authentication, you will not have an auth_success field populated for that event..


 


Below, you will find a complete list of fields that this action type can expose and the respective descriptions:


 










































Field Name



Description



direction



The direction of the conversation relevant to the Defender for Endpoint-onboarded device, where the values are either ‘In’ or ‘Out’



auth_attempts



The number of authentication attempts until the success or failure of the attempted session.



auth_success



The success or failure in authentication, where ‘true’ means successful user authentication and ‘false’ means the user-provided credentials are incorrect.



client



The version and type of client used to authenticate to the SSH session.



host_key



Host public key value



server



SSH server information



version



SSH protocol major version used



uid



The unique ID of the SSH session attempt



 


Let’s look at a few advanced hunting examples using this action type. In the first example, you want to look for potentially infected devices trying to perform “T1110: Brute-Force” against remote servers using SSH as an initial step to “T1021.004: Lateral Movement – Remote Services: SSH”.


 


The query below will give you a list of Local/Remote IP combinations with at least 12 failed attempts (three failed authentications on four sessions) of SSH connections in the last hour. Feel free to use this example and adapt it to your needs.


 


Link to query


// Detect potential bruteforce/dictionary attacks against SSH


DeviceNetworkEvents


| where ActionType == ‘SshConnectionInspected’


| extend json = todynamic(AdditionalFields)


| extend direction=tostring(json.direction), auth_attempts = toint(json.auth_attempts), auth_success=tostring(json.auth_success)


| where auth_success==’false’


| where auth_attempts > 3


| summarize count() by LocalIP, RemoteIP


| where count_ > 4


| sort by count_ desc


 


In the next example, let’s suppose you are looking to identify potentially vulnerable SSH versions and detect potentially unauthorized client software being used to initiate SSH connections and operating systems that are hosting SSH server services in your environment:


 


Link to query


// Identify Server/Client pairs being used for SSH connections


DeviceNetworkEvents


| where  ActionType == “SshConnectionInspected”


| extend json = todynamic(AdditionalFields)


| project Server = tostring(json.server),Client = tostring(json.client)


| distinct Server ,Client


 


cventour_1-1681380056116.png


Figure 4 – An example result with a short description of the different components


 


The results above describe breaking down the SSH banners to identify the different components. A short analysis of the banners shows that the server is Ubuntu 22.04, running OpenSSH version 8.9, and the client software is WinSCP version 5.21.3. Now, you can search these versions online to verify if they are vulnerable.


 


Note: The query above can be used to surface potential “T1046: Network Service Discovery” attempts, as attackers may try to search for unpatched or vulnerable SSH services to compromise.


 


Reviewing ICMP connections


 


The IcmpConnectionInspected action type will provide details about ICMP-related activity. The breadth of fields generated creates opportunities for some interesting detections. Here’s an example of the human-readable view of the event as shown on the event flyout page


 


cventour_2-1681380100285.png


 


 Below, you will find a complete list of fields that this action type can expose and the respective descriptions:


 






















































Field Name



Description



direction



The direction of the conversation relevant to the Defender for Endpoint-onboarded device, where the values are either ‘In’ or ‘Out’



conn_state



The state of the connection. In the screenshot example OTH means that no SYN packet was seen. Read the Zeek documentation for more information on conn_state.



duration



The length of the connection, measured in seconds



missed_bytes



Indicates the number of bytes missed in content gaps, representing packet loss. 



orig_bytes



The number of payload bytes the originator sent. For example, in ICMP this designates the payload size of the ICMP packet.



orig_ip_bytes



The number of IP level bytes that the originator sent as seen on the wire and taken from the IP total_length header field.



orig_pkts



The number of packets that the originator sent.



resp_bytes



The number of payload bytes the responder sent.



resp_ip_bytes



The number of IP level bytes that the responder sent as seen on the wire.



resp_pkts



The number of packets that the responder sent. 



Uid



Unique Zeek ID of the transaction.



 


Let’s explore a few examples of hunting queries that you can use to leverage the ICMP connection information collected by Defender for Endpoint.


 


In the first example, you wish to look for potential data leakage via ICMP to cover the “T1048: Exfiltration Over Alternative Protocol” or “T1041: Exfiltration Over C2 Channel” techniques. The idea is to look for outbound connections and check the payload bytes a device sends in a given timeframe. We will parse the direction, orig_bytes, and duration fields and look for conversations over 100 seconds where more than 500,000 were sent. The numbers are used as an example and do not necessarily indicate malicious activity. Usually, you will see the download and upload are almost equal for ICMP traffic because most devices generate “ICMP reply” with the same payload that was observed on the “ICMP echo” request.


 


Link to query


// search for high upload over ICMP


DeviceNetworkEvents


| where ActionType == “IcmpConnectionInspected”


| extend json = todynamic(AdditionalFields)


| extend Upload = tolong(json[‘orig_bytes’]), Download = tolong(json[‘resp_bytes’]), Direction = tostring(json.direction), Duration = tolong(json.duration)


| where Direction == “Out” and Duration > 100 and Upload > 500000


| top 10 by Upload


| project RemoteIP, LocalIP, Upload = format_bytes(Upload, 2, “MB”), Download = format_bytes(Download, 2, “MB”),Direction,Duration,Timestamp,DeviceId,DeviceName


 


Below is an example result after exfiltrating a large file over ICMP to another device on the network:


 


cventour_3-1681380100287.png


 


In the last example, you wish to create another hunting query that helps you detect potential Ping sweep activities in your environment to cover the “T1018: Remote System Discovery” and “T1595: Active Scanning” techniques. The query will look for outbound ICMP traffic to internal IP addresses, create an array of the targeted IPs reached from the same source IP, and display them if the same source IP has pinged more than 5 IP Addresses within a 10-minute time window.


 


Link to query


// Search for ping scans


DeviceNetworkEvents


| where ActionType == “IcmpConnectionInspected”


| extend json = todynamic(AdditionalFields)


| extend Direction = json.direction


| where Direction == “Out” and ipv4_is_private(RemoteIP)


| summarize IpsList = make_set(RemoteIP) by DeviceId, bin(Timestamp, 10m)


| where array_length(IpsList) > 5


 


Identifying the origin process of ICMP traffic can be challenging as ICMP is an IP-Layer protocol. Still, we can use some OS-level indications to narrow down our search. We can use the following query to identify which process-loaded network, or even ICMP-specific, binaries:


 


Link to query


DeviceImageLoadEvents


| where FileName =~ “icmp.dll” or FileName =~ “Iphlpapi.dll”


 


More information


 


Understand which versions of the Microsoft Defender for Endpoint agent support the new integration here:



Find out more details about the integration in our ZeekWeek 2022 presentations:



View the open-source contribution in Zeek’s GitHub repository:



Previous announcements: