Help requested for being unable to login with SQL Server account

Question

question

mgoblue0970 asked Feb 19, '19

Help requested for being unable to login with SQL Server account

Hello:

I'm running SQL Sever 14.0.1000.169

I can connect to the database with SSMS and via sqlcmd -S dogzilla.

I ran this command to create a new SQL Server, rather than Windows, account:

create login cli with password = '<password>';

go

When I run this:

sqlcmd -S dogzilla -U cli

and enter the password, I get this error:

Password: Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : SQL Server Network Interfaces: Connection string is not valid [87]. . Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 13 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..

I don't understand, I thought that error was seen when using a Windows account. I'm not I'm trying to use a SQL Server account. Any assistance provided is greatly appreciated.

Thanks!

sql-server login

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2019-02-21T02:35:15Z

mgoblue0970 answered Feb 21, '19

Turns out this is because the SQL Server PolyBase Data Movement service wouldn't start and was hung up in a change pending state. After using SSCM to enable TCP in the network configuration (which is disabled by default) and bouncing the MSSQLSEVER service, SQL Server PolyBase Data Movement service finally started and I could connect with a SQL Server account from the terminal.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Feb 21, 2019 at 09:17 AM

Glad you got sorted. That's certainly a weird reason!

0 ·

Answer 2 · 2019-02-19T08:59:48Z

Kev Riley answered Feb 19, '19

Check the SQL server log - you may see an entry like

Login failed for user 'cli'. 
Reason: An attempt to login using SQL authentication failed. 
Server is configured for Integrated authentication only

Which means that although SQL will let you create SQL accounts, you may not have configured the server to allow them to login. That you will find if you right-click the server in Object Explorer, choose Properties, and go to the Security page

2019-02-19-08h58-02.png (34.6 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2019-02-20T02:37:53Z

mgoblue0970 answered Feb 20, '19

@Kev Riley Thanks for the reply. Yes, I have the radio button for SQL Server and Windows Authentication mode selected. The error still applies unfortunately.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Feb 20, 2019 at 12:05 PM

OK what error is logged in the SQL log then?

0 ·

Kev Riley ♦♦ commented · Feb 20, 2019 at 12:10 PM

Actually the error you are getting is a general network error, meaning that the client could not even find the SQL Server host on the network (for many reasons). Are you trying both sqlcmd approaches from the same client?

The actual reason is Connection string is not valid [87]

0 ·

Kev Riley ♦♦ commented · Feb 20, 2019 at 12:25 PM

I managed to recreate that error message on my local machine by trying to connect to the named MSSQLSERVER instance (which doesn't exist) - do you have any aliases setup in SQL config?

0 ·

question