SQL Server – Mixed Mode Authentication Not Being Set

sql server

I have a local developer instance of SQL Server installed on my machine. I set the mixed mode authentication:

  1. Open SSMS
  2. Connect to localhost (windows auth)
  3. Right click localhost connection, right click -> properties
  4. Security tab
  5. Choose "SQL Server and Windows Authentication mode"

I restarted my computer, and verified the above settings are selected.

I cannot login using a username and password I created. Windows Event Viewer shows

Login failed for user 'ctx'. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only. [CLIENT: ]

Why does event view say "Integrated authentication only" when SQL Server shows "SQL Server and Windows Authentication mode"?

-- Windows 10 Enterprise 

SELECT @@VERSION
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 17763: ) 

Best Answer

To start, even though the gui shows mixed mode authentication, running

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')
WHEN 1 THEN 'Windows Authentication'
WHEN 0 THEN 'Windows and SQL Server Authentication'
END as [Authentication Mode]

gives

'Windows Authentication'

Two related small things resulted in this issue. (1) My windows domain account is not an administrator, so I can't restart the SQL Server instance from the Configuration Manager. (2) Restarting windows does not reload the SQL Server configuration options, or at least, it won't change the authentication setting.

A windows domain account with administrative privileges on my local machine was able to stop and restart SQL Server from the Configuration Manager. I can now login with the SQL account I created.

After that, the above query gives

'Windows and SQL Server Authentication'

So, even though I can change the authentication options, and SQL Server displays in the GUI that the options have been changed, the options are not actually changed until the SQL Server instance is manually restarted by an admin (rebooting doesn't do it).