Troubleshooting SQL Server Assessment

Overview

When running DMC Database Assessment to scan SQL Server instances, you may encounter access issues, authentication failures, or configuration problems that prevent successful assessment.
This guide provides secure, streamlined steps to troubleshoot and resolve connectivity, credential, and configuration problems specific to SQL Server assessment.

Common Access Issues

SQL Server assessment typically fails due to one of these categories:

• Network connectivity problems – Ports blocked, firewalls, SQL Server Browser service not running
• Authentication failures – Invalid credentials, incorrect authentication mode, insufficient permissions
• SQL Server configuration issues – Remote connections disabled, TCP/IP not enabled, Mixed Mode not configured
• DMC configuration issues – .NET Runtime not installed, DMC not running as Administrator, credential format errors
• System state problems – SQL Server service not running, SQL Server Browser service stopped

Troubleshooting Navigation

Use the cards below to navigate to specific troubleshooting areas:

Network Connectivity Issues

Verify Network Access

Test Basic Connectivity

ping <server-ip-or-hostname>

If ping fails, check:

• Jumpbox network configuration
• Target SQL Server NIC/IP settings
• Firewall rules blocking ICMP

Test Required Ports

SQL Server Default Instance (Port 1433):

Test-NetConnection -ComputerName <server-ip> -Port 1433

SQL Server Browser Service (Port 1434 UDP):

Test-NetConnection -ComputerName <server-ip> -Port 1434 -Udp

For Named Instances: Named instances use dynamic ports. The SQL Server Browser service (port 1434 UDP) is required to discover the correct port.

# Test SQL Server Browser
Test-NetConnection -ComputerName <server-ip> -Port 1434 -Udp

# Test connection to named instance (browser will resolve port)
sqlcmd -S SERVERNAME\INSTANCENAME -E -Q "SELECT @@VERSION"

Check Firewall Rules

Windows Firewall (for SQL Server on Windows):

• Port 1433 (TCP) – SQL Server default port
• Port 1434 (UDP) – SQL Server Browser service (required for named instances)

Linux Firewall (for SQL Server on Linux):

• Port 1433 (TCP) – SQL Server default port
• Port 1434 (UDP) – SQL Server Browser service

Configure Windows Firewall:

# For Default Instance (Port 1433)
New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow

# For SQL Server Browser (Port 1434)
New-NetFirewallRule -DisplayName "SQL Server Browser" -Direction Inbound -Protocol UDP -LocalPort 1434 -Action Allow

Configure Linux Firewall:

For firewalld (RHEL/CentOS/Fedora):

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

For ufw (Ubuntu/Debian):

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

SQL Server Configuration

Enable Remote Connections

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

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

SQL Server 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;

Authentication Issues

Windows Integrated Authentication

DMC supports Windows Integrated Authentication using domain or local Windows accounts.

Issue: “Access is denied” when using Windows credentials

Cause: DMC not running as Administrator or Windows impersonation failed

Solution:

1. Verify DMC is running as Administrator (check window title or Task Manager)
2. If not running as admin:
   • Close DMC
   • Right-click DMC icon → Run as administrator
   • Re-enter license key if prompted
   • Retry the assessment

Verify Administrator Privileges:

# Check if running as admin
([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)

Issue: “Login failed” with Windows Authentication

Possible Causes:

• Windows account doesn’t exist on the machine running DMC
• Account doesn’t have SQL Server permissions
• Incorrect username format

Solution:

1. Verify Username Format:

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

2. Verify Account Exists:

   # For domain accounts
   Get-ADUser -Identity sqladmin -Server CONTOSO
   
   # For local accounts
   Get-LocalUser -Name Administrator

3. Verify SQL Server Permissions:

   -- Check if account has sysadmin role
   SELECT name, type_desc, is_disabled
   FROM sys.server_principals
   WHERE name = 'DOMAIN\username' OR name = '.\username';

4. Grant Permissions if Needed:

   -- Grant sysadmin role (recommended)
   ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\username];
   
   -- Or grant minimum permissions
   GRANT VIEW SERVER STATE TO [DOMAIN\username];
   GRANT VIEW ANY DATABASE TO [DOMAIN\username];
   GRANT CONNECT SQL TO [DOMAIN\username];

Issue: “The directory name is invalid” with Windows credentials

Cause: Impersonated user doesn’t have access to DMC’s temp directories

Solution:

• This should be automatically handled by DMC
• If persists, try SQL Server Authentication instead
• Or ensure the Windows account has broad filesystem permissions on the DMC machine

SQL Server Authentication

DMC supports SQL Server Authentication using SQL Server logins (username and password).

Issue: “Login failed” or “Authentication failed” with SQL Server Authentication

Possible Causes:

• SQL Server not configured for Mixed Mode authentication
• SQL Server login account doesn’t exist on the jump box as a local Windows user with the same password
• Invalid credentials
• SQL login doesn’t exist or is disabled
• Incorrect username format (should not include backslash)

Solution:

1. Verify SQL Server is in Mixed Mode:

   -- Check authentication mode
   EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', 
       N'Software\Microsoft\MSSQLServer\MSSQLServer', 
       N'LoginMode';

   Value 2 = Mixed Mode, Value 1 = Windows Only

2. Test Credentials Manually:

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

3. Verify SQL Login Exists:

   SELECT name, type_desc, is_disabled, create_date
   FROM sys.server_principals
   WHERE type = 'S' AND name = 'username';

4. Enable SQL Login if Disabled:

   ALTER LOGIN [username] ENABLE;

5. Reset Password if Needed:

   ALTER LOGIN [username] WITH PASSWORD = 'NewPassword123!';

6. Verify Local Account Exists on Jump Box:

   • The SQL Server login account must exist on the jump box as a local Windows user
   • Username must match exactly (case-sensitive)
   • Password must match exactly
   • Create the local account if it doesn’t exist:

     # Create local user account on jump box
     New-LocalUser -Name "sqluser" -Password (ConvertTo-SecureString "Password123" -AsPlainText -Force) -Description "SQL Server Authentication Account"

7. Verify Username Format:

   • Correct: sa, sqluser, dmauser (no backslash)
   • Incorrect: DOMAIN\username, .\username (these indicate Windows Auth)

Issue: “SQL Server Authentication requires Mixed Mode”

Cause: SQL Server is configured for Windows Authentication only

Solution:

1. Enable Mixed Mode authentication (see SQL Server Authentication Mode Configuration above)
2. Restart SQL Server service
3. Retry the assessment

Credential Issues

Credential Format Errors

Issue: Incorrect username format in credentials

Windows Authentication Format:

• Domain account: DOMAIN\username (e.g., CONTOSO\sqladmin, MYDOMAIN\dbuser)
• Local account: .\username (e.g., .\Administrator, .\SQLAdmin) or SERVERNAME\username

SQL Server Authentication Format:

• Username only: sa, sqluser, dmauser (no backslash, no domain prefix)

Common Format Errors:

• Using DOMAIN\username format for SQL Server Authentication (should be username only)
• Using username format for Windows Authentication (should include DOMAIN\ or .\)
• Including spaces or special characters incorrectly

Solution:

1. Review the credential format in the Database Credentials screen
2. Verify the authentication type tag matches your intended authentication method
3. Edit the credential if the format is incorrect
4. Re-map credentials to SQL Server instances if needed

Permission Issues

Issue: “Permission denied” or “Insufficient permissions”

Cause: Account doesn’t have sufficient SQL Server permissions

Solution:

1. Verify Current Permissions:

   -- Check server role membership
   SELECT r.name AS RoleName, m.name AS MemberName
   FROM sys.server_role_members rm
   JOIN sys.server_principals r ON rm.role_principal_id = r.principal_id
   JOIN sys.server_principals m ON rm.member_principal_id = m.principal_id
   WHERE m.name = 'username' OR m.name = 'DOMAIN\username';
   
   -- Check explicit permissions
   SELECT permission_name, state_desc
   FROM sys.server_permissions
   WHERE grantee_principal_id = USER_ID('username');

2. Grant sysadmin Role (Recommended):

   -- For Windows account
   ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\username];
   
   -- For SQL Server login
   ALTER SERVER ROLE sysadmin ADD MEMBER [username];

3. Grant Minimum Permissions (Least Privilege):

   -- For Windows account
   GRANT VIEW SERVER STATE TO [DOMAIN\username];
   GRANT VIEW ANY DATABASE TO [DOMAIN\username];
   GRANT CONNECT SQL TO [DOMAIN\username];
   
   -- For SQL Server login
   GRANT VIEW SERVER STATE TO [username];
   GRANT VIEW ANY DATABASE TO [username];
   GRANT CONNECT SQL TO [username];

Credential Mapping Issues

Issue: “No credential assigned” or credential mapping errors

Cause: SQL Server instance doesn’t have a credential mapped in the Credential Mapping screen

Solution:

1. Navigate to the Credential Mapping screen in DMC
2. Review the status indicator (e.g., “0/2 mapped”)
3. Use “Apply to all servers” dropdown to assign a credential to all instances
4. Or assign credentials individually using the dropdown in each server row
5. Verify all instances show a checkmark (✓) indicating credential assignment
6. Click Next only when all instances have credentials assigned

DMC Configuration Issues

.NET Runtime Issues

Issue: “.NET Runtime Required” modal appears

Cause: .NET Runtime 8.0 or above is not installed on the DMC machine

Solution:

1. Option 1: Install via DMC UI

   • Click “Download .NET Runtime” button in the modal
   • Run the downloaded installer
   • Click “Retry” in the modal
   • Restart DMC if still not detected

2. Option 2: Install Manually

   • Download .NET Runtime 8.0 or later from the official .NET download page
   • Run the installer
   • Restart DMC
   • The modal will not appear once .NET Runtime is installed

Verify .NET Runtime Installation:

# Check installed .NET Runtime versions
dotnet --list-runtimes

# Should show .NET Runtime 8.0 or higher

Administrator Privileges

Issue: Assessment fails with “Access is denied” or impersonation errors

Cause: DMC is not running as Administrator

Solution:

1. Close DMC completely
2. Right-click DMC.exe → Run as administrator
3. Re-enter license key if prompted
4. Retry the assessment

Why Administrator is Required:

• Windows Integrated Authentication: DMC must impersonate the specified Windows user, which requires Administrator privileges
• SQL Server Authentication: Assessment operations require elevated privileges even though no impersonation occurs

Credential Format in DMC

Issue: Console output shows “Failed to parse connection string”

Cause: Special characters in password not handled correctly (rare, usually fixed in latest versions)

Solution:

1. Verify you’re using the latest version of DMC
2. Try re-entering the password in the credential
3. If persists, try using a password without special characters temporarily to test
4. Contact support if issue persists

Common Error Messages and Solutions

Based on the code flow and assessment reports, here are common error messages you may encounter:

Understanding Assessment Report Errors

When an assessment completes with errors, the assessment report JSON file contains detailed error information:

Location: Assessment reports are saved in the output directory under each server’s folder:

C:\temp\scans\databaseassessments\<run-id>\<server-name>\SqlAssessmentReport-*.json

Error Structure:

{
  "Errors": [
    {
      "ErrorID": "LoginFailure_RemoteConnectionNotAllowed",
      "ErrorSummary": "SQL connection failed to server instance...",
      "Message": "Detailed error message...",
      "Guidance": "To resolve this error, try one of the following..."
    }
  ]
}

How to Review Errors:

1. Navigate to the assessment output directory
2. Open the SqlAssessmentReport-*.json file for the failed server
3. Look for the Errors[] array
4. Review ErrorID, ErrorSummary, and Guidance fields
5. Follow the guidance provided for each error

Connection Testing

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

Test Network Connectivity

Test Port Connectivity:

# Test SQL Server port (1433)
Test-NetConnection -ComputerName SERVERNAME -Port 1433

# Test SQL Server Browser (1434 UDP)
Test-NetConnection -ComputerName SERVERNAME -Port 1434 -Udp

Test SQL Server Connection

Windows Integrated Authentication:

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

# Test with specific Windows account (if available)
runas /user:DOMAIN\username "sqlcmd -S SERVERNAME\INSTANCENAME -E -Q \"SELECT @@VERSION\""

SQL Server Authentication:

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

Verify SQL Server Service Status

Check SQL Server Service:

# Check SQL Server service status
Get-Service -Name MSSQLSERVER
Get-Service -Name MSSQL$INSTANCENAME

# Check SQL Server Browser service
Get-Service -Name SQLBrowser

Start Services if Needed:

# Start SQL Server service
Start-Service -Name MSSQLSERVER

# Start SQL Server Browser service
Start-Service -Name SQLBrowser

Single Instance Assessment for Troubleshooting

When a SQL Server instance shows issues during batch assessment, it’s recommended to assess that specific instance individually to isolate the problem.

When to Use Single Instance Assessment

Use this approach when:

• An instance shows as “Failed” in batch assessments
• An instance appears in console output with specific errors
• You want to test configuration changes on a specific instance
• You want to verify credentials work for a single instance

Steps for Single Instance Assessment

Reviewing Assessment Console Output

The DMC console output provides detailed information about each assessment attempt. Understanding the output helps identify issues quickly.

Successful Assessment Output

[1/3] WIN2019SRV3\SQLEXPRESS
Starting SQL assessment using Windows Integrated Authentication
Assessment completed successfully
Success: Issues: 4, Objects: 690/690, Time: 00:02:00, Status: Completed
  Databases assessed: 2

What This Shows:

• Instance name and number in sequence
• Authentication method used
• Success status
• Number of issues found
• Objects assessed
• Time taken
• Number of databases assessed

Failed Assessment Output

[2/3] WIN2019SRV2\SQLEXPRESS
Starting SQL assessment using Windows Integrated Authentication
Failed: Assessment returned error status
  Error: LoginFailure_RemoteConnectionNotAllowed
  Error -1 - SQL connection failed...

What This Shows:

• Instance name and number in sequence
• Authentication method used
• Failure status
• Error ID (e.g., LoginFailure_RemoteConnectionNotAllowed)
• Error message details

Exporting Console Log

You can export the console output for detailed analysis:

1. During or after assessment, click Export Log button
2. Save the log file
3. Review the log file for detailed error messages and timestamps
4. Use the log file to identify patterns or recurring issues

Next Steps

After troubleshooting and resolving issues:

1. Re-test Connectivity – Verify ports, firewall, and SQL Server services
2. Re-validate Credentials – Test credentials manually with sqlcmd
3. Re-run Assessment – Start with a single instance to verify fixes
4. Monitor Console Output – Watch for any new error messages
5. Review Assessment Reports – Check JSON reports for detailed error information
6. Run Full Assessment – Once single instance succeeds, run full batch assessment

Related Documentation

• Scan MS SQL – Step-by-step guide to running Database Assessment
• MS SQL Requirements – Detailed SQL Server requirements and configuration
• Remediating Access Issues – General troubleshooting guide (includes SQL Server section)