47

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?

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Tyler
  • 17,669
  • 10
  • 51
  • 89
  • 1
    This is an "answer my own question" question, so please forgive the broad looking description of the question, I am just trying to catch as many Google searches as possible. – Tyler Jun 06 '16 at 13:34
  • 2
    For the record, 3 people voted to migrate this post to Super User. However the system requires 4 people to vote to migrate for the migration to take place. – gunr2171 Jun 06 '16 at 18:15

1 Answers1

144
  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.
MetaGuru
  • 42,847
  • 67
  • 188
  • 294
Tyler
  • 17,669
  • 10
  • 51
  • 89
  • 2
    Works perfectly with windows host -> windows server guest ! – Amr Ellafy Oct 02 '17 at 15:30
  • Excellent tutorial!! helped me a lot!! on Step 3: SQL Server Browser Service must be started =) – Facundo Colombier Nov 03 '17 at 21:05
  • 2
    Thank you for your excellent tutorial. For sake of completeness here's how to set the Firewall to allow TCP on port 1433: https://blogs.technet.microsoft.com/danstolts/2011/06/how-to-open-firewall-port-1433-for-sql-server-database-engine-for-use-with-scom-or-anything-else/ – lwohlhart Dec 19 '17 at 13:34
  • I turned off the Firewall for _private_ networks but not _public_ and this caused me an issue when my host machine was at the library, etc. Rather than turning off the Firewall, I added an exception for SQL Server but again make sure that both public and private boxes are ticked! – Neil C. Obremski Nov 23 '19 at 19:01
  • It looks like the link @lwohlhart provided to a guide from MS about configuring the Windows firewall to allow traffic on port 1433 is dead. [Here is an updated link.](https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-windows-firewall-for-database-engine-access?view=sql-server-ver15#to-open-a-port-in-the-windows-firewall-for-tcp-access) – Pat Putnam Jan 22 '20 at 15:50
  • Should Guest IP follow a special rule? – Ericyu67 May 19 '20 at 14:16
  • I used an Ubuntu 20.04 VM, set up with Microsoft's quickstart guide ( https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver16 ), then the final step in this guide. Success! – dangowans Jun 20 '23 at 18:53