求助：MVC应用中如何将SQL Server无密码用户迁移至Azure Active Directory

Alright, let's break this down step by step—you're trying to generate random passwords for your existing SQL Server users and bulk migrate them to Azure AD, but hit a wall with the JSON method. Let's fix this and get you up and running:

First, we need to create secure, Azure AD-compliant passwords for your users and update the SQL table. Azure AD requires passwords to be at least 8 characters long, with a mix of uppercase, lowercase, numbers, and/or special characters. Here's a SQL script to generate and set these passwords:

-- Update users with random 8-character passwords meeting Azure AD complexity rules
UPDATE Users
SET Password = CONCAT(
    -- Random uppercase letter (A-Z)
    CHAR(ROUND(RAND(CHECKSUM(NEWID())) * 25 + 65, 0)),
    -- Random lowercase letter (a-z)
    CHAR(ROUND(RAND(CHECKSUM(NEWID())) * 25 + 97, 0)),
    -- Random number (0-9)
    CHAR(ROUND(RAND(CHECKSUM(NEWID())) * 9 + 48, 0)),
    -- 5 random alphanumeric characters to reach 8 total
    SUBSTRING(CONVERT(VARCHAR(255), NEWID()), 1, 5)
)
WHERE Password IS NULL OR Password = '';

You can adjust the length by modifying the substring length or adding more character groups if needed. After running this, verify a few rows to ensure passwords are generated correctly.

The key issue here: Azure AD does NOT support bulk user import via JSON—it uses a standardized CSV template. This is likely why your previous attempt failed. Here's how to get your data into the right format:

1. Download the official Azure AD bulk import template from the Azure Portal:
   • Go to Azure Active Directory > Users > Bulk operations > Bulk import users > Download template
2. Export your SQL user data to match this template's columns. You can do this via SSMS (right-click the Users table > Tasks > Export Data) or with a SQL script like this (adjust columns to match your table and the template):

-- Export users to CSV matching Azure AD template
SELECT
    'Create' AS [Operation],
    EmailAddress AS [User name], -- Must be a valid email (matches Azure AD domain if using custom domains)
    FirstName AS [First name],
    LastName AS [Last name],
    Password AS [Password],
    'Enabled' AS [Account enabled],
    'User' AS [User type],
    EmailAddress AS [Alternative email address]
    -- Add other optional columns (department, city, etc.) from the template if needed
FROM Users
-- Use SSMS Export Wizard or bcp command to save this as a CSV file

Make sure to map your SQL columns exactly to the template's column names—any mismatches will cause import failures.

Now that you have a valid CSV, follow these steps to import:

• Go back to Azure Active Directory > Users > Bulk operations > Bulk import users
• Upload your CSV file, then click Validate to catch any formatting or data issues (like invalid emails or non-compliant passwords)
• Fix any validation errors, then run the import
• Check the Bulk operation results page to see successful/failed imports. For failures, the portal will provide a detailed error file to troubleshoot.

Once users are migrated, you'll want to update your MVC app to use Azure AD for authentication. Here's a quick setup:

1. Install the Microsoft.Identity.Web NuGet package in your MVC project
2. Update appsettings.json with your Azure AD app details (register an app in Azure AD first to get these values):

"AzureAd": {
  "Instance": "https://login.microsoftonline.com/",
  "Domain": "your-domain.onmicrosoft.com",
  "TenantId": "your-tenant-guid",
  "ClientId": "your-app-client-guid",
  "CallbackPath": "/signin-oidc"
}

3. Configure authentication in Program.cs:

builder.Services.AddAuthentication(OpenIdConnectDefaults.AuthenticationScheme)
    .AddMicrosoftIdentityWebApp(builder.Configuration.GetSection("AzureAd"));

builder.Services.AddAuthorization(options =>
{
    options.FallbackPolicy = options.DefaultPolicy;
});

// Add this after building the app
app.UseAuthentication();
app.UseAuthorization();

4. Add the [Authorize] attribute to controllers or actions you want to protect.

内容的提问来源于stack exchange，提问作者thanawalad