Scan MS SQL

Prepare Windows Jumpbox VM

Prepare a Windows-based Jumpbox VM with:

• Network access to SQL Server instances you want to assess with the following ports open:
  • Port 1433 (TCP) - SQL Server default port (required for default instances)
  • Port 1434 (UDP) - SQL Server Browser (required for named instances)
  • Custom ports - If SQL Server uses non-default ports, allow those specific TCP ports
• At least 20 GB free disk space
• Administrator privileges (required for Windows Integrated Authentication)
• .NET Runtime 8.0 or above (required for Database Assessment)

Network and Firewall Requirements

DMC requires network connectivity to SQL Server instances on specific ports. Configure your firewall and SQL Server to allow these connections.

Required Ports:

1. Port 1433 (TCP) - SQL Server default port

   • Required for connecting to default SQL Server instances
   • Used for all SQL Server communication once the connection is established

2. Port 1434 (UDP) - SQL Server Browser

   • Required for discovering and connecting to named SQL Server instances
   • Used by SQL Server Browser service to identify which port a named instance is using

3. Custom Ports

   • If SQL Server is configured to use non-default ports, ensure those specific TCP ports are open in your firewall

SQL Server Requirements:

• TCP/IP Protocol Enabled: SQL Server must have TCP/IP protocol enabled for remote connections

  • Open SQL Server Configuration Manager
  • Navigate to: SQL Server Network Configuration → Protocols for [INSTANCE]
  • Ensure TCP/IP is enabled
  • Restart SQL Server service after enabling

• SQL Server Browser Service: Required for named instances

  • The SQL Server Browser service must be running to discover named instances
  • Enable and start the service:

    Set-Service -Name SQLBrowser -StartupType Automatic
    Start-Service -Name SQLBrowser

Firewall Configuration:

Configure Windows Firewall on both the jumpbox and SQL Server machines to allow SQL Server connections:

# On the SQL Server machine - Allow SQL Server default port
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# On the SQL Server machine - Allow SQL Server Browser
New-NetFirewallRule -DisplayName "SQL Server Browser" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow

Testing Connectivity:

Before running the assessment, verify network connectivity:

# Test TCP port 1433 connection
Test-NetConnection -ComputerName SQLSERVERNAME -Port 1433

# Test UDP port 1434 (SQL Server Browser)
# Note: Test-NetConnection doesn't support UDP, use telnet or other tools

Windows Account Permissions Requirements

When using Windows Authentication for SQL Server assessment, the Windows account (domain or local) used for authentication must have specific permissions:

On the Jumpbox (where DMC is running):

• The Windows account must have “Log on as a batch job” rights. This is required because DMC runs SqlAssessment.exe as the specified Windows user, which requires this logon type.

On SQL Server:

• The Windows account must have appropriate SQL Server permissions:
  • Member of sysadmin server role, or
  • Custom account with these permissions:
    • VIEW SERVER STATE
    • VIEW ANY DATABASE
    • CONNECT SQL

Install .NET Runtime 8.0 or Above (Required)

Database Assessment requires .NET Runtime 8.0 or higher to be installed on your system. The Database Assessment feature requires .NET Runtime version 8.0 or higher to function properly.

Installation Options:

Option 1: Install Before Starting (Recommended)

1. Download .NET Runtime 8.0 or later from the official .NET download page
2. Run the installer
3. Restart your computer if prompted
4. Launch DMC and proceed with Database Assessment

Option 2: Install via DMC UI If you select Database Assessment without .NET Runtime installed:

1. A modal dialog will appear: ".NET Runtime Required"
2. Click “Download .NET Runtime” button (downloads latest .NET 10.0)
3. Run the downloaded installer
4. Click “Retry” in the modal to continue
5. If .NET is not detected after installation, restart DMC for changes to take effect

Verify .Net Installation

• After installation, DMC will automatically detect .NET Runtime
• If not detected, restart DMC to refresh the detection
• The modal will not appear if .NET Runtime 8.0+ is already installed

Review Access Requirements

For details, see the System and Access Requirements section.

Run DMC as Administrator

Run DMC.exe from your installation directory as Administrator.

Input License Key

Enter the DMC license key you received when downloading DMC.

Select Database Assessment Mode

Click Database Assessment to begin configuring your SQL Server assessment.

Discover SQL Instances

To automatically discover SQL Server instances, complete at least one VMware or Physical scan first. However, you can also add SQL Server instances manually or via CSV import without running infrastructure scans.

Set Output Directory

Before proceeding with scan configuration, you can specify where scan results will be saved:

1. Review Default Location: The default output directory is C:\temp
2. Change Directory (optional):
   • Click the Browse button next to the output directory field
   • Navigate to your desired directory
   • Select the folder and click OK
3. Verify Path: Confirm the path displayed in the input field is correct

Review Existing Scans

The “Existing Scans Found” section displays any previous scan results in the selected output directory:

• If scans are found: You’ll see a count of available scans (e.g., “1 scans found in C:\temp”) and a table listing scan details
• If no scans are found: The section displays “No previous scans found in this directory”

Discover SQL Servers

DMC provides three options for discovering SQL Server instances. Select one of the tabs below to see instructions for your chosen method:

Select Servers for Assessment

After discovering or adding SQL Server instances:

1. Review the list of SQL Server instances
2. Use checkboxes to select which instances you want to assess
3. Use the search box to filter servers if you have many instances
4. Use pagination controls if you have more than 10 servers

Click Next when you have selected the SQL Server instances you want to assess.

# Grant "Log on as a batch job" right to a domain account
$user = "DOMAIN\username"  # Replace with your domain account
$tempPath = [System.IO.Path]::GetTempFileName()
secedit /export /cfg $tempPath
$content = Get-Content $tempPath
if ($content -notmatch "SeBatchLogonRight\s*=\s*.*$user") {
    if ($content -match "SeBatchLogonRight\s*=") {
        $content = $content -replace "(SeBatchLogonRight\s*=\s*)(.*)", "`$1`$2,$user"
    } else {
        $content += "`nSeBatchLogonRight = $user"
    }
    Set-Content -Path $tempPath -Value $content
    secedit /configure /db "$env:windir\security\local.sdb" /cfg $tempPath
}
Remove-Item $tempPath

# Grant "Log on as a batch job" right to a local account
$user = ".\username"  # Or use "SERVERNAME\username" - Replace with your local account
$tempPath = [System.IO.Path]::GetTempFileName()
secedit /export /cfg $tempPath
$content = Get-Content $tempPath
if ($content -notmatch "SeBatchLogonRight\s*=\s*.*$user") {
    if ($content -match "SeBatchLogonRight\s*=") {
        $content = $content -replace "(SeBatchLogonRight\s*=\s*)(.*)", "`$1`$2,$user"
    } else {
        $content += "`nSeBatchLogonRight = $user"
    }
    Set-Content -Path $tempPath -Value $content
    secedit /configure /db "$env:windir\security\local.sdb" /cfg $tempPath
}
Remove-Item $tempPath

Manage Credentials

After adding credentials, they appear in the Database Credentials table. The table displays:

• Name - The friendly name you assigned to the credential
• Username - The username format (shows domain\username for Windows Auth, or plain username for SQL Auth)
• Authentication Type - Displays the authentication method as a colored tag:
  • Windows Authentication (Domain or Local Machine)
  • SQL Authentication
• Actions - Edit and Delete buttons for each credential

Managing Credentials:

• Edit credentials by clicking the edit icon (pencil button) - modify the credential name, username, or password
• Delete credentials by clicking the delete icon (trash button) - remove credentials you no longer need
• View all configured credentials in the table - see a count of credentials (e.g., “1 credential(s)”)

Click Next when you have added the credentials you need.

Map Credentials to Servers

The credential mapping screen displays all SQL Server instances you selected for assessment. Each instance must have a credential assigned before you can proceed.

Understanding the Screen:

• The status indicator at the top shows how many servers have been mapped (e.g., “0/2 mapped”) and updates automatically as you assign credentials
• Each row in the table shows a checkmark (✓) when a credential is assigned, or a warning (⚠) when no credential is assigned
• Dropdown menus list all credentials you’ve added in the previous step, showing the credential name and authentication type for easy selection

DMC provides two methods for mapping credentials. Select one of the tabs below to see instructions for your chosen method:

Search and Filter

• Use the search box to filter servers by hostname, instance name, or edition
• Use pagination controls if you have many servers

Click Next when all SQL Server instances have credentials assigned.

Review Assessment Summary

Before starting the scan, review the summary information displayed on the scan screen:

SQL Server Instances in Scope Card:

• A prominent card displays the total number of SQL Server instances ready for assessment
• This shows how many instances will be assessed in this scan session
• Example: “2” indicates two SQL Server instances are in scope

Console Output Section:

• The console output area displays real-time progress and status messages
• Initially empty before the scan starts
• Shows detailed information as the assessment progresses
• Includes an Export Log button to download the console output

Start Assessment

1. Review the SQL Server Instances in Scope count to confirm the number of instances to be assessed
2. Verify the output directory is correct (you can change it using the folder icon if needed)
3. Click Run Scan button at the bottom right to begin the assessment

Monitor Progress

During the assessment, you can monitor progress in real time:

• Console Output - Displays detailed progress for each SQL Server instance as it’s assessed
• Status Updates - Shows which instance is currently being assessed and completion status
• Export Log - Click the Export Log button to download the console output at any time

What Happens During Assessment:

For each SQL Server instance:

1. DMC connects to the SQL Server using the assigned credentials
2. Gathers metadata about databases, objects, and configurations
3. Assesses migration readiness for Azure SQL targets
4. Identifies potential issues and blockers
5. Generates a detailed assessment report

Assessment Duration:

• Small instances (< 10 databases): 1-2 minutes per instance
• Medium instances (10-50 databases): 2-5 minutes per instance
• Large instances (> 50 databases): 5-15 minutes per instance

Console Output

The console output provides detailed information about the assessment:

• Success messages - Show when an instance is assessed successfully
• Error messages - Display connection or assessment failures
• Progress updates - Show which instance is being processed
• Summary information - Total instances, successful assessments, and failures

You can export the console log by clicking Export Log.