Configuring the server and the instance

Make changes to the configuration with an installation account that holds the following privileges:

  • SysAdmin on the SQL Server instance,
  • Local Admin on the Windows server.
  • In SQL Server Management Studio, run the following TSQL script on the instance:

    exec sp_configure 'backup compression default',1
    reconfigure

  • In SQL Server Management Studio, run the following TSQL script on the instance:

    exec sp_configure 'show advanced options',1
    reconfigure
    exec sp_configure 'remote admin connections',1
    reconfigure

  1. Open the Windows local security policy manager.
  2. Go to Local policies > User Rights Assignment.
  3. In the Lock pages in memory setting, add the SQL Server service account, PRF\SQLENGINE in our example.

The SQL Server listening port must be changed for security reasons.

  1. Open the SQL Server Configuration Manager utility.
  2. Go to SQL Server Network Configuration > Protocols for ENDPOINTSECURITY.
  3. Right-click on TCP/IP and select Properties.
  4. In the IP Addresses tab, under IPAll, change the TCP port. Enter port 30001.
    TCP/IP properties window 
  5. Select SQL Server services.
  6. In the panel on the right, right-click on SQL Server (ENDPOINTSECURITY) and select Restart.

On new Windows servers, the firewall is enabled and TCP ports are closed by default. All the traffic streams that SQL Server requires must be opened:

  • SQL TCP: TCP 30001 (SQL Engine)
  • SQL UDP: UDP 1434 (SQL Browser)
  1. Open the Windows Defender firewall application with advanced security features.
  2. In Incoming traffic rules, create a Port rule with the following parameters:
    • Protocol TCP and Port 30001,
    • Action: Allow connection,
    • Profile: Domain, Private and Public
    • Name: SQL TCP.
  3. Create a second Port rule for UDP 1434 with the same parameters, that you will name "SQL UDP".

TIP
You can also create rules using Powershell:
New-NetFirewallRule –Name "SQL TCP" -DisplayName "SQL TCP" -Profile Any -Enabled True -Protocol TCP -LocalPort 30001 -Action Allow

New-NetFirewallRule –Name "SQL UDP" -DisplayName "SQL UDP" -Profile Any -Enabled True -Protocol UDP -LocalPort 1434 -Action Allow

  • In SQL Server Management Studio, test connections with a Windows authentication, then a SQL Server authentication.