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 - 1 Module, where Fabiano Ferreira sent the following question: “In the stpchecklist_security script, there is a validation that SQL Browser runs with a single instance and treats it as an error. However, is SQL Browser not used to allow connection use via remote admin connections? If so, would it be a mistake to leave it active? ”- And that was a GREAT doubt!
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?
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 because it identifies the requested instance name on the connection and returns which dynamic port that instance is currently using.
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.
Already 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 multiple instances, if all of them have a fixed port, you can leave SQL Browser disabled because connecting using the “server, port” format can be done without any difficulty (although the default “server \ instance” easier to decorate, it's true).
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.
Also, when you say “server, port”, you can change this port from time to time and with the impact of changing just the application connection string and making a very simple change to the instance protocol to change the port (on this screen from the print above). Already changing the instance name involves making more complex modifications to the database as well, in addition to applications, and if you use the instance name for some monitoring or routine, this change can impact that.
And the DAC connection? Disabling SQL Browser does not work!
That is, DAC does not work without SQL Browser !!
Calm down, young people! I will explain why this happens. Just as it translates the instance name to the port number, the SQL Browser service also identifies the prefix “ADMIN:” on 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, since SQL Browser is disabled, it did not do this “translation” to the port used by the DAC connection in this instance (Each instance can only have 1 DAC connection) and then SQL Server could not identify which port it will connect to. . The "magic" of using the DAC connection without SQL Browser is simply to inform the port used by that instance's DAC in the "server, port" format..
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:
sp_readerrorlog 0, 1, 'Dedicated admin connection'
Using the sys.dm_tcp_listener_states DMV
is_ipv4 = 1
AND [type] = 0
AND ip_address <> '127.0.0.1'
Using the sys.dm_server_registry DMV
value_name = 'TcpDynamicPorts'
AND registry_key LIKE '%\AdminConnection\Tcp'
And now, I will prove that I am using the DAC connection:
@@SPID AS MEU_session_id
JOIN sys.endpoints B ON A.endpoint_id = B.endpoint_id
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. SQL Server Security - 1 Modulewhere 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.