SQL SERVER – Getting Started with DBCC FLUSHAUTHCACHE on SQL Azure

First thing first – the command DBCC FLUSHAUTHCACHE only is supported on SQL Azure. If you are going to write a comment that this does not work on the on-premises SQL Server, I totally agree as this command is only available on SQL Azure. Now that we have discussed this let us move to the topic of how this feature works.

SQL SERVER - Getting Started with DBCC FLUSHAUTHCACHE on SQL Azure DBCC-FLUSHAUTHCACHE-800x369

On-premises SQL Server

When we are working on-premises SQL Server and change the password of our server, all the active connections get terminated and you will have to provide the new password and re-authenticate your connection. This is fantastic and very much required. I personally prefer this way.

Solarwinds

SQL Azure

However, the behavior of change of password is a bit different on SQL Azure. Here authentication cache is copied to user database memory and it is validated (or reauthenticated) silently on the background at every 10 hours if your connection was continuously active. Please note that the user does not have to provide any input or feedback in this situation, it all happens in the background and automatically.

This means, if you change your password on SQL Azure, it is quite possible till the next authentication situation arises, you will be able to keep on querying your database. This may be good in some situation but in the most situation, if you have changed your password in the security scenario, you may want this to mimic the behavior of your server like on-premises and disconnect all the connection.

DBCC FLUSHAUTHCACHE

In this situation, you can provide the following command and it will remove all the details of the authentication from the user database memory and forces your connection to re-authenticate with the server.

DBCC FLUSHAUTHCACHE

One of my client who is one of the largest banks in Asia often hires me for my Comprehensive Database Performance Health Check. They recently had to change the password on their server due to the security concern and to their surprise, they found they are not able to expire all the connections to the server, they had immediately called me on the phone and I was able to help them by running above command.

Here is my question to you – how many of you know about this command? Leave a comment, please.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – DROP Multiple Temp Tables Using Cursors on Azure
Next Post
SQL SERVER – Different Size of COPY_ONLY Full Backup on Primary and Secondary Replica in Always On

Related Posts

Leave a Reply

Menu