Getting "Server is configured for Integrated authentication only" Login Failures when "Mixed Mode Authentication" is Cleary Enabled

Question

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:
![](/media/postImages/original/IMraKG6vPCSL6F56QquzhQag)

Allow Remote Connections:
![Enter image description here](/media/postImages/original/IM8_dUrpDnST25ixJjY8Ev5g)

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,
![Enter image description here](/media/postImages/original/IMlsvv0gKZT_6J2Vn83GzbpQ)

Accepted Answer

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.

Answer

"An attempt to login using SQL authentication failed. Server is configured for Windows authentication only." can be returned in the following situations.

1. 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;
2. 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/
2. 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;
3. 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.

Getting "Server is configured for Integrated authentication only" Login Failures when "Mixed Mode Authentication" is Cleary Enabled

Relevant content