Connect to a SQL Server named instance (HOST\SQLEXPRESS) from your Mac

What you need

• The Windows PC’s name or IP address, and the instance name (SQLEXPRESS is the usual one for SQL Server Express).
• Administrator access to that PC, to change SQL Server’s network settings and Windows Firewall. On a work server, ask whoever runs it for the items below.
• TCP/IP turned on for the instance. New Developer and Express installations have it off; the other editions have it on.
• A way for your Mac to learn the instance’s port: the SQL Server Browser service running and UDP port 1434 open, or a fixed TCP port that you give when you connect.
• SQL Server and Windows Authentication mode (“mixed mode”) and a SQL Server login. A Mac isn’t part of the Windows domain, and Inlet signs in with a user name and password only: it doesn’t do Windows or Kerberos sign-in yet.

Find your connection details

Instance name. On the PC, open SQL Server Configuration Manager and select SQL Server Services. SQL Server (MSSQLSERVER) is a default instance, which you reach as PC-NAME; SQL Server (SQLEXPRESS) is a named instance, reached as PC-NAME\SQLEXPRESS.

Why the port matters. A default instance listens on TCP port 1433. Named instances, including every Express instance, use a dynamic port by default: SQL Server picks a free one each time it starts, so it can change after a restart. The SQL Server Browser service listens on UDP port 1434 and answers “which port is SQLEXPRESS on?”. That’s how PC-NAME\SQLEXPRESS works: the client asks Browser, then connects to the port it’s told. Setup starts Browser automatically when it installs a named instance, but it can be stopped or blocked, and Microsoft notes that routers often don’t pass UDP on.

The port it’s using now. SQL Server writes it to its error log at start, in a line like Server is listening on [ 'any' <ipv4> 1433] (search the log in SSMS for “server is listening on”). Connected over TCP, any login can also ask:

SELECT CONNECTIONPROPERTY('local_tcp_port') AS port;

Turn on TCP/IP

In SQL Server Configuration Manager, expand SQL Server Network Configuration, select Protocols for SQLEXPRESS, right-click TCP/IP and choose Enable. Then restart the instance: SQL Server Services, right-click SQL Server (SQLEXPRESS), Restart.

Connections on the PC itself use shared memory, which is on in every installation, so SSMS on the PC works even while TCP/IP is off. Don’t take that as proof the network side works.

Give the instance a fixed port (recommended)

A fixed port means a firewall rule that stays right and a connection that doesn’t depend on Browser:

1. In Protocols for SQLEXPRESS, double-click TCP/IP and open the IP Addresses tab.
2. Scroll to IPAll. If TCP Dynamic Ports says 0, delete it (leave it empty).
3. Type a port in TCP Port, for example 14330, and choose OK.
4. Restart SQL Server (SQLEXPRESS).

With Listen All set to Yes on the Protocol tab, only the IPAll values count. Pick a port no other program uses.

Open Windows Firewall

In PowerShell as administrator on the PC, allow the instance’s TCP port and, if you use Browser, UDP 1434. Limiting them to your local network is safer than allowing everyone:

New-NetFirewallRule -DisplayName "SQL Server SQLEXPRESS" -Direction Inbound -Protocol TCP -LocalPort 14330 -RemoteAddress LocalSubnet -Action Allow
New-NetFirewallRule -DisplayName "SQL Server Browser" -Direction Inbound -Protocol UDP -LocalPort 1434 -RemoteAddress LocalSubnet -Action Allow

If you keep a dynamic port, a port rule can’t follow it. Microsoft’s alternative is a program rule for sqlservr.exe (under C:\Program Files\Microsoft SQL Server\MSSQL<version>.SQLEXPRESS\MSSQL\Binn\), but an update can move that path and break the rule. A fixed port is simpler.

Turn on SQL Server authentication

In SSMS on the PC, right-click the server in Object Explorer, choose Properties, open the Security page, select SQL Server and Windows Authentication mode, and choose OK. The change takes effect only after a restart: right-click the server and choose Restart.

Then create a login for yourself rather than enabling sa, which Microsoft advises against unless you need it:

CREATE LOGIN app WITH PASSWORD = '<password>', DEFAULT_DATABASE = app;
GO
USE app;
CREATE USER app FOR LOGIN app;
ALTER ROLE db_owner ADD MEMBER app;

If the server was installed in Windows Authentication mode, sa stays disabled after you switch modes; ALTER LOGIN sa ENABLE; and a new password turn it on.

Connection string

There are three ways to name the server:

Server=tcp:PC-NAME\SQLEXPRESS;Initial Catalog=app;User ID=app;Password=<password>;Encrypt=True;TrustServerCertificate=True;
Server=tcp:PC-NAME,14330;Initial Catalog=app;User ID=app;Password=<password>;Encrypt=True;TrustServerCertificate=True;
jdbc:sqlserver://PC-NAME;instanceName=SQLEXPRESS;databaseName=app;encrypt=true;trustServerCertificate=true
sqlserver://app:<password>@PC-NAME/SQLEXPRESS?database=app

The last one is Go’s go-mssqldb, which puts the instance in the URL path. JDBC, given both a port and an instanceName, connects to the port and checks that the instance name matches it. See SQL Server connection strings for each format.

TLS

Unless someone installed a certificate for it, SQL Server encrypts with a self-signed certificate it generates at start (its error log says A self-generated certificate was successfully loaded for encryption.). That keeps passwords off the wire, but no client can verify it, so a client that checks certificates refuses to connect. On your own network, encrypt without checking (TrustServerCertificate=True).

To verify instead, install a certificate whose name matches the name you connect with, from a CA your Mac trusts, and select it in Configuration Manager (Protocols for SQLEXPRESS › Properties › Certificate). Force Encryption on the Flags tab makes the server refuse unencrypted connections. Both need a restart.

Connect with Inlet

1. Choose New Connection, pick SQL Server, and enter PC-NAME\SQLEXPRESS as the host. Inlet asks SQL Server Browser on the PC for the instance’s port. If you gave the instance a fixed port, you can enter PC-NAME and the port instead, which works even without Browser.

2. Enter the SQL Server login and password, and the database.

3. Under encryption, choose to encrypt without checking the certificate, unless the server has one your Mac can verify.

4. If Browser doesn’t answer, Inlet says so and tells you what to do:

   SQL Server Browser on PC-NAME didn’t say where the instance “SQLEXPRESS” is (UDP port 1434). Give the instance’s port instead.
   

5. If the PC is on another network, use Inlet’s SSH tunnel through a machine on that network. A tunnel forwards a TCP port, and Browser answers over UDP, so give the instance’s port.

6. Save the password in the Keychain, or have Inlet ask every time, and tag the environment.

Connect from the command line

Microsoft’s Go-based sqlcmd (brew install sqlcmd) takes the same server names. Quote a name with a backslash, so your shell keeps it:

sqlcmd -S 'PC-NAME\SQLEXPRESS' -U app -d app
sqlcmd -S PC-NAME,14330 -U app -d app

Its driver, go-mssqldb, asks Browser for the port when you give an instance name and no port. To check the fixed port from your Mac:

nc -vz PC-NAME 14330

What a missing Browser looks like. We couldn’t test on Windows for this page. Nothing listens on UDP 1434 in a Linux SQL Server container, so asking our SQL Server 2022 (16.0.4295.3) container for a named instance shows the same failure as a stopped or blocked Browser. With sqlcmd 18.6, localhost\NOSUCH gave up after about 9 seconds:

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to localhost\NOSUCH. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

.NET clients report the same thing as error: 26 - Error Locating Server/Instance Specified.

Troubleshooting

Microsoft’s suggested order: connect with the IP address and port first, then with the instance name, then with the computer name.

• Error Locating Server/Instance Specified (error 26): Browser didn’t answer. It isn’t running, a firewall drops UDP 1434, the instance is hidden (HideInstance), or the PC has several addresses and answered from another one. Start Browser and open UDP 1434, or connect with the port.
• The port works but the instance name doesn’t: the same causes; the TCP side is fine.
• Neither works: TCP/IP is off for the instance, the TCP port is blocked, or you have the wrong port. Check the “Server is listening on” line in the error log.
• It worked yesterday: the instance uses a dynamic port and got a new one when it restarted. Give it a fixed port.
• Login failed for user: if the server’s error log says Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. (state 58), switch to mixed mode and restart. For sa after a switch, state 7 is the one for a disabled login.
• The certificate chain isn’t trusted: the server is using its self-signed certificate. Encrypt without checking it, or install a proper one.
• Cannot open database requested by the login: the database name is wrong, or your login has no user in it.
• Couldn’t find the host “PC-NAME” in Inlet: your Mac can’t resolve the Windows computer name. Use its IP address.