SQL SERVER Dedicated Administrator Connection (DAC) enabled and how to connect.

In the SQL Server 2000 and earlier days it was all too often I found myself resorting to a server reboot to get control of my database server or access to a single database that had an issue.
One of the best additions to the SQL Server feature set was the Dedicated Administrator Connection (DAC). Prior to this being a part of the platform, an overloaded instance might not even allow a DBA to connect and perform any actions.

So lets look at if DAC is enabled and also look at determining who is connecting to your instance with the DAC. In general, the DAC should be left open on your instances and not used. If you have used it in an emergency, it's possible that you might reconnect to that instance as a part of your daily work and use the DAC. If someone else had the need to connect in an emergency, they wouldn't be able to since only one connection is allowed.

How to making the Connection to DAC. By default the DAC is only available from the server itself. If you want to allow this from other machines, you can enable remote administrator connections.

When connecting with the DAC, one can use a couple different methods. Either an SSMS window or SQLCMD.EXE can be used. Typically the SSMS is the easiest and you would prefix the server name with ADMIN:INSTANCE
NOTE: The admin: string is case insensitive. Therefore, ADMIN:, admin:, and AdMiN: are equivalent. Make sure to use all capital letters.




Once you've connected, your connection looks like any other connection with SSMS except you only have a single tab and the Object Explorer is not populated and you will see the ADMIN: prefix in the lower right corner of SSMS



Am I Using the DAC?
If you query the sys.endpoints DMV, you will see an endpoint created for the DAC. The name will be "Dedicated Admin Connection" and you will see which protocol is in use as well as the state. There is an is_admin_endpoint column as well.


SELECT * FROM sys.endpoints


With this information, I can start to determine who is connecting through this endpoint. Since each connection uses an endpoint, I can join to this table to find out which connection is using the DAC. The sys.dm_exec_sessions DMV contains a column, endpoint_id, that is useful to join back to this table.

With this information you can write a query such as this and see how is connected or if a connection is open. If no connection is open the results will be ZERO rows. Remove the WHERE e.is_admin_endpoint = 1 if you want to see all session data


SELECT s.session_id, e.name AS Endpoint, s.login_name, s.nt_domain, s.nt_user_name,
s.login_time, s.host_name, s.program_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.endpoints e ON e.endpoint_id = s.endpoint_id
WHERE e.is_admin_endpoint = 1


If remote Dedicated Administrator Connection (DAC) is not enabled it can be enable through the SSMS GUI or you can execute the below T-SQL to enable remote clients to utilize the Dedicated Administrator Connection. This by default will use port 1434 so ensure that the Firewall has a rule to allow this traffic. Otherwise DAC is available only available if logged into the machine and not remotely.

Use master
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more