3

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 same issue so it is not related to being in the container.

i provide this extra context because from the container i cannot use the traditional ., (local), localhost options. it must address the server by its loopback interface IP 127.0.0.1 since this is proxied to the container through host.docker.internal

5
  • 3
    Look at the errorlog for SQL Server, there will be a login failure (18456), what's the state and the reason? Commented Nov 22, 2022 at 15:18
  • 2
    Have you tried port 1433 instead of 1434? TCP 1434 is usually the default port for Dedicated Admin Connections, requiring sysadmin.
    – Dan Guzman
    Commented Nov 22, 2022 at 15:52
  • @SeanGallardy where can i view those? Commented Nov 22, 2022 at 16:57
  • 1
    @DanGuzman wow that could be it man! i thought that guy who configured the machine before had just set the regular connection to that port. when i ran the query suggested in this answer (stackoverflow.com/a/19452652/19832958) i saw 1434 and tried it Commented Nov 22, 2022 at 16:59
  • 1
    @DanGuzman that solved it man! i had to use sql server configuration manager and enable the 127.0.0.1 IP address (active + enabled) on port 1433. if you want to post as an answer i can mark it as solved. Commented Nov 22, 2022 at 17:09

1 Answer 1

5

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.

1
  • thank you again dan. this got it all sorted out. hopefully this helps someone else in the future Commented Nov 23, 2022 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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