cannot connect to SQL Server using local 127.0.0.1 address unless the login user has sysadmin role

problem

i am trying to connect to the server using Microsoft ODBC Driver 17 for SQL Server through sqlcmd setting the -S (server) option pointing at 127.0.0.1,1434. i have confirmed this is the correct port.

i cannot use the traditional ., (local), localhost options. it must address the server by its loopback interface IP 127.0.0.1 (see end of post for additional context of why).

TLDR: why can i only connect over the local IP 127.0.0.1 when the login has sysadmin role?

what i have tried

when i connect using a login (local) that has the sysadmin role it works:
sqlcmd -S 127.0.0.1,1434 -U local -P ** -d **
1>


however i am trying to connect through a new login i created that has more restricted access . therefore i do not want to give this new login the sysadmin role and instead it is using the default public role.

when i try to connect the same way with this login i get Login failed for user 'TestLogin'
sqlcmd -S 127.0.0.1,1434 -U TestLogin -P ** -d **;
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'TestLogin '..


i am trying to understand why sysadmin role somehow allows "remote" (all local on the host machine) access but without this role it fails.
root cause

the root cause is that sysadmin role is required to connect over 127.0.0.1. i cannot find any information why this is the case.
Full Context

my end goal is to be able to connect to the server from a containerized process using SQLALchemy/PyODBC. in this configuration i use host.docker.internal to access the host localhost (127.0.0.1).

i am able to establish the connection using the local (with sysadmin role) login from inside the container without issue. however, when using the more restricted login it fails.

so i tried to isolate the issue by attempting from outside the container and just calling sqlcmd directly from powershell on the host machine. as you can see from the original question i am having the sa

TCP 1434 is usually the default port for Dedicated Admin Connections, requiring sysadmin. That explains why the connection succeeds with a sysadmin role member but fails with a minimally privileged login.

If you haven't already done so, enable port 1433 on the 127.0.0.1 loopback IP using SQL Server Configuration Manager. You should then be able to connect without sysadmin role membership. Also, since 1433 is the SQL Server default port, an explicit port specification is not required.

StackExchange Database Administrators Q#320070, answer score: 5