How can I connect to SQLServer running on VirtualBox from my host Macbook

Question

I want to run SQLServer on my Mac but I can't do it natively. How can I host a SQLServer with VirtualBox and connect to it from my MacBook for local development?

Answer 1

1. Download VirtualBox
2. Download a Windows 10 ISO from here
3. Create a new Windows 10 VM with VirtualBox. When it asks for the "Virtual Optical Disk File", point it to the ISO download from Step 2:

4. Continue with the Windows 10 setup
5. In the Windows VM, download and install SQL Server (I used Express)
6. When the SQL Server installation finishes, install the tool (SSMS) which we will need later

7. With SSMS, create a new database (I called mine testdatabase)

8.1. Create a new Login: right click on Security > New > Login... Be sure to select the SQL Server authentication option.

8.2. In the Server Roles tab, select the sysadmin option:

8.3. In the User Mapping tab, map the login to the database, and check all assign the role memberships:

9. Open the server properties (right click the root level object). Go to the Security tab, and switch the Server Authentication mode to SQL Server and Windows Authentication mode:

10. Open the Windows Services program, and find the SQL Server Browser. Open its properties and change the Startup type to automatic:

11.1. Open the Sql Server Configuration Manager program. Navigate to the Protocols under the SQL Server Network Configuration and Enable the TCP/IP option:

11.2. Open the TCP/IP properties switch to the IP Addresses tab. Make a note of the IP Address field under IP2 (you will need this later):

11.3. Set the TCP Port under IPALL to 1433:

12. Configure the Firewall on the Windows VirtualBox to allow 1433 to be unblocked (I just disabled the whole firewall, probably not the best option.) edit: another user has kindly added the steps for adding a firewall rule to the end of this post.

13. In your Macbook's VirtualBox app, open the settings for the Windows VM and go to the Network tab. Set the Attached to dropdown to NAT, then click Port Forwarding. Add a rule to forward the VM's 1433 port to your localhost's 1433 port. The Guest IP will be the IP from Step 11.2:

---

You should now be able to connect to your SQLServer from your macbook with a connection string something like this:

jdbc:sqlserver://127.0.0.1;databaseName=testdatabase

---

Steps to open a port in the Windows firewall for TCP access

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane (upper right corner).
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, In my case we are using the default which is 1433. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, I am going to Leave Domain turned on and turn private and public off. Then click Next.
7. In the Name dialog box, type "Allow SQL 1433 Inbound" and for a description I am putting in the same. Then click Finish.