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

Tyler picture Tyler · Jun 3, 2016 · Viewed 34.4k times · Source

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

Tyler picture Tyler · Jun 3, 2016
  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:

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

8.2. In the Server Roles tab, select the sysadmin option: enter image description here

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

enter image description here

  1. 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:

enter image description here

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

enter image description here

enter image description here

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:

enter image description here

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):

enter image description here

11.3. Set the TCP Port under IPALL to 1433:

enter image description here

  1. 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.

  2. 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:

enter image description here


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.