Wednesday, April 10, 2013

Set DAC port with a specific number

Usually named instance can uses dynamic port, if firewall setup on the server, you can use SQL Server Configuration Manager to assign a specific port to SQL Server instance. However, there is no UI to reconfigure the DAC port, you need to use regedit.exe to configure a specific port number.

1. Run regedit.exe
2. Open registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLX.InstanceName\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

"X" is the sql server version number, "InstanceName" is the sql server instance name. for instance, my sql2012 named instance is "SQL2012", the path is like below
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

3. Set Value "TcpDynamicPorts" to a specific port number










here we set the  "TcpDynamicPorts" to 12345.

4. Restart sql server service, then in errorlog you can find the DAC is monitoring port 12345
2013-04-10 15:30:16.22 Server      Server is listening on [ ::1 <ipv6> 12345].
2013-04-10 15:30:16.22 Server      Server is listening on [ 127.0.0.1 <ipv4> 12345].
2013-04-10 15:30:16.22 Server      Dedicated admin connection support was established for listening locally on port 12345.

5. Validate the connection with DAC
sqlcmd -S tcp:localhost,12345 -E

please note, don't need to use parameter -A in sqlcmd command line.












No comments:

Post a Comment