Hello people,
Good Morning!
In this post I will demonstrate how to enable / disable the remote Dedicated Administrator Connection (DAC) feature of the SQL Server instance, verify that the feature is active, and how to use it.
Introduction - What is DAC?
Imagine in the following situation: All workers in the instance are in use and no longer allowed to connect to the bank, as you would to analyze what is happening and take action to solve this problem, since you cannot even to connect?
With that in mind, Microsoft has created DAC, a very useful feature for DBA as it allows connection to the instance using a unique and dedicated feature for Database Administrators.
Its operation is quite simple: Once activated, the instance creates the DAC connection at startup and leaves it in standby, waiting for the DBA connection. Even if the instance is overloaded and no workers are available, this connection is already connected to the database and can be used by the DBA.
Remember that this feature can only be used by users with sysadmin permission and only one user can use this connection at a time.
How to tell if remote DAC is active?
To identify if this feature is enabled on the instance, simply use sp sp_configure:
|
1 |
EXEC sp_configure 'remote admin connections' |
Viewing the DAC Connection through DMV sys.endpoints
How to enable DAC?
I will demonstrate two ways: Using T-SQL and SQL Server Management Studio.
Using Transact-SQL
|
1 2 3 4 5 6 7 |
Use master GO /* 0 = Apenas DAC local; 1 = DAC remoto */ sp_configure 'remote admin connections', 1 GO RECONFIGURE GO |
Using SQL Server Management Studio
Right-click on your instance and select the "Facets" option, as shown in the image below:
This will open the “View Facets” screen. In the Facets combo, select “Surface Area Configuration” and set the TRUE value to the “RemoteDacEnabled” parameter.
SQL Server Express and the DAC Connection
By default, SQL Server Express does not have the DAC connection feature by default. However, this can be enabled using a trace flag. This is the third way to enable the DAC connection, which is by using the 7806 trace flag in SQL Server Express initialization parameters. This third option is especially useful for scenarios where you have a problem preventing user login (eg Login Trigger), the DAC connection is not enabled and you are unable to login to the instance.
To enable it, simply open SQL Server Configuration Manager, and view the properties of the instance that you want to enable DAC:
In the instance configuration screen, go to the “Startup Parameters” tab and add the parameter “-T7806”:
After committing the changes, you must restart the SQL Server Express service for the changes to take effect. After this you will see that you can now connect to the instance using the DAC connection.
Connecting on instance using DAC
Using the SQLCMD
To connect to the instance through SQLCMD using DAC, simply use the -A parameter, as shown below:
Want to know more about SQLCMD, the SQL Server command line utility? Visit this link.
Using SQL Server Management Studio
To connect to the instance through SQL Server Management Studio using DAC, simply use the prefix ADMIN: before the instance name at connection time, as shown below:
To identify who is using the DAC connection on the instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT B.session_id, A.name, B.connect_time, B.last_read, B.last_write, B.client_net_address FROM sys.endpoints A INNER JOIN sys.dm_exec_connections B ON A.endpoint_id = B.endpoint_id WHERE A.is_admin_endpoint = 1 |
Important: Because only one DAC connection is allowed at a time, you cannot use it in Object Explorer, only in the New Query screen.
If you are having difficulty using the DAC connection in an environment where the SQL Browser service is disabled, please read this article: SQL Server - How to Connect Using Dedicated Admin Connection (DAC) Connection Without SQL Browser.
Thanks for stopping by and see you next time!
sql, sql server, dac, Dedicated Administrator Connection, Remote Admin Connections, can't connect, dba connection, connection error, login timeout, connection timeout
sql, sql server, dac, Dedicated Administrator Connection, Remote Admin Connections, can't connect, dba connection, connection error, login timeout, connection timeout
