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.
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
- In SQL Server Management Studio, run the following TSQL script on the instance:
exec sp_configure 'backup compression default',1
reconfigure
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
- 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
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
- Open the Windows local security policy manager.
- Go to Local policies > User Rights Assignment.
- In the Lock pages in memory setting, add the SQL Server service account, PRF\SQLENGINE in our example.
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
The SQL Server listening port must be changed for security reasons.
- Open the SQL Server Configuration Manager utility.
- Go to SQL Server Network Configuration > Protocols for ENDPOINTSECURITY.
- Right-click on TCP/IP and select Properties.
- In the IP Addresses tab, under IPAll, change the TCP port. Enter port 30001.
- Select SQL Server services.
- In the panel on the right, right-click on SQL Server (ENDPOINTSECURITY) and select Restart.
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
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)
- Open the Windows Defender firewall application with advanced security features.
- 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.
- 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
![Closed](../../Skins/Default/Stylesheets/Images/transparent.gif)
- In SQL Server Management Studio, test connections with a Windows authentication, then a SQL Server authentication.