Unlocking Cross-Platform Connectivity: Accessing SQL Server on Your Mac with Azure Data Studio

As the demand for remote database management grows, the ability to connect to a SQL Server instance on a local machine from a Mac becomes increasingly crucial.

Azure Data Studio, Microsoft's cross-platform database tool, offers a convenient solution for Mac users. In this guide, we'll explore how to set up Azure Data Studio to connect to SQL Server on a local machine, enabling seamless database management and development from your Mac.

Despite the differing operating systems, it's entirely feasible with the right tools and configurations. This guide walks you through the process step-by-step, ensuring a seamless connection to SQL Server from your Mac.

Prerequisites:

1. A Mac computer with network access to the machine hosting SQL Server.
2. A SQL Server instance installed and running on a local machine.
3. Knowledge of the SQL Server instance name or IP address.
4. SQL Server authentication credentials (username & password).

Verify SQL Server Configuration

Ensure that SQL Server is configured to allow remote connections. Open SQL Server Management Studio (SSMS) on your local machine, connect to the SQL Server instance, and navigate to "SQL Server Configuration Manager."

Under "SQL Server Network Configuration," verify that TCP/IP is enabled and that SQL Server Browser service is running.

Create SQL Server Login

At the time of this writing I'm using SQL Server 2019. For this example I created a SQL Server login, conveniently named MacOS, from the Microsoft SQL Server Management Studio installation on my local Windows 10 Pro workstation where SQL Server resides.

USE [master]
GO

CREATE LOGIN [MacOS] WITH PASSWORD=N'',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [MacOS]
GO        

Enable TCP/IP Protocol

If TCP/IP protocol is not enabled, follow these steps:

1. Open SQL Server Configuration Manager.

The SQL Server 2019 (15.x) SQLServerManager15.msc file is located in the SysWOW64 directory "C:\Windows\SysWOW64\". You can also access in your Windows Computer Management desktop application under "Services and Applications".

If you receive an error message when you open SQL Server Configuration Manager in SQL Server: "Cannot connect to WMI provider..."

Open an elevated command prompt, type the following command, and press Enter:

C:\Program Files (x86)\Microsoft SQL Server\150\Shared>mofcomp "sqlmgmproviderxpssp2up.mof"        

After you run the mofcomp tool, restart the WMI service for the changes to take effect. Restart the WMI service by running the following PowerShell command as administrator:

PS C:\WINDOWS\SysWOW64> Get-Service winmgmt | Restart-Service -Force        

2. Expand "SQL Server Network Configuration" and select "Protocols for [your SQL Server instance]".

3. Right-click on TCP/IP and select "Enable".

Configure SQL Server Firewall

If the firewall is enabled on the local machine hosting SQL Server, you need to allow inbound connections on the SQL Server port (default is 1433). Follow these steps:

1. Open Windows Defender Firewall with Advanced Security.
2. Click on "Inbound Rules" and then "New Rule...".
3. Select "Port" and click "Next >".
4. Choose "TCP" and select radio button next to "Specific local ports:". Type 1433 in the associated text box and click "Next >".
5. Select "Allow the connection" and click "Next >".
6. Choose when the rule applies (Domain, Private, Public) and click "Next".
7. Give your rule a name and click "Finish".

Launch Azure Data Studio

Open Azure Data Studio on your Mac. If you haven't installed it yet, you can download it from the official Microsoft website here.

Connect to SQL Server In Azure Data Studio

Click on the "Connections" tab in the sidebar. Then, click on the "New Connection" button. You will be prompted to enter connection details.

Enter Connection Details

Fill in the following information:

• Server Name: Enter the name or IP address of your SQL Server instance.
• Authentication Type: Choose either "SQL Login".
• Username and Password: Provide your SQL Server authentication credentials.

Test the Connection

After entering the connection details, click on the "Connect" button. Azure Data Studio will attempt to connect to the SQL Server instance using the provided information. If the connection is successful, you will see a confirmation message.

If you're still unable to connect to the SQL Server instance: there may be a conflict with a TCP endpoint.

The error occurs when you configure a TCP endpoint for Service Broker using the same port that the SQL Server instance is configured to use. Execute the following commands in your SQL Server Management Studio:

-- List endpoints.
SELECT * FROM sys.tcp_endpoints

-- Drop the endpoint that's causing the problem.
DROP ENDPOINT ConflictingEP

-- Alter the endpoint to use a different port.
ALTER ENDPOINT ConflictingEP as tcp (listener_port=1980)        

Explore Database(s)

Once connected, you can explore databases, execute queries, manage schemas, and perform other database-related tasks directly from Azure Data Studio on your Mac!

Connect to a local instance of Microsoft SQL Server from the MacOS Terminal CLI

Install FreeTDS on Mac

FreeTDS is a set of libraries for Unix and Linux that allows your Mac to communicate with SQL Server. Install it using Homebrew by running the following command in your terminal:

brew install freetds        

Configure FreeTDS

Once FreeTDS is installed, configure it to connect to your SQL Server instance. Edit the FreeTDS configuration file by running:

sudo nano /usr/local/etc/freetds.conf        

Add the following configuration at the end of the file:

[YourServerName]
    host = your_server_ip_or_hostname
    port = 1433
    tds version = 7.4        

Replace "YourServerName" with a name for your server, and "your_server_ip_or_hostname" with the IP address or hostname of your SQL Server instance.

Test the Connection

Verify that you can connect to SQL Server from your Mac using the configured FreeTDS. Open Terminal and run the following command:

tsql -S YourServerName -U YourUsername -P YourPassword        

Replace "YourServerName" with the name you specified in the FreeTDS configuration file, and "YourUsername" and "YourPassword" with your SQL Server authentication credentials.

If the connection is successful, you will see a prompt indicating that you are connected to the SQL Server instance.

Conclusion

Connecting to a SQL Server instance on a local machine from a Mac involves configuring both the SQL Server instance and the Mac itself. With Azure Data Studio, connecting to a SQL Server instance on a local machine from a Mac has never been easier.

By following the steps outlined in this guide, you can establish a seamless connection, empowering you to manage databases, develop applications with efficiency and convenience, and perform other tasks remotely from your Mac with ease, regardless of your operating system.