How to connect to SQL Server in Win7 virtual machi

问题:

I restored a MSSQL backup to SQL Server Express in a Windows 7 virtual machine under Virtualbox. I am attempting to connect to this SQL Server instance from the host (Mac OS) using a user inside the imported database that has credentials to connect. The virtual machine is configured to use a Host-Only adapter. SQL Server express is running inside the VM at WIN7VM/SQLEXPRESS, port 1433.

I am able to connect to Apache in the VM on port 8080 from the Mac host without a problem. I can also telnet into the SQL server at port 1433 from the Mac host, using the host-only IP address (192.168.56.101).

What I am unable to do is connect to SQL server from Navicat in MacOS, or remotely using PHP. Here is what I have tried:

• Disabled Windows Firewall
• Configured SQL server port setting to use 1433
• Enabled SQL server authentication
• Enabled TCP/IP in SQL Server
• Enabled remote connections to SQL server

I am trying to connect with this info:

• Host/IP address: 192.168.56.101 OR 192.168.56.101\SQLEXPRESS (have tried both)
• Port: 1433
• Database: MyDatabase
• Authentication type: Basic
• Username: username
• Password: password

What am I missing here to be able to remotely connect without any issues? Thanks in advance!

回答1:

I believe you need to use a Bridged Adapter - rather than a Host-Only adapter. A Host-Only adapter does not use the physical NIC - so that is probably the issue here. I have encountered the same issue before. Using a Bridged Network connection on both the Host and the Guest - will mean that both are on the same network. Assuming the firewall are not blocking port 1433, you should be able to connect without issue.

回答2:

It turned out to not be a Virtualbox issue, but a SQL Server authentication issue.

The user I was trying to connect with had access to a database, but did not have a corresponding SQL Server Login.

MSDN - Managing Logins, Users, and Schemas How-to Topics