Troubleshooting Azure Active Directory Integrated Authentication in Azure SQL

by Contributed | Aug 27, 2021 | Technology

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

Integrated authentication provides a secure and easy way to connect to Azure SQL Database and SQL Managed Instance. It leverages hybrid identities that coexist both on traditional Active Directory on-premises and in Azure Active Directory.

At the time of writing Azure SQL supports Azure Active Directory Integrated authentication with SQL Server Management Studio (SSMS) either by using credentials from a federated domain or via a managed domain that is configured for seamless single sign-on for pass-through and password hash authentication. More information here Configure Azure Active Directory authentication – Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics | Microsoft Docs

We recently worked on an interesting case where our customer was getting the error “Integrated Windows authentication supported only in federation flow” when trying to use AAD Integrated authentication with SSMS.

Recently they have migrated from using ADFS (Active Directory Federation Services) to SSSO for PTA (Seamless Single Sign-on for Pass-through Authentication). To troubleshoot the issue, we performed the following checks.

Validating setup for SSSO for PTA

1. Ensure you are using the latest version of Azure AD Connect


2. Validate the Azure AD Connect status with the Azure portal https://aad.portal.azure.com


3. Verify the below features are enabled
   
   
   
   • Sync Status
   
   
   • Seamless single sign-on
   
   
   • Pass-through authentication
   
   
   
   

Testing Seamless single sign on works correctly using a web browser

Follow the steps here and navigate to https://myapps.microsoft.com Be sure to either clear the browser cache or use a new private browser session with any of the supported browsers in private mode.

If you successfully signed in without providing the password, you have tested that SSSO with PTA is working correctly.

Now the question is. Why the sign in is failing with SSMS?

For that we turned to grab a capture using Fiddler

Collecting a Fiddler trace

The following link has a set of instructions on how to go about setting up Fiddler classic to collect a trace. Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW – Microsoft Tech Community

1. Once Fiddler is ready, I recommend that you pre-filter the capture by process as to only capture traffic that is originating from SSMS. That would prevent capturing traffic that is unrelated to our troubleshooting.


2. Clear the current session if there are any frames that were captured before setting the filter


3. Reproduce the issue


4. Stop the capture and save the file

When we reviewed the trace, we saw a few interesting things

We can only see a call to login.windows.net which is one of the endpoints that helps us use Azure Active Directory authentication.

For SSSO for PTA we would expect to see subsequent calls to https://autologon.microsoftazuread-sso.com which were not present in the trace.

This Azure AD URL should be present in the Intranet zone settings, and it is rolled out by a group policy object in the on premises Active Directory.

A key part on the investigation was finding that the client version is 1.0.x.x as captured on the Request Headers. This indicates the client is using the legacy Active Directory Authentication Library (ADAL)

Why is SSMS using a legacy component?

The SSMS version on the developer machine was the latest one so we needed to understand how the application is loading this library. For that we turned to Process Monitor (thanks Mark Russinovich)

We found that SSMS queries a key in the registry to find what DLL to use to support the Azure Active Directory Integrated authentication.

Using the below PowerShell cmdlets, we were able to find the location of the library on the filesystem

Set-Location -Path HKLM:
Get-ItemProperty -Path SOFTWAREWOW6432NodeMicrosoftMSADALSQL | Select-Object -Property TargetDir

Checking on the adalsql.dll details we confirmed this is the legacy library

As SSMS is a 32 bit application it loads the DLL from the SysWOW64 location. If your application is 64 bit you may opt to check the registry key HKLM:SOFTWAREMicrosoftMSADALSQL 

A clean install of the most recent version of SSMS creates a different DLL with the most up to date library

In this case the developer machine ended up having up that registry location modified and pointing to the legacy client (adalsql.dll). As the newer DLL (adal.dll) was already installed on the system the end user simply made the change to use the adal.dll on the registry.

It is important to be aware of this situation. Installing older versions of software like SSMS, SSDT (SQL Server Data Tools), Visual Studio etc. may end up modifying the registry key and pointing to the legacy ADAL client.

Cheers!

How Azure Security can help Federal Agencies meet Cybersecurity Executive Order Requirements

by Contributed | Aug 26, 2021 | Technology

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

In May 2021, the Biden Administration signed Executive Order (EO) 14028, placing cloud security at the forefront of national security. Federal agencies can tap into Microsoft’s comprehensive cloud security strategy to navigate the EO requirements with ease. The integration between Azure Security Center and Azure Sentinel allows agencies to leverage an existing, cohesive architecture of security products rather than attempting to blend various offerings. Our security products, which operate at cloud-speed, provide the needed visibility into cloud security posture while also offering remediation from the same pane of glass. Built-in automation reduces the burden on security professionals and encourages consistent, real-time responses to alerts or incidents.

The Azure Security suite helps federal agencies and partners improve their cloud security posture and stay compliant with the recent EO. While there are many areas Azure Security can support, this blog will focus on how Azure Security Center and Azure Sentinel can empower federal agencies to address the following EO goals:

• Modernize and Implement Stronger Cybersecurity Standards in the Federal Government


• Improve Detection of Cybersecurity Incidents on Federal Government Networks

Microsoft applies its industry-leading practices to Azure Security products, generating meaningful insights about security posture that simplify the process of protecting federal agencies and result in cost and time savings.

Azure Security Center (ASC) is a unified infrastructure security management system that strengthens the security posture of your data centers. Azure Defender, part of Azure Security Center, provides advanced threat protection across your hybrid workloads in the cloud – whether they’re in Azure or not – as well as on-premises.

Azure Sentinel, our cloud-native security information event management (SIEM) and security orchestration automated response (SOAR) solution is deeply integrated with Azure Security Center and provides security information event management and security orchestration automated response. 

Note: For more information on products and features available in Azure Government, please refer to: Azure service cloud feature availability for US government customers | Microsoft Docs

Modernize and Implement Stronger Cybersecurity Standards in the Federal Government

Section three of the EO emphasizes the push toward cloud adoption and the need for proper cloud security. It highlights the necessity of a federal cloud security strategy, governance framework, and reference architecture to drive cloud adoption. There are significant security benefits when using the cloud over traditional on-premises data centers by centralizing data and providing continuous monitoring and analytics.

Azure Sentinel contains workbooks, visual representations of data, that help federal agencies gain insight into their security posture. Section three of the EO mandates Zero Trust planning as a requirement, which can be daunting to implement. The Zero Trust (TIC3.0) Workbook provides a visualization of Zero Trust principles mapped to the Trusted Internet Connections (TIC) framework. After aligning TIC 3.0 Security Capabilities to Zero Trust Principles and Pillars, this workbook shares easy-to-implement recommendations, log sources, automations, and more to empower federal agencies looking to build Zero Trust into cloud readiness. Read more about the Zero Trust (TIC3.0) Workbook.

For federal agencies beginning their digital transformations, ASC provides robust features out of the box to secure your environment and accelerate secure cloud adoption by leveraging existing best practices and guardrails.

ASC continuously scans your hybrid cloud environment and provides recommendations to help you harden your attack surface against threats. Azure Security Benchmark (ASB) is the baseline and driver for these recommendations. ASB is a Microsoft-authored, Azure-specific set of guidelines for security and compliance best practices based on common compliance frameworks. Azure Security Benchmark builds on the controls from the Center for Internet Security (CIS) and the National Institute of Standards and Technology (NIST) with a focus on cloud-centric security.  ASB empowers teams to leverage the dynamic nature of the cloud and continuously deploy new resources by providing the needed visibility into the posture of these resources as well as easy to follow steps for remediation. With over 150+ built-in recommendations, ASB evaluates Azure resources across 11 controls, including network security, data protection, logging and threat detection, incident response, governance and strategy, and more.

Government agencies have complex compliance requirements that can be streamlined through Azure Security Benchmark. ASB provides federal agencies with a strong baseline to assess the health of their Azure resources. Teams can complement this visibility by including additional regulatory compliance standards or their own custom policy. Azure Security Center’s regulatory compliance dashboard provides insights into compliance posture against compliance requirements, including NIST SP 800-53, SWIFT CSP CSCF-v2020, Azure CIS 1.3.0, and more.

We recently released Regulatory Compliance in Workflow Automation, where changes in regulatory compliance standards can trigger real-time responses, such as notifying relevant stakeholders, launching a change management process, or applying specific remediation steps. Building in automation allows organizations to improve security posture by ensuring the proper steps are completed consistently and automatically, according to predefined requirements. Automation also reduces the burden on your security teams by streamlining repeatable tasks. Read more about how to build in automation for regulatory compliance.

With visibility and remediation all from the same dashboard, ASB and other out-of-the-box regulatory compliance initiatives empower security teams to get immediate, actionable insights into their security posture. Leveraging Microsoft best practices, built with Azure in mind, federal agencies can tap into the security of the cloud without committing resources to build new frameworks.

Using Azure Security Center’s regulatory compliance feature and workbooks in Azure Sentinel, federal agencies can tap into Microsoft best practices and existing frameworks, regardless of where they may be in their cloud journeys, to get and stay secure. These products not only provided heightened visibility into cloud security posture, but also provide steps for remediation to harden your attack surface and prevent attacks. These tools harness the power of automation, AI/ML, and more to reduce the burden on your security teams and allow them to focus on what matters.

Improve Detection of Cybersecurity Incidents on Federal Government Networks

The objective of section seven of the EO is to promote cross-government collaboration and information sharing by enabling a government-wide endpoint detection and response (EDR) system.

Integrating Azure Security Center and Azure Sentinel provides federal agencies with increased visibility to proactively identify threats and build in automated responses. Through Azure Sentinel, agencies can ensure they have the appropriate tools, whether that be automated responses or access to logs, to contain and remediate threats.

In addition to providing cloud security posture management, Azure Security Center has a cloud workload protection platform, commonly referred to as Azure Defender. Azure Defender provides advanced, intelligent protection for a variety of resource types, including servers, Kubernetes, container registries, SQL database servers, storage, and more. Read more about resource types covered by Azure Defender.

When Azure Defender detects an attempt to compromise your environment, it generates a security alert. Security alerts contain details of the affected resource, suggested remediation steps, and refer to recommendations to help harden your attack surface to protect against similar alerts in the future. In some scenarios, logic apps can also be triggered. Like automated responses to deviations in regulatory compliance standards, logic apps allow for consistent responses to Azure Security Center alerts.

Azure Defender not only has a breadth of coverage across many resource types, but also depth in coverage by resource type. Given the increase in frequency and complexity of attacks, organizations require dynamic threat detections. Azure Defender benefits from security research and data science teams at Microsoft who are continuously monitoring the threat landscape, leading to the constant tuning of detections as well as the inclusion of additional detections for greater coverage. Azure Defender incorporates integrated threat intelligence, behavioral analytics, and anomaly detection to identify threats across your environment.

Azure Sentinel is a central location to collect data at scale – across users, devices, applications, and infrastructure – and to conduct investigation and response.

There are two ways that Azure Sentinel can ingest data: data connectors and continuous export.

Azure Sentinel comes with built-in connectors for many Microsoft products, allowing for out-of-the-box, real-time integration. The Azure Defender connector facilitates the streaming of Azure Defender security alerts into Azure Sentinel, where you can view, analyze, and respond to alerts in a broader organizational threat context.

In addition to bringing Azure Defender alerts, organizations can stream alerts from other Microsoft products, including Microsoft 365 sources such as Office 365, Azure Active Directory, Microsoft Defender for Identity, or Microsoft Cloud App Security.

Continuous export in Azure Security Center allows for the streaming of not only Azure Defender alerts but also secure score and regulatory compliance insights.

After connecting data sources to Azure Sentinel, out-of-the-box, built-in templates guide the creation of threat detection rules. Our team of security experts created rule templates based on known threats, common attack vectors, and suspicious activity escalation chains. Creating rules based on these templates will continuously scan your environment for suspicious activity and create incidents when alerts are generated. You can couple built-in fusion technology, machine learning behavioral analytics, anomaly rules, or scheduled analytics rules with your own custom rules to ensure Azure Sentinel is scanning your environment for relevant threats.  

Automation rules in Azure Sentinel help triage incidents. These rules can automatically assign incidents to the right team, close noisy incidents or known false positives, change alert severity, or add tags.

Automation rules are also used to run playbooks in response to incidents. Playbooks, which are based on workflows built-in Azure Logic Apps, are a collection of processes that are run in response to an alert or incident. This feature allows for predefined, consistent, and automated responses to Azure Sentinel activity, reducing the burden on your security team and allowing for close to real-time responses to alerts or incidents.

Due to the integrated nature of our threat protection suite, completing investigation and remediation of an Azure Defender alert in Azure Sentinel will still update the alerts status in the Azure Security Center portal. For example, when an alert is closed in Azure Sentinel, that alert will display as closed in Azure Security Center as well (and visa versa)!

At Microsoft, we are excited about the opportunity to expand our partnerships with federal agencies as we work to improve cloud security, and in doing so, improve national security.

For more information, please visit our Cyber EO resource center.