Understanding DBCC FLUSHAUTHCACHE

This command only applies to Azure SQL Database, at a high level it empties the database authentication cache for logins and firewall rules for the current USER database.

In Azure SQL Database the authentication cache makes a copy of logins and server firewall rules which are in the master database and puts them into memory within the user database. The Database Engine attempts re-authorisation using the originally submitted password and no user input is required.

If this still doesn’t make sense, then an example will really help. What I will do is connect to my Azure SQL Database using my server admin, then I will issue some queries in different query windows. I will then change the server admin account where then I will issue the DBCC FLUSHAUTHCACHE command. What will happen?

Step 1: I Connect to Azure using the server admin account and write a quick select statement.

stepssms

Step 2: In the background I change the server admin account password via the Azure portal.

resetpw

Step 3: I connect back to the database via SSMS (SQL Server Management Studio) and issue more queries. It will still authenticate and work using the original password.

selectstar

Step 4: I now run the DBCC FLUSHAUTHCACHE (currently commented out) and open up a new query window.

Soon as I hit new query I get the below.

nopass

I now need to re-authenticate with the new password that I changed in step 2. Hopefully it makes sense now.

 

Advertisements

One thought on “Understanding DBCC FLUSHAUTHCACHE

  1. Pingback: Flushing The Authentication Cache – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s