Getting "Server is configured for Integrated authentication only" Login Failures when "Mixed Mode Authentication" is Cleary Enabled
SQL Server Log Auth failure message:
12/20/2022 19:35:27,Logon,Unknown,Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Integrated authentication only.
Which does not shore up with the setting of the instance - Mixed Mode is On:
Allow Remote Connections:
I am attempting to authenticate using sql credentials pulled from secretsmanger. The endpoint has the key and secret pointer and all policies check pout. The scenario is:
DMS Endpoint --> Replication Instance(Secrest Manager) --> RDS Sql Server Instance
When I test the connection from the DMS Endpoint above, I get the following message:
Test Endpoint failed: Application-Status: 1020912, Application-Message: Cannot connect to SQL Server Authentication failed, Application-Detailed-Message: RetCode: SQL_ERROR SqlState: 28000 NativeError: 18456 Message: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''.
However, I can use the same sql login from my laptop via ZScaler and log into the rds instance.
I am sure everything is connected, and I rebooted that RDS instance and double-checked settings. There is a Postgres RDS instance with the exact same configuration and the connection test from the DMS Endpoint returns success.
It is just odd that SQL Server is logging that "integrated security only" is enabled and failing logins, but the configuring is showing mixed mode. Has anyone seen anything of the sort?
NOTE: It is behaving like a double-hop issue, however, that should not occur with SQL Authentication and generally on Windows you get the ANONYOMOUS_USER as the username.
UPDATE : I noticed there is a rdsadmin user with a disabled status,
- Più recenti
- Maggior numero di voti
- Maggior numero di commenti
This is not an answer to the error posted above but this can be closed. The problem went away after a destroy and new deploy of the environment.
"An attempt to login using SQL authentication failed. Server is configured for Windows authentication only." can be returned in the following situations.
- A SQL Server instance is not configured for mixed mode authentication - it seems not to be the case, as be default all RDS SQL Servers have mixed mode authentication;
- When SQL login and password are empty. Please check "Misleading errors: “Server is configured for Windows ..." - https://sqlstudies.com/2018/06/18/misleading-errors-server-is-configured-for-windows-authentication-only-but-its-not/
- When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection doesn't explicitly specify that the connection should use a trusted connection;
- When SQL server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection string doesn't explicitly specify the use of a trusted authentication.
To resolve this issues 2 and 3, include TRUSTED_CONNECTION = TRUE in the connection string. For further details, please take a look at links below for further details:
"MSSQLSERVER_18456 - More rare possible cause" - https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-18456-database-engine-error?view=sql-server-ver16
Kind Regards
Simon M.
Contenuto pertinente
AWS UFFICIALEAggiornata 2 anni fa- AWS UFFICIALEAggiornata 2 anni fa
- AWS UFFICIALEAggiornata 2 anni fa
@rePost-User-4555259 - Doh! This is the first time I have ever come across #2 (Thank You!) very interesting and #4 can be discarded. I know that the .net core code base recently switched to a more secure attachment mode when connecting to odbc databases, faulting to more secure, while being a breaking change. This info has been helpful.
To resolve this issues 2 and 3, include TRUSTED_CONNECTION = TRUE <-- Kind of hard to do using dms. However, I am betting this is a secretsmanager issue returning a blank username, it's the only logical explanation as far as I can tell. It worked after a clean slate and prior to a secret rotation :/