Why is Dedicated Admin Connection a lifeline ? (SQL Server)

The Dedicated Admin Connection is a lifeline you may potentially use if you have a serious performance issue and are unable to access and run queries for troubleshooting. Note: the dedicated admin connection option has been available since SQL Server 2005.

The dedicated admin connection has one reserved scheduler that has one thread allocated, the benefit is the one thread would always be available for processing requests that can be used to perform troubleshooting. Although only one sysadmin can use the dedicated admin connection at a time.

It would be advised to run simple, quick queries and do not abuse the use of dedicated admin connection as it will not benefit the performance of your queries in any way as parallelism is not possible, as only one thread would be available.

As part of best practice, I would highly recommend the Dedicated Admin Connection is enabled at the SQL Server instance configuration.

Within this blog, I will demonstrate the different ways of connecting for the Dedicated Admin Connection. Also, I will demonstrate one example when dedicated admin connection would be useful.

Perquisites:

  • The account you’re going to use for dedicated admin connection needs to have sysadmin.
  • The dedicated admin connections need to be enabled, as it’s only enabled for those accounts that are logged onto the local machine.

Connecting:

The SSMS or SQLCMD could be used to login using the dedicated admin connection.

Accessing via the SSMS you cannot directly connect using the connect option or when SSMS is launched with the connect to server prompt. If either of these are attempted, you would receive an error. Further error information and explanation will be available in the blog post: “Encountering errors when attempting to connect with Dedicated Admin Connections”. The workaround for this would be to access SSMS click cancel then open a query window, at this point when you get the “Connect to Server” prompt you should be able to connect using the dedicated admin connection.

To use SQLCMD for connecting, firstly open a command prompt. Then you have two options for providing the admin parameter for connecting to the Dedicated Admin Connections.

SQLCMD -S Admin:<Host\Instance Name> -U <login_id> -P <password>

SQLCMD -S <Host\Instance Name> -U <login_id> -P <password> -A

SQLCMD -S Admin:<Host\Instance Name>

SQLCMD -S <Host\Instance Name> -A

Without the -U and -P parameters it would use your network account you are currently logged in with.

Example:

So now we’ve confirmed that we can successfully connect using the Dedicated Admin Account. Let’s see when Dedicated Admin is useful.

The below trigger rollbacks all logins. Please note: the trigger below is only for demonstration purposes and should not be used on any SQL Server environment. Unless you want to test the theory, I would advise performing this on a test environment.

USE [master]

GO

CREATE TRIGGER [Fail_logins] ON ALL SERVER

FOR LOGON AS

BEGIN

 DECLARE @ErrorText [varchar] (128) 

 SET @ErrorText = 'Cannot allow login to SQL Server. '

 IF SUSER_SNAME() <> ''

 BEGIN

 PRINT @ErrorText

 ROLLBACK;

 END

END;

GO

 

ENABLE TRIGGER [Fail_logins]

 ON ALL SERVER

GO

Once the above trigger has been created any logins would fail with following error:

At this point, it will be a panic moment as all attempted connections would be rolled back.

The only lifeline you have is Dedicated Admin Connection.

Connect using the dedicated Admin Connections as demonstrated above, you will find using Dedicated Admin Connection will allow successful connection to the SQL Server.

You will want to investigate what triggers are currently in place and the event taking place.

Run the following query:

select

 t.name

, t.object_id

, t.is_disabled

, te.type_desc

 FROM master.sys.server_triggers t

 INNER JOIN master.sys.server_trigger_events te

 ON t.object_id = te.object_id

go

In the results you will find the trigger “Fail logins” is currently enabled (value: 0) and the event type is associated with logon.

The trigger will require disabling or to be deleted to remediate the problem.

DISABLE Trigger <Trigger Name> ON ALL SERVER;

GO

DROP Trigger <Trigger Name> ON ALL SERVER;

GO

For demonstration purposes I will disable the trigger and drop the actual trigger through SSMS GUI, to show that login to SQL Server can be made successfully again.

When attempting to connect to SQL Server without Dedicated Admin Connection it should be successful.

Step 1:

Step 2: The login has been successful.

Step 3: Navigate to the Server Objects from the object explorer and expand. Then expand the Triggers object explorer, within this, you would find the trigger that was created.

Step 4: You could either script the trigger to be dropped or click Delete.

Step 5: DROP the Fail_logins trigger.

Review

From this blog, you have learned how to access the Dedicated Admin Connections and the importance. Also, a scenario when Dedicated Admin Connection is useful and an understanding of Dedicated Admin Connection purpose. Bear in mind the Dedicated Admin Connection is your only lifeline if there was a critical performance issue or you cannot connect to the SQL Server instance successfully.

Further readings

I will be writing further blogs on “How to enable the Dedicated Admin Connection” and “Encountering errors when attempting to connect with Dedicated Admin Connections” and would recommend reading both blogs once available. Therefore, keep a lookout for an update on those blogs.

Leave a Reply

avatar
  Subscribe  
Notify of