Notes for using Microsoft SQL Server on Windows in conjunction with Docker/macOS/Linux (Connect 2024)

For an instance of Microsoft SQL Server running on Windows to be accessible from Linux or macOS, some default settings need to be adjusted. This chapter describes the basic procedure and is intended as a guide for the administrators concerned. However, depending on the actual environment, the required settings may differ from the setup suggested here.

Creating the databases

In this scenario, it is recommended to create the required databases in advance in order to restrict access to individual databases. Proceed as follows:

Start SQL Server Management Studio from the Windows Server Start menu and connect it to the database
In the Object Explorer (on the left side of the window), expand the node for the SQL Server instance to be connected to the Connect Server
Right-click the "Databases" node and select the "New Database..." option from the context menu
Set the name of the database and adjust the other settings if necessary
Click the "OK" button to create the database

Configure access via TCP/IP

The easiest way to access a Windows-based instance of Microsoft SQL Server from a Linux or macOS-based system is using TCP/IP. This protocol needs to be enabled after installing Microsoft SQL Server. Proceed as follows:

If you have not already done so, start the SQL Server Configuration Manager from the Windows Server Start menu
Select the appropriate subitem for the SQL Server instance from the "SQL Server network configuration" item
Double-click the "TCP/IP" item on the right side of the window
In the following window, on the "Log" tab, set the "Enabled" option to "Yes" and the "Monitor all" option to "No".
Now switch to the "IP addresses" tab and set the "Enabled" option to "Yes" and the "Port" option to "1433" for the IP addresses you want to access using TCP/IP.
Click the "OK" button to apply the changes.
Confirm the following warning by clicking the "OK" button.
Close the SQL Server Configuration Manager.

Firewall configuration

The next step is to make sure that the Windows server with the SQL Server instance can accept requests from the Connect server on TCP/IP port 1433. The procedure here can differ depending on the firewalls used. The following is an example of the procedure for configuring the Windows firewall:

Start Windows Defender Firewall with Advanced Security from the Windows Server Start menu.
In the tree on the left side of the window, select the "Inbound rules" node.
In the Actions on the right side of the window, click "New Rule...".
Now select the "Port" option and then click the "Next" button.
Enter the port 1433 and click the "Next" button again.
Confirm the default setting in the window that now appears by clicking the "Next" button again.
Now select the network profiles for which the SQL Server is to be accessible. Then click on the "Next" button.
Now enter a meaningful name and a short description for the firewall rule and click the "Finish" button

Authentication

Since, in this scenario, Microsoft SQL Server cannot use Windows authentication, the authentication mode must now be set to "Windows and SQL Server". If the authentication mode "SQL Server" is already used, no configuration change is required.

Start SQL Server Management Studio from the Windows Server Start menu and connect it to the database
In the Object Explorer (on the left side of the window), right-click the node for the SQL Server instance to be connected to the Connect Server and select "Properties" from the context menu.
On the left side of the dialog that now appears, select the "Security" item.
Enable the option "SQL Server and Windows Authentication mode".
Click the "OK" button to apply the change
Confirm the following warning also by clicking the "OK" button

Restarting the SQL Server instance

For the changes made so far to take effect, the SQL Server instance must be restarted. First, make sure that the SQL Server instance is not currently required. Proceed as follows to restart the instance:

If not already done, start SQL Server Management Studio from the Windows Server Start menu and connect it to the database.
In the Object Explorer (on the left side of the window), right-click the node for the SQL Server instance to be connected to the Connect Server and select "Restart" from the context menu.
A UAC dialog may now appear, as administrator rights are required for this action. Confirm this by clicking on the "Yes" button.
Confirm the query of SQL Server Management Studio by clicking the "Yes" button.

The SQL Server instance is now stopped and then restarted. Depending on the size of the managed databases, this process can take from a few seconds to a few minutes. The action is completed as soon as the displayed progress dialogs have disappeared.

Create a database user

In the last step, a database user must be created, with the help of which the Connect Server authenticates itself to the SQL Server. Proceed as follows:

If not already done, start SQL Server Management Studio from the Windows Server Start menu and connect it to the database.
In the Object Explorer (on the left side of the window), expand the node for the SQL Server instance to be connected to the Connect Server and expand its “Security” subnode
Right-click the "Logins" node and select the "New Login..." option from the context menu.
In the following dialog enter the desired user name, activate the option "SQL Server authentication", enter the desired password and deactivate the options "Enforce password expiration" and "User must change Password at next login".
Now switch to the "User Mapping" area.
In the Users mapped to this login area, select the databases used by Connect Server one by one, and in the Database role membership for: ...", select the db_owner option.
Click the "OK" button to create the user.

Testing the connection

The accessibility of the SQL server via TCP/IP can be easily checked from the shell. In the following command [IP] must be replaced by the IP address of the SQL server.

For further testing, a SQL Server client must be installed. The procedure depends on the operating system. The following link contains the corresponding instructions:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools?view=sql-server-ver15

After installing the client tools, the following command should establish a connection to the database (the values in square brackets should be replaced accordingly):

Connection with default instance:

Connection with named instance:

Connection strings for the configuration of the Connect Server

The connection strings for the Connect Server have the following format (replace the values in square brackets accordingly):