Monday, 28 September 2020

[SQL Server] Set the specific port number for SQL Server DAC

Set(change) the specific port number for SQL Server DAC

DAC is very useful when your SQL Server is stuck.

If you want to know how to configure SQL Server DAC, then click here.

Problem

If you have installed single instance on the server, then it will be fine as DAC will use 1434 port.

But what if you have installed multiple instances?

Then SQL Server will use random ports so you don't know which port you need to request to open for DAC to security team.

In this scenario, you can specify the port number for the specific instance.

Check Registry

The location of DAC port registry is here.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<number>.<InstanceName>\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

In my case, I have installed 2 SQL Server instances on the server.

* SQL Server 2017: SQL2017 is the instance name.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.SQL2017\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

TcpDynamicPorts is the value you need to take a look.

As you can see the port number is a random value and it's 50147 for the moment.

* SQL Server 2019: SQL2019 is the instance name.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.SQL2019\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Modify the registry

Now, modify the value of TcpDynamicPorts.

* SQL Server 2017: 22017

* SQL Server 2019: 22019

Open the firewall for DAC

* SQL Server 2017: 22017

* SQL Server 2019: 22019

Restart services

Restart SQL Server Services that you've changed the port.

Check ports and verify connections

Check the port is opened.

netstat -an | findstr "22017"

Connect to the defined port from local -- 22019 (SQL 2019)

sqlcmd -S tcp:localhost,22019 -E

Connect to the defined port from remote -- 22017 (SQL 2017)

sqlcmd -S <server>,22017 -U <user> -P <password> -d master

Sunsick's Tech Blog