SQL Server 2008 Authentication mode changed on its own

Question

This morning we experienced an issue where our production server seemed to decide to change from Mixed authentication mode to Windows only authentication of its own volition. I was curious to know if any of you other DBAs out there had ever experienced this or might be able to point me in a good direction to try and make sure this doesn't happen to us again. The only other thing that seemed to be going on database wise, beyond typical use, was that the transaction log had just completed a backup.

Answer 1

The chances that the Authentication Mode changed on it's own is pretty near zero. More likely, it got changed by accident.

This is a change that requires a restart of the SQL Server service in order for it to take effect. What likely happened is that the Authentication Mode was changed sometime since the last restart (which could have been months ago)--then, when the instance restarted, the change "suddenly" took effect seemingly on it's own.

This particular configuration is stored in the registry, so if you have OS backups of your server, you could do some research to go back in time to try to determine when it changed.

Answer 2

I would check the server log if you can, I would definitely setup a DDL trigger to alert you when it changes.

I'm skeptical that it changed on its own, but I found a few articles showing that you can look at the registry to view it's auth mode and it makes me wonder if the auth mode was changed at the registry / server level and not the SQL Server level.

Here's the registry and code to view the registry level:

DECLARE @AuthenticationMode INT  
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
N'Software\Microsoft\MSSQLServer\MSSQLServer',   
N'LoginMode', @AuthenticationMode OUTPUT  

SELECT CASE @AuthenticationMode    
WHEN 1 THEN 'Windows Authentication'   
WHEN 2 THEN 'Windows and SQL Server Authentication'   
ELSE 'Unknown'  
END as [Authentication Mode] 

Source

Here's another topic about the registry. I would probably set up a powershell script that can check every 5 min and see if it's set up correctly. If not, I'd have it change it. I'd look through the server logs and see if that shows any authentications to the server.

With the powershell script, if you make it log the results each 5 min and provide a datestamp, you'll be able to pinpoint the time it changes if it changes again. Perhaps it's a GPO update?

More on the registry level.

Script to change the registry.

One more note, it appears you need to restart for this change to take affect. Pinpoint when the server restarted last, I'm guessing you can find some activity leading up to that.