I have a local developer instance of SQL Server installed on my machine. I set the mixed mode authentication:
- Open SSMS
- Connect to
localhost
(windows auth) - Right click localhost connection, right click -> properties
- Security tab
- 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
gives
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
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).