Configuring Windows Server 2022 for Windows Authentication to SQL Managed Instance

This comprehensive guide walks through the essential steps to enable Windows Authentication from Windows Server 2022 to Azure SQL Managed Instance using Microsoft Entra ID and the modern interactive authentication flow.
Prerequisites and System Requirements

For Windows Server 2022 to successfully connect to Azure SQL Managed Instance using Windows Authentication, several prerequisites must be met:

• Operating System: Windows Server 2022 or higher is required for the modern interactive authentication flow
• Microsoft Entra ID Integration: On-premises Active Directory must be synchronized with Microsoft Entra ID using Microsoft Entra Connect
• Device Join Status: The server must be Microsoft Entra hybrid joined or Microsoft Entra joined
• Interactive Authentication: Applications must connect via interactive sessions (supports SSMS and web applications, but not service applications)

Ensuring Microsoft Entra Hybrid Join Status

Verification Commands

To verify that your Windows Server 2022 is properly Microsoft Entra hybrid joined, use the following diagnostic command:

dsregcmd.exe /status

A properly configured hybrid-joined device should show:

AzureAdJoined: YES

DomainJoined: YES

DeviceId: A valid GUID value
Addressing Pending State Issues

If devices appear in a "pending" state in the Microsoft Entra admin center, this indicates that the hybrid join process hasn't completed successfully. Common scenarios include:

• New Domain-Joined Devices: Devices that can't complete the registration process due to connectivity or configuration issues
• Previously Registered Devices: Devices that were moved between organizational units or had their registration disrupted

Resolving Pending State with Scheduled Tasks

For machines in a pending state, the Automatic-Device-Join scheduled task is crucial for completing the hybrid join process. This task is located under:

Task Scheduler Library > Microsoft > Windows > Workplace Join > Automatic-Device-Join
Enabling the Scheduled Task

If the task is disabled (which can happen on some systems), enable it using PowerShell:

Enable-ScheduledTask -TaskPath "\Microsoft\Windows\Workplace Join\" -TaskName "Automatic-Device-Join"

Manual Task Execution

To trigger the device registration process immediately, run the task manually:

schtasks.exe /run /tn "Microsoft\Windows\Workplace Join\Automatic-Device-Join"

Important Notes:

• This command requires local administrator privileges.
• The user must be signed in for the task to complete successfully.
• The task may need to run twice: once to generate the userCertificate attribute, and again after Azure AD Connect synchronization to complete registration.

Understanding the Device Registration Process

The hybrid join process involves multiple components working together:

• Automatic-Device-Join Task: Runs at user logon and generates device certificates
• Azure AD Connect: Synchronizes device information from on-premises AD to Microsoft Entra ID
• userCertificate Attribute: Generated by the device and stored in Active Directory for Azure AD Connect to sync

The task triggers on:

• User logon events
• Event ID 4096 in the Microsoft > Windows > User Device Registration log
• Hourly intervals

Configuring Group Policy for Kerberos Authentication

Essential Group Policy Setting

To enable the modern interactive authentication flow for Windows Authentication to SQL Managed Instance, configure the following group policy setting:

Path: Administrative Templates\System\Kerberos\Allow retrieving the cloud Kerberos ticket during the logon

Configuration Steps

Open Group Policy Editor on your domain controller or management system
Navigate to the Policy Path:

• Computer Configuration
• Administrative Templates
• System
• Kerberos
• Configure the Setting:
• Select "Allow retrieving the cloud kerberos ticket during the logon"
• Set the policy to Enabled
• Click OK to save the configuration

Registry Implementation

This policy setting configures the following registry value:

Registry Hive: HKEY_LOCAL_MACHINE
Registry Path: Software\Microsoft\Windows\CurrentVersion\Policies\System\Kerberos\Parameters
Value Name: CloudKerberosTicketRetrievalEnabled
Value Type: REG_DWORD
Enabled Value: 1

After enabling the group policy, users with existing logon sessions may need to refresh their Microsoft Entra Primary Refresh Token (PRT):

dsregcmd.exe /RefreshPrt

This command should be run from an elevated command prompt to ensure the PRT is properly refreshed.

Verification and Testing

To verify that Kerberos tickets are being retrieved correctly, use the following command after user logon:

klist

You should see Kerberos tickets from kerberos.microsoftonline.com indicating that cloud Kerberos authentication is functioning.

Authentication Flow Overview

The modern interactive authentication flow enables the following process:

1. Client Authentication: Windows Server 2022 authenticates to Microsoft Entra ID using hybrid join credentials
2. Kerberos Ticket Request: The system requests a cloud Kerberos ticket during logon
3. SQL Authentication: Applications use the cloud Kerberos ticket to authenticate to Azure SQL Managed Instance
4. Seamless Access: Users experience single sign-on without requiring line-of-sight to domain controllers

This configuration eliminates the need for traditional trust relationships and enables secure authentication to cloud resources while maintaining familiar Windows Authentication experiences for applications and users.

Troubleshooting Common Issues

Device Registration Problems

Monitor the following event logs for troubleshooting device registration issues:
Event Viewer Path: Microsoft > Windows > User Device Registration > Admin
Key Events: Look for events 204, 304, and 4096
Group Policy Application

• Ensure group policy is properly applied by:
• Running gpupdate /force on target servers
• Verifying the registry value is set correctly

Checking that the policy applies to the correct organizational units

Network Connectivity

Verify that Windows Server 2022 can reach the required Microsoft endpoints for device registration and Kerberos authentication:

• https://enterpriseregistration.windows.net
• https://login.microsoftonline.com
• https://device.login.microsoftonline.com

By following these comprehensive steps, Windows Server 2022 systems will be properly configured to use Windows Authentication with Azure SQL Managed Instance through the modern interactive authentication flow, providing seamless and secure access to cloud database resources.

Securing API Access to SharePoint Online: A Guide to Azure Application Registration and SharePoint App Creation

Gaining API access to a SharePoint environment is essential for developers looking to build applications that interact with SharePoint data. This can be achieved in two primary ways: through Azure Application registration or by creating an application directly in SharePoint. Each method has its own set of steps and considerations. In this blog, we will explore both methods and guide you through the process of setting up and securing API access to your SharePoint environment.

Method 1: Azure Application Registration

Azure Active Directory (Azure AD) offers a secure way for applications to access SharePoint through Azure Application registration. Here's how you can set it up:

1. Navigate to Azure Portal: Start by going to the Azure portal and creating a new application registration. This will represent your application in the directory and will be the basis for its authentication and authorization processes.

2. Configure Application Settings: Assign a name to your application and configure settings such as supported account types and redirect URIs. These settings will determine how your application interacts with users and other services.

3. Set API Permissions: The most crucial step is setting the API permissions for your application. Navigate to the "API permissions" section and add permissions for SharePoint. For read-only access, you will typically add permissions such as Sites.Read.All or Files.Read.All. Ensure you understand the scope and implications of each permission you grant.

Method 2: Creating an Application in SharePoint

For those who prefer to work directly within the SharePoint environment, creating an application in SharePoint is a viable alternative. Here's how to do it:

1. Access SharePoint Admin Center: Log into your SharePoint admin center and navigate to the 'appregnew.aspx' page (https://<main-site name>.sharepoint.com/_layouts/15/appregnew.aspx ). This is where you'll register your new application. This is needed only if the Azure AD APPlication is not created.

1. Generate Client ID & Secret: Click on the "Generate" button to create a new client ID and secret. These credentials will be used to authenticate your application with SharePoint.

Once the Application is created through Method 1 or Method 2 next step is to provide right access to the same. 

Next step is granting permissions to the newly created principal. Since we're granting tenant scoped permissions this granting can only be done via the appinv.aspx page on the tenant administration site. You can reach this site via https://sitename-admin.sharepoint.com/_layouts/15/appinv.aspx. 

Once the page is loaded add your client id and look up the created principal:

Set Permissions: Define the permissions your application will need by providing the permission XML. For full control permissions, you might use something like:

<AppPermissionRequests AllowAppOnlyPolicy="true"> <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" /> </AppPermissionRequests>

When you click on Create you'll be presented with a permission consent dialog. Press Trust It to grant the permissions:

Best Practices and Considerations

• Security: Regardless of the method you choose, security should be your top priority. Treat your client ID and secret as you would any sensitive credentials. Ensure that only authorized personnel have access to this information and that it's stored securely.

• Permission Scope: Always adhere to the principle of least privilege. Grant only the permissions necessary for your application to function. Excessive permissions can pose a security risk.

• Maintenance: Regularly review and update your application's settings and permissions to accommodate changes in your environment or application requirements.

• Documentation: Both Microsoft's Azure documentation and SharePoint documentation are excellent resources. Refer to these regularly to stay updated on best practices and new features.

By following these steps and considerations, you can successfully set up API access to your SharePoint environment, whether through Azure Application registration or directly within SharePoint. Each method has its own benefits and the best choice depends on your specific needs and environment. Always prioritize security and stay informed on best practices to ensure a successful and safe integration.

Using Azure Lighthouse for Monitoring Other Tenant.

Azure Lighthouse is a service provided by Microsoft Azure that allows service providers to manage multiple customers’ Azure services from a single control plane. It provides a centralized portal to manage multiple Azure tenants, customers or subscriptions, giving the service provider a single view of all Azure resources across different customer environments. Azure Lighthouse provides several features including delegated resource management, multi-tenant management, and cross-tenant management, which help service providers to manage resources across their entire customer base in a secure and efficient manner. It simplifies and streamlines the management of Azure services, provides greater visibility into customers’ environments, and enables service providers to deliver better services to their customers.

In Short, we give access to a subscription or resource to a User/Group in another tenant with specific Roles. 

Sample ARM Template for adding a Customer account to Service Providers Lighthouse. 

"managedByTenantId": "a86bc255-XXXX-CCCC-VVVV-51fba84872aa"

Above is the Tenant ID of the Managed Services Provider. 

"defaultValue": "XXXXXXXXXXXXXXXXXXXXXXX"

Above is the Name of the Offering.

defaultValue": "YYYYYYYYYYYYYYYYYYYYYYYYYY"

Above is the Description of the Offering.

"principalId": "9d45cb5e-4682-4a4d-b54a-a89e3fa7bc84",

Above is the Object ID of the User or the Group we are selecting for this Offering.

"roleDefinitionId": "acdd72a7-3385-48ef-bd42-f606fba81ae7",

Above is the ID of the Role we are selecting.

"principalIdDisplayName": "Azure Reader Access"

Above is the Name of the Role we are selecting.

Sample ARM

{

"$schema": "https://schema.management.azure.com/schemas/2019-08-01/subscriptionDeploymentTemplate.json#",

"contentVersion": "1.0.0.0",

"parameters": {

"mspOfferName": {

"type": "string",

"metadata": {

"description": "Specify a unique name for your offer"

},

"defaultValue": "XXXXXXXXXXXXXXXXXXXXXXX"

},

"mspOfferDescription": {

"type": "string",

"metadata": {

"description": "Name of the Managed Service Provider offering"

},

"defaultValue": "YYYYYYYYYYYYYYYYYYYYYYYYYY"

}

},

"variables": {

"mspRegistrationName": "[guid(parameters('mspOfferName'))]",

"mspAssignmentName": "[guid(parameters('mspOfferName'))]",

"managedByTenantId": "a86bc255-XXXX-CCCC-VVVV-51fba84872aa",

"authorizations": [

{

"principalId": "9d45cb5e-AAAA-BBBB-CCCCC-DDDDDDDD",

"roleDefinitionId": "acdd72a7-3385-48ef-bd42-f606fba81ae7",

"principalIdDisplayName": "Azure Reader Access"

},

{

"principalId": "9d45cb5e-AAAA-BBBB-CCCCC-DDDDDDDD",

"roleDefinitionId": "cfd33db0-3dd1-45e3-aa9d-cdbdf3b6f24e",

"principalIdDisplayName": "Support Request Contributor"

}

]

},

"resources": [

{

"type": "Microsoft.ManagedServices/registrationDefinitions",

"apiVersion": "2020-02-01-preview",

"name": "[variables('mspRegistrationName')]",

"properties": {

"registrationDefinitionName": "[parameters('mspOfferName')]",

"description": "[parameters('mspOfferDescription')]",

"managedByTenantId": "[variables('managedByTenantId')]",

"authorizations": "[variables('authorizations')]"

}

},

{

"type": "Microsoft.ManagedServices/registrationAssignments",

"apiVersion": "2020-02-01-preview",

"name": "[variables('mspAssignmentName')]",

"dependsOn": [

"[resourceId('Microsoft.ManagedServices/registrationDefinitions/', variables('mspRegistrationName'))]"

],

"properties": {

"registrationDefinitionId": "[resourceId('Microsoft.ManagedServices/registrationDefinitions/', variables('mspRegistrationName'))]"

}

}

],

"outputs": {

"mspOfferName": {

"type": "string",

"value": "[concat('Managed by', ' ', parameters('mspOfferName'))]"

},

"authorizations": {

"type": "array",

"value": "[variables('authorizations')]"

}

}

}

Configure AWS Login With Azure AD Enterprise App

The idea is to enable users to sign in to AWS using their Azure AD credentials. This can be achieved by configuring single sign-on (SSO) between Azure AD and AWS. The process involves creating an enterprise application in Azure AD and configuring the AWS application to use Azure AD as the identity provider. Once this is set up, users can log in to AWS using their Azure AD credentials and access the AWS resources that they have been authorized to use. The tutorial provided by Microsoft explains the steps involved in setting up this SSO configuration between Azure AD and AWS.

https://docs.microsoft.com/en-us/azure/active-directory/saas-apps/amazon-web-service-tutorial

1. Azure >> Enterprise APP >> <<Configure Azure AD SSO
1. Deploy Amazon Web Services Developer App
2. Single Sign On >>.SAML
1. Popup to save
1. Identifier: https://signin.aws.amazon.com/saml
2. Reply URL: https://signin.aws.amazon.com/saml
2. Save
3. SAML Signing Certificate
1. Download "Federation Metadata XML"
4. Add the AD user's to Application's User' and Group
2. AWS >> IAM >> Identity provider
1. Create
1. SAML
2. AZADAWS
3. Upload the Metadata XML
2. Verify Create
3. AWS>> IAM >> ROLE << This Role will Come in Azure Application
1. SAML 2.0 Federations
1. Choose :  Earlier Created Identity provider
2. Allow programmatic and AWS Management Console access
3. Choose required permissions
4. Create the role with Appropriate name
4. AWS >> IAM >> POLICIES <<  This policy will allow to fetch the roles from AWS accounts.
1. Choose JSON
1. { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "iam:ListRoles" ], "Resource": "*" } ] }
2. Name : AzureAD_SSOUserRole_Policy.
3. Create the Policy
5. AWS >> IAM >> USER
1. Name : AzureADRoleManager
2. Choose Programmatic access
3. Permission : Attach existing polices
1. Choose : AzureAD_SSOUserRole_Policy
4. Create User
5. Copy Access and Secret key
6. Azure Enterprise App >> Choose Amazon Web Services App which was deployed
1. Provisoing
1. Make it automatic
2. Give Aws Access and Secret key
3. Test and Save
4. Make the "Provisioning Status" to ON
5. Wait for a sync to complete
6. Once Sync is Completed got the user's and Groups
1. Choose the user, select Click EDIT
2. Choose the AWS Role

Azure AD integration to GCP Cloud for SSO

Idea is to enable SSO to GCP cloud with Azure AD configuration

Make sure cloud identity is subscripted in GCP account and we have a super admin user in that account.

Also the same domain is verified in both Azure and GCP.

Note: If Same domain is verified in any other Gsuit or GCP account, that should be used.

Base Document Followed

https://cloud.google.com/solutions/federating-gcp-with-azure-ad-configuring-provisioning-and-single-sign-on

https://social.msdn.microsoft.com/Forums/en-US/0ac3b510-382e-48fc-8612-9aea20d8a040/issues-with-azuread-and-gsuite-sso?forum=WindowsAzureAD

Process.

In GCP: Create 1 Super admin in Google env (Super admin is only available in admin.google.com which is available only if Gsuite or if Cloud Identity is register.)

In Azure:  Create 1 Application for the User Provisioning.

Make sure the user has been created in GCP user portal. Admin.google.com

In Azure Create Second App

We will face login error after configuring as per the GCP document.  Errors have been listed below. To solve we need add the Identifier and Reply URL.

Errors faced

Error1:

AADSTS650056: Misconfigured application. This could be due to one of the following: The client has not listed any permissions for 'AAD Graph' in the requested permissions in the client's application registration. Or, The admin has not consented in the tenant. Or, Check the application identifier in the request to ensure it matches the configured client application identifier. Please contact your admin to fix the configuration or consent on behalf of the tenant. Client app ID: 01303a13-8322-4e06-bee5-80d612907131.

https://social.msdn.microsoft.com/Forums/en-US/0ac3b510-382e-48fc-8612-9aea20d8a040/issues-with-azuread-and-gsuite-sso?forum=WindowsAzureAD

Solution :  In SAML Config : add Identifier (Entity ID) :  google.com/a/<Domain Name>

Error2:

AADSTS900561: The endpoint only accepts POST requests. Received a GET request.

Solution :  In SAML Config : add Reply URL :  https://google.com/a/*