question

mgoblue0970 avatar image
mgoblue0970 asked

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-serverlogin
10 |1200

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

mgoblue0970 avatar image
mgoblue0970 answered

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 comment
10 |1200

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

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

0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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


10 |1200

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

mgoblue0970 avatar image
mgoblue0970 answered

@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 comments
10 |1200

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

OK what error is logged in the SQL log then?

0 Likes 0 ·

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 Likes 0 ·

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 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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