MS SQL Requirements

DMC supports assessment of SQL Server instances for Azure migration readiness with the following requirements.

Supported SQL Server Versions

Supported SQL Server Platforms

DMC supports assessment of SQL Server instances running on both Windows and Linux operating systems.

DMC System Requirements

.NET Runtime 8.0 or Above (Required)

Database Assessment requires .NET Runtime 8.0 or higher to be installed on the machine running DMC. The Database Assessment feature requires .NET Runtime version 8.0 or higher to function properly.

Installation:

1. Download .NET Runtime 8.0 or later from the official .NET download page
2. Run the installer and follow the installation wizard
3. Restart DMC (or restart your computer if prompted) to ensure detection
4. Verify installation - DMC will automatically detect .NET Runtime when Database Assessment is selected

Network Requirements

DMC requires network access to target SQL Server instances within the scope of assessment.

SQL Server Prerequisites

Remote Connections must be enabled on SQL Server instances to allow DMC to connect and assess databases.

Enable Remote Connections

EXEC sp_configure 'remote access', 1;
RECONFIGURE;

Enable TCP/IP Protocol

TCP/IP must be enabled on SQL Server instances to allow remote connections.

Get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib\Tcp" -Name Enabled

SQL Server Browser Service

The SQL Server Browser service must be running for named instance discovery and connection.

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

Get-Service -Name SQLBrowser

Configure Firewall Rules

Firewall rules must be configured to allow SQL Server connections from the DMC jump box. Configuration steps differ based on the operating system hosting SQL Server.

New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

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

sudo firewall-cmd --permanent --add-port=1433/tcp
sudo firewall-cmd --permanent --add-port=1434/udp
sudo firewall-cmd --reload

sudo ufw allow 1433/tcp
sudo ufw allow 1434/udp
sudo ufw reload

sudo iptables -A INPUT -p tcp --dport 1433 -j ACCEPT
sudo iptables -A INPUT -p udp --dport 1434 -j ACCEPT

Credential Requirements

DMC supports two authentication methods for SQL Server assessment:

• Windows Integrated Authentication: Uses Windows domain or local accounts with impersonation
• SQL Server Authentication: Uses SQL Server logins with credentials provided directly to SQL Server

The following sections outline the permissions and requirements for each method.

DMC Application Requirements

DMC must run as Administrator for all SQL Server assessment operations.

Standard Credential Requirements

For comprehensive SQL Server assessment, accounts with the sysadmin server role are recommended. This provides complete access to assess all databases, server configurations, and migration readiness factors.

Windows Integrated Authentication

Username Format:

• Domain account: DOMAIN\username (e.g., CONTOSO\sqladmin)
• Local account: .\username (e.g., .\Administrator)

SQL Server Authentication

SQL Server Authentication is supported and uses SQL Server logins (not Windows accounts). Credentials are provided directly to SQL Server, and DMC does not impersonate a Windows user.

Required SQL Server Permissions:

• Standard (Recommended): sysadmin server role
• Least Privilege: VIEW SERVER STATE, VIEW ANY DATABASE, CONNECT SQL (see Least Privilege Account Setup section below)

Username Format:

• SQL Server login name only (no backslash, no domain prefix)
• Examples: sa, sqluser, dmauser, migration_assessor

Prerequisites:

• SQL Server must be configured for Mixed Mode Authentication (SQL Server and Windows Authentication mode)
• A SQL Server login must exist with the required permissions
• DMC must run as Administrator (required for assessment operations)

Grant sysadmin Role:

-- Grant sysadmin role to a SQL Server login
ALTER SERVER ROLE sysadmin ADD MEMBER [username];

-- Grant sysadmin role to a Windows account
ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\username];

Least Privilege Account Setup

If you prefer to use least-privilege accounts instead of sysadmin accounts, you can set up dedicated user accounts with specific permissions. This approach provides a more secure configuration while still allowing SQL Server assessment.

Minimum Required Permissions

For least-privilege SQL Server assessment, the following permissions are the minimum required:

Windows Integrated Authentication (Least Privilege)

For Windows accounts using least-privilege permissions:

Prerequisites:

• The Windows account must exist on the machine running DMC (required for Windows impersonation)
• SQL Server must allow Windows authentication
• DMC must run as Administrator (required for Windows impersonation)

Required SQL Server Permissions:

-- Grant minimum permissions to a Windows domain account
USE master;
GRANT VIEW SERVER STATE TO [DOMAIN\username];
GRANT VIEW ANY DATABASE TO [DOMAIN\username];
GRANT CONNECT SQL TO [DOMAIN\username];

-- Grant minimum permissions to a Windows local account
USE master;
GRANT VIEW SERVER STATE TO [.\username];
GRANT VIEW ANY DATABASE TO [.\username];
GRANT CONNECT SQL TO [.\username];

Username Format:

• Domain account: DOMAIN\username (e.g., CONTOSO\dmauser)
• Local account: .\username (e.g., .\dmauser)

SQL Server Authentication (Least Privilege)

For SQL Server logins using least-privilege permissions:

Prerequisites:

• SQL Server must be configured for Mixed Mode Authentication
• A SQL Server login must be created with the minimum permissions listed below
• DMC must run as Administrator (required for assessment operations)

Required SQL Server Permissions:

The following permissions are the minimum required for SQL Server Authentication with least-privilege access:

Grant Minimum Permissions:

-- Create SQL Server login (if it doesn't exist)
CREATE LOGIN [dmauser] WITH PASSWORD = 'StrongPassword123!';

-- Grant minimum permissions to SQL Server login
USE master;
GRANT VIEW SERVER STATE TO [dmauser];
GRANT VIEW ANY DATABASE TO [dmauser];
GRANT CONNECT SQL TO [dmauser];

Username Format:

• SQL Server login name only (no backslash, no domain prefix)
• Examples: dmauser, sql_assessor, migration_user

Assessment Completeness Comparison

SQL Server Configuration Requirements

Authentication Mode Configuration

For SQL Server Authentication:

SQL Server must be configured to allow SQL Server authentication (Mixed Mode).

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'LoginMode', REG_DWORD, 2;

Connection Testing

Before running Database Assessment, verify connectivity to SQL Server instances.

Test Network Connectivity

Test Port Connectivity:

Test-NetConnection -ComputerName SERVERNAME -Port 1433

Test SQL Server Browser:

Test-NetConnection -ComputerName SERVERNAME -Port 1434 -Udp

Test SQL Server Connection

Windows Integrated Authentication:

sqlcmd -S SERVERNAME\INSTANCENAME -E -Q "SELECT @@VERSION"

SQL Server Authentication:

sqlcmd -S SERVERNAME\INSTANCENAME -U username -P password -Q "SELECT @@VERSION"

Troubleshooting Common Issues

Issue: “Remote connections not allowed”

Solution:

1. Enable remote connections (see Enable Remote Connections above)
2. Verify TCP/IP is enabled
3. Check firewall rules allow port 1433 (TCP) and 1434 (UDP)
4. Ensure SQL Server Browser service is running (for named instances)

Issue: “SQL Server Browser service is not running”

Solution:

1. Start SQL Server Browser service (see SQL Server Browser Service above)
2. Set startup type to Automatic
3. Verify firewall allows UDP port 1434

Issue: “Login failed” or “Authentication failed”

For Windows Authentication:

• Verify the Windows account exists on the machine running DMC
• Ensure account has SQL Server permissions (sysadmin recommended)
• Verify DMC is running as Administrator

For SQL Server Authentication:

• Verify SQL Server is configured for Mixed Mode authentication
• Test credentials using sqlcmd command
• Ensure the SQL login has appropriate permissions

Issue: “Permission denied” or “Insufficient permissions”

Solution:

1. Verify account has sysadmin role (recommended) or minimum permissions:
   • VIEW SERVER STATE
   • VIEW ANY DATABASE
   • CONNECT SQL
2. Grant additional permissions if needed:

   ALTER SERVER ROLE sysadmin ADD MEMBER [username];