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.
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.
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)
