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

Question

I am running a SQL Server database on docker using microsoft/mssql-server-linux:2017-latest image.

The database connection string in appsettings.json is defined as:

"ConnectionStrings": {
    "DefaultConnection": "Server=sql.data,1433;MultipleActiveResultSets=true;User Id=SA;Password=Pass@word"
  },

and the database service is defined as the following in the docker-compose.yml:

  sql.data:
    image: microsoft/mssql-server-linux:2017-latest
    environment:
      - ID=SA
      - PASSWORD=Pass@word
      - ACCEPT_EULA=Y
    ports:
      - "1433:1433"

When I try to connect to the database (using Entity Framework) in my application, I get the following error:

SqlException: Login failed for user 'SA'.

To debug it, I login to the docker image, and try to access the database from the docker using the following command:

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "Pass@word"

then I get the following error:

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

I have tried " (double-quotation), ' (single-quotation), and without quoting the username and password, and any combination of both, but still get the same error.

Solution 1

Replace Password with SA_Password in connection string:

  "ConnectionStrings": {
    "DefaultConnection": "Server=sql.data,1433;MultipleActiveResultSets=true;User Id=SA;SA_Password=Pass@word"
  },

Solution 2

Try adding '(quotation mark) around your username and password : 'sa' 'Pass@word'

"ConnectionStrings": {
"DefaultConnection": "Server=sql.data,1433;MultipleActiveResultSets=true;User Id='SA';Password='Pass@word'"

},