如何通过SSMS及.NET代码从主机连接VirtualBox虚拟机中的SQL Server

Hey Dheeraj, let’s walk through fixing this connection issue step by step—I’ve tackled similar VirtualBox NAT + SQL Server problems before, so let’s start with the most common culprits.

NAT mode hides your VM from the host’s network by default, so you need to set up port forwarding to let the host reach SQL Server on the VM. Here’s how:

• Open VirtualBox, select your Windows 7 VM, go to Settings > Network
• Confirm "Enable Network Adapter" is checked, and network mode is set to "NAT"
• Click the small Port Forwarding icon (right side of the network settings)
• Add a new rule with these details:
  • Name: SQLServer (or any name you like)
  • Protocol: TCP
  • Host Port: 1433 (or use 14330 if 1433 is already in use on your host)
  • Guest Port: 1433 (default SQL Server port—match this to what you set in SQL config later)
  • Host IP: Leave blank (or enter 127.0.0.1 if you only want local host access)
  • Guest IP: Enter your VM’s static IPv4 address (get this by running ipconfig in the VM’s command prompt)

Even with port forwarding, SQL Server might not be set up to accept remote connections. Let’s check these settings:

• Ensure SQL Server Service is Running
  • On the VM, press Win + R, type services.msc and hit Enter
  • Find "SQL Server (MSSQLSERVER)" (or your named instance name) and confirm it’s set to Automatic and is running
• Enable TCP/IP Protocol
  • Open SQL Server Configuration Manager on the VM
  • Navigate to SQL Server Network Configuration > Protocols for MSSQLSERVER
  • Right-click TCP/IP and select Enable
• Configure TCP/IP Port Settings
  • Right-click TCP/IP again, choose Properties, then go to the IP Addresses tab
  • Scroll down to the IPAll section:
    • Set TCP Port to 1433 (matches the guest port in your port forwarding rule)
    • Clear the TCP Dynamic Ports field (make sure it’s empty, not set to 0)
  • For each listed IP address (like IP1, IP2), set Active and Enabled to Yes (especially the one matching your VM’s IP)
  • Restart the SQL Server service to apply these changes

Now let’s test with both SSMS and .NET code:

• Testing with SSMS
  • Open SSMS on your Windows 10 host
  • In the "Connect to Server" window:
    • Server name: localhost,1433 (or localhost,your_host_port if you used a different port)
    • Authentication: Use SQL Server Authentication (ensure the sa account is enabled on the VM) or Windows Authentication (if you’ve set up cross-machine account access)
    • Enter your credentials and click Connect
• Testing with .NET Code
  • Use this simple snippet to verify connectivity:

    using System;
    using System.Data.SqlClient;
    
    class SqlConnectionTest
    {
        static void Main()
        {
            // Update these values to match your setup
            string connString = @"Server=localhost,1433;Database=YourTestDB;User Id=YourUsername;Password=YourPassword;";
            
            try
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    Console.WriteLine("Connection successful! 🎉");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Connection failed: {ex.Message}");
            }
        }
    }
    

  • Replace YourTestDB, YourUsername, and YourPassword with your actual details, and adjust the port if you used a non-default one.

If you still can’t connect:

• Allow Remote Connections in SQL Server
  • On the VM’s SSMS, right-click your server instance, go to Properties > Connections
  • Check the box labeled "Allow remote connections to this server"
• Verify Port Accessibility
  • On your host, open Command Prompt and run telnet localhost 1433 (enable Telnet first via Control Panel > Programs > Turn Windows features on or off)
  • If it connects, the port is open; if not, double-check your port forwarding rule and SQL Server’s TCP port setting
• Check for Named Instances
  • If you installed SQL Server as a named instance (not default), make sure you set a static port for it (dynamic ports cause connection issues with NAT)

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