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
References
- remote admin connections Server Configuration Option
- Diagnostic Connection for Database Administrators
- sqlcmd Utility
No comments:
Post a Comment