Login failed for user 'SA'.

[ghost]

Hi guys, I come here for help, Thanks for those who will help me in advance! The thing I want to do is to run a sqlserver docker container instance on my host, so that I can connect to it using Azure Data Studio.

Environment:

• Host os: Ubuntu 20.04.3 LTS
• Docker version: 20.10.8, build 3967b7d
• Docker image: mcr.microsoft.com/mssql/server:2019-latest
• Azure Data Studio: Version: 1.32.0 Commit: 4a45ba7cf20dd4129f1a08e5e776dfb33e3d1d1e Date: 2021-08-16T18:00:41.235Z VS Code: 1.56.0 Electron: 12.0.7 Chrome: 89.0.4389.128 Node.js: 14.16.0 V8: 8.9.255.25-electron.0 OS: Linux x64 5.11.0-27-generic

I followed the official tutorial to setup the container, after finishing this, I can connect to sqlserver in my container.

# in my **container**
# I already entered my container
mssql@sql1:/$ /opt/mssql-tools/bin/sqlcmd  -S localhost -U SA -P "<my_password>"
1> select name from sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
test_db                                                                                                                         

(5 rows affected)
1> QUIT
mssql@sql1:/$ 

But this is not what I want, as I said(The thing I want to do is to run a sqlserver docker container instance on my host, so that I can connect to it using Azure Data Studio.), I need to connect to it from my host with Azure Data Studio. To test the connectivity(port mapping or something) between host and container, I decided to install mssql-tool(sqlcmd) on my host and try connecting to the container.

# In my **host**
# I have sqlcmd installed
➜  ~ docker ps
CONTAINER ID   IMAGE                            COMMAND                  CREATED        STATUS        PORTS                                       NAMES
c07fb64eaffd   mcr.microsoft.com/mssql/server   "/opt/mssql/bin/perm…"   17 hours ago   Up 17 hours   0.0.0.0:1433->1433/tcp, :::1433->1433/tcp   sql1
➜  ~ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "<my_password>"
1> select name from sys.databases;
2> go
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
test_db                                                                                                                         

(5 rows affected)
1> QUIT
➜  ~ 

You can see that I successfully connect to it with sqlcmd from host, Then I think everything is fine, I can try connecting to it using Azure Data studio.

1. I launched Azure Data Studio
2. Connections -> Add Connection -> Connection Details -> Connection Type: Microsoft SQL Server -> Server: localhost -> Authentication type: SQL Login -> User name: SA -> Password: my_password -> Database: -> Server group: -> Name(optional): I make it empty
3. click Connect down below Then I get a pop up saying Login failed for user 'SA'. Here is the detailed error information:

   Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'SA'.
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass47_0.<CreateReplaceConnectionContinuation>b__0(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   --- End of stack trace from previous location ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
   --- End of stack trace from previous location ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 312
   --- End of stack trace from previous location ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 557
   ClientConnectionId:b9e24745-75ee-435f-bb39-1149d6d14848
   Error Number:18456,State:1,Class:14

   I checked this issue, but still don't know how to work it out. And I also googled a lot and watched a lot of youtube videos, including the official video tuorial. I felt so confused, it seemed that the lecturer did not do anything special and the connectivity was totally fine.

And there is one thing I am curious about: when I enter the docker container to use systemctl to check the status of the server, I got this: (I know the mssql image does not include systemctl, I enter the container as ROOT and have it installed )

# In the **container**
mssql@sql1:/$ systemctl status mssql-server
mssql-server.service - Microsoft SQL Server Database Engine
    Loaded: loaded (/usr/lib/systemd/system/mssql-server.service, enabled)
    Active: inactive (dead)
mssql@sql1:/$ 

I don't know why the status is inactive... :( Again, Thx for your help and take cate :)

[bjothorl]

I had a login fail too, though the reason was password related:

Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: 172.21.0.1]

error messages when trying to connect. Changed passwords a million times, but in the end I finally found my problem, and solved it:

volumes:
     - "./data-mssql:/var/opt/mssql/data"

was what caused my issue. I'm completely new to docker, but I think maybe it recovered from old data, and therefore also copied an old password. I removed the folder "./data-mssql/" and re-composed, and then I was able to connect properly.

Initially I had set a bad password with:

SA_PASSWORD: "password"

because I didn't realize it needed to be more complex, and I think it persisted through to the next docker images.

Reproduce?

1. start image with

   SA_PASSWORD: "password"

   and

   volumes:
    - "./data-mssql:/var/opt/mssql/data"

2. change SA_PASSWORD to something else.
3. re-compose image and try to log in to the sql server.

[martin-josef]

That was my issue as well, thanks for the tip bjothorl.