2

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: )
Improve this question
4
  • Out of curiosity, do you see the same error message in the SQL Error Log for localhost?
    – Scott Hodgin - Retired
    Oct 17, 2019 at 12:33
  • What does SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END as [Authentication Mode] this code return?
    – SqlWorldWide
    Oct 17, 2019 at 12:38
  • Having several instances and confusion between those comes to mind. As well as having a client alias pointing to somewhere else. Also, I'd check the registry to verify the setting actually modifies the registry, LoginMode (here are some words about the strange 3-states of this reg entry: sqlblog.karaszi.com/watch-out-for-old-stuff).
    – Tibor Karaszi
    Oct 18, 2019 at 5:55
  • Try connecting to your instance, using .UDL, then check error log for the message. This might help you in diagnosing the issue: "Solving Connectivity errors to SQL Server"
    – Milan
    Oct 18, 2019 at 8:38

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.