Speak guys !!
In this article I would like to share with you a little study on how to connect using the Dedicated Admin Connection (DAC) connection without SQL Browser. This idea came from a question sent in my course of SQL Server Security - Module 1, where Fabiano Ferreira sent the following question: “in the stpchecklist_seguranca script, there is a validation about SQL Browser being executed with a single instance and treating it as an error. However, SQL Browser is not used to allow the use of the connection via remote admin connections? If so, would it be a mistake to keep him active? ” - And that was an EXCELLENT question!
What is the Dedicated Admin Connection (DAC) connection?
As you know, the connection Dedicated Admin Connection (DAC) Allows SQL Server to reserve a connection slot so that in extreme cases, such as all connections being used or logon triggering preventing connections, you can still log in to the instance and try to fix the problem without restarting the SQL service. . To learn more about the DAC connection, click here.
What is SQL Browser? How does it work?
SQL Browser is the SQL Server service that “translates” the name of the instance to the port it is using. I will exemplify how the server instances are directed:
That is, there are the instances SQL2008, SQL2012, SQL2014, SQL2016, SQL2017, and SQL2019 on this server (Ex: dirceu-vm \ sql2017). When you make a connection attempt using this instance, SQL Browser will identify the name of the reported instance (SQL2017) and return which port this instance is using, according to the settings:
In the case above, my instance is using a dynamic port, that is, you enter the value “0” in this field and each time the service is started, this instance will use a random port. In this scenario, where a server has multiple instances, SQL Browser is important, as it identifies the name of the instance requested in the connection and returns which dynamic port that instance is currently using.
Connecting in an instance with SQL Browser enabled:
Note that in the example above, I didn't need to enter the port number this instance is using, because from the moment I enter the instance name, the SQL Browser service will identify which port this instance is using and perform The connection for me. If you disable SQL Browser, you will have to manually tell on the connection which port the instance is using. As this port changes each time the service is started, it is difficult to know which port of each instance.
Now, let's use a fixed port for our instance (1437):
In the example above, I already know which port my instance will use, because it is fixed and I defined it. Even if the server has several instances, if all of them have a fixed port, you can leave the SQL Browser disabled, as the connection using the format "server, port" can be made without any major difficulties (although the default "server \ instance" easier to decorate, it’s true).
Connecting in an instance with SQL Browser disabled:
Why disable SQL Browser?
After the above explanation about SQL Browser, it became clear that SQL Server does not need this feature to function normally (Except in Clustered environments. In this case, SQL Browser cannot be disabled), as it ends up exposing the name and the ports used by each instance on the network. If you are searching for internet security checklists or good security practices, you will find that most recommend disabling this service.
Although I don't think this is going to be a great Security Improvement Since the risk of keeping SQL Browser running is relatively low, I think any additional hardship you might offer to a potential attacker is worth implementing.
In addition, when you enter “server, port”, you can change this port from time to time and with the impact of changing only the application's connection string and making a very simple change to the instance protocol to change the port (on this screen of the print above). Already changing the name of the instance involves making more complex modifications to the database as well, in addition to the applications, and if you use the name of the instance any monitoring or routine, this change can impact that.
And the DAC connection? Disabling SQL Browser does not work!
With SQL Browser enabled, simply add the prefix ADMIN: before the server name \ instance to connect using DAC (if this connection is not being used, of course):
And so, I can connect using the DAC:
But then when I disable SQL Browser and try to connect using DAC, I run into this error message:
That is, DAC does not work without SQL Browser !!
Calm down, young people! I will explain why this happens. Just as it does the "translation" of the instance name to the port number, the SQL Browser service also identifies the prefix "ADMIN:" in the connection and returns the port number of the DAC connection of this instance, and thus, the connection is successfully made using the DAC.
That is, as the SQL Browser is disabled, it did not make this "translation" for the port used by the DAC connection in this instance (Each instance can only have 1 DAC connection) and then SQL Server was unable to identify which port it will connect to . The "magic" for using the DAC connection without the SQL Browser is simply to inform the port used by the DAC of that instance in the format "server, port".
When we are using the default instance, the default port of the DAC connection is 1434, but when we have named instances and especially multiple instances, the port will be dynamic. And to find out which port is being used by the DAC connection, we can use the queries below:
Using sp_readerrorlog
|
1 |
sp_readerrorlog 0, 1, 'Dedicated admin connection' |
Using the sys.dm_tcp_listener_states DMV
|
1 2 3 4 5 6 7 8 |
SELECT [port] FROM sys.dm_tcp_listener_states WHERE is_ipv4 = 1 AND [type] = 0 AND ip_address <> '127.0.0.1' |
Using the sys.dm_server_registry DMV
|
1 2 3 4 5 6 7 |
SELECT * FROM sys.dm_server_registry WHERE value_name = 'TcpDynamicPorts' AND registry_key LIKE '%\AdminConnection\Tcp' |
Using SQL Server Log
Once we identify which port is used by the DAC connection (in this case, it is 49830), just enter it in the connection string:
And now, I will prove that I am using the DAC connection:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT DISTINCT A.endpoint_id, A.local_net_address, A.local_tcp_port, B.[name], A.session_id, @@SPID AS MEU_session_id FROM sys.dm_exec_connections A JOIN sys.endpoints B ON A.endpoint_id = B.endpoint_id WHERE A.endpoint_id IS NOT NULL AND A.local_tcp_port IS NOT NULL AND B.is_admin_endpoint = 1 |
And that's it folks!
I hope you enjoyed this article!
To learn more about all the security in SQL Server, be sure to take my course. Security in SQL Server - Module 1where I discuss various configurations, best practices, and tips for improving the security of your SQL Server environment, and it would also be VERY interesting to take the course on Windows Fundamentals for DBA SQL Serverlegend Rodrigo Ribeiro, where he discusses several very important topics that a DBA should know, including the DAC connection, even showing how to change this port :).
A hug and see you in the next article.
