Skip to content

SQL Server#

Currently preferred server version is MS SQL 2016 std. or higher. SQL Server databases must be created using generated upgrade scripts or migrations.

SQL Port

Ensure port 1433 is opened for incoming traffic in your firewall, when hosting multiple instances, the port configured dynamically and will have a random available port-number.
Use the SQL Configuration Manager to change the port on an SQL Instance.

  1. Open SQL Server Configuration Manager
  2. In the SQL Server Network Configuration menu, select Protocols for MSSQLSERVER
  3. Make sure TCP/IP is enabled

SQL Server TCP/IP Protocol

Ports configuration#

It is best to not have a Dynamic Tcp Port because this only works with named instances and also requires SQL Browser service to be running. You do not have to stick to the default port at all times. Moreover, it might be even better to change it due to security reasons. In order to configure the port number:

  1. Open SQL Server Configuration Manager
  2. In the SQL Server Network Configuration menu, select Protocols for MSSQLSERVER
  3. Right-click TCP/IP and select Properties
  4. In the window that opens, click the IP Addresses tab
  5. You can either set a different port number for each one of the IP addresses or simply set one for all of them
  6. Click OK

Default Port

It is best to set the IPAll - TCP Port to 1433 the default port for SQL Server. Only when you have multiple instance of SQL Server running (or other network service has the port in use) you can set a alternate port number, this will require you to include the port in all ConnectionStrings as it is not the default port anymore.

SQL Server TCP/IP Port

The changes you have just made will not take effect until you restart the service: Restart SQL Service

SQL Authentication#

When running a SQL Server on Windows you have the options during installation of using Windows Authentication or mixed mode with SQL Authentication. Alternatively when you skip past this setting we can change this in de server options. It is a requirement when our Docker containers need to connect to SQL server, they are not part of the domain or host processes and will only connect via IP enabled connections with SQL Server Authentication
Authentication Mode

There is an alternative Database setup called Contained Databases, this supports Database contained users and is similar to how databases in Azure manages databases. Most all the metadata is contained within the database, this includes users.

Computer Management - SQL Server Configuration#

When you have SQL Server installed locally and you can't find the SQL Server Configuration Manager under Windows, then try the alternative, it's also available in the Computer Management console.
Computer Management - SQL Server Configuration

What is Microsoft SQL Server Browser?#

Microsoft SQL Server Browser runs as a Windows service on the server PC. Microsoft SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about Microsoft SQL Server instances that are installed on the PC. Microsoft SQL Server Browser contributes to three actions:

  • Browses a list of available servers
  • Connects to the correct server instance
  • Connects to Dedicated Administrator Connection (DAC) endpoints

For each instance of the database engine, the Microsoft SQL Server Browser service provides the instance name and the version number. Microsoft SQL Server Browser is installed with Microsoft SQL Server 2005 and also provides assistance for earlier versions of Microsoft SQL Server.

How Microsoft SQL Server Browser Works#

When an instance of Microsoft SQL Server starts, if the TCP/IP or VIA protocols are activated, a TCP/IP port is assigned. If the named pipes' protocol is enabled, Microsoft SQL Server listens on a specific named pipe. This port or pipe is used by that specific instance to exchange data with client applications.

During installation, port 1433 and pipe SQL query are assigned to the default instance. If required, these can be changed later by the server administrator in Microsoft SQL Server Configuration Manager. Because only one instance of Microsoft SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances, including Microsoft SQL Server Express.

By default, named instances are configured to use dynamic ports, so an available port is assigned when Microsoft SQL Server starts. When connecting client PCs, you can specify the desired port. However, if the port is dynamically assigned, the port number can change any time Microsoft SQL Server is restarted, so the correct port number becomes unknown to the client PC.

On startup, Microsoft SQL Server Browser starts and claims UDP port 1434. Microsoft SQL Server Browser reads the registry, identifies all Microsoft SQL Server instances on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, Microsoft SQL Server Browser returns all ports enabled for Microsoft SQL Server.