Forum Discussion

John_S_Watson's avatar
John_S_Watson
Copper Contributor
Jul 18, 2024

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user

Please excuse me if I break any etiquette rules - this is my first foray into SQL Server land.

 

I'm working with Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Developer Edition.

I cannot connect to a newly installed SQL Server with sqlcmd when using a password. For example, a new user sw:

C:\Users\john>sqlcmd -s jw-lat -e
1> create login sw with password='sw';
2> go
create login sw with password='sw';

1> create user sw for login sw;
2> go
create user sw for login sw;

1> alter server role sysadmin add member [sw];
2> go
alter server role sysadmin add member [sw];

1> exit

C:\Users\john>sqlcmd -s jw-lat,1433 -U sw -P 'sw'
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sw'..

C:\Users\john>

I think that the communication with SQL Server is working (port 1433 is open) because if I stop it I get a different error:

C:\Users\john>
C:\Users\john>sqlcmd -s jw-lat,1433 -U sw -P 'sw'
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sw'..

C:\Users\john>net stop mssqlserver
The SQL Server (MSSQLSERVER) service is stopping.
The SQL Server (MSSQLSERVER) service was stopped successfully.


C:\Users\john>sqlcmd -s jw-lat,1433 -U sw -P 'sw'
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Named Pipes Provider: Could not open a connection to SQL Server [2]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

C:\Users\john>

In the Management Studio I have confirmed that the user sw's status is login enabled, and the server's properties -> security -> server authentication is set to SQL Server and Windows Authentication mode.

I've been Googling around for ages on this. What am I missing? It must be something basic.

Thank you for any insight.

  • John_S_Watson's avatar
    John_S_Watson
    Jul 21, 2024
    I have found this in the SQL Server rror log,
    Login failed for user 'sw'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
    so I've opened a new topic for this.
    • John_S_Watson's avatar
      John_S_Watson
      Copper Contributor
      Jul 21, 2024

      Thankyou for replying. In the Management Studio I have confirmed that the server's properties -> security -> server authentication is set to SQL Server and Windows Authentication mode, which I think is what is meant by "Mixed mode"?
      I have also run the command it suggests,
      EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
      N'Software\Microsoft\MSSQLServer\MSSQLServer',
      N'LoginMode', REG_DWORD, 2;
      which I assume is mean to add a string variable to the key Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer. It doesn't. I created the string variable LoginMode=2 through the registry editor, but still cannot login using a password.
      Thank you, anyone, for any insight.

      • John_S_Watson's avatar
        John_S_Watson
        Copper Contributor
        Jul 21, 2024
        I have found this in the SQL Server rror log,
        Login failed for user 'sw'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
        so I've opened a new topic for this.

Resources