Login failed for user '.'

[JoCodes]

Hi, I'm trying connect to mssql docker container through Visual Studio 2017 Mac ( not VS Code) .I have created a docker with mssql instance up and running . When I ping from the Terminal it responds and can log in too. But when I try to connect through the Dotnet application it throws error ,

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

While I can't use Integrated Authentication or Trusted Connection parameter since the Application uses AspBoilerPlate ( ABP ) framework doesn't support this. Is there any work around I can use to overcome this? Thanks in advance.

[twright-msft]

Are you running the container locally on your Mac? Is this the mssql-server-linux image or the mssql-server-windows image?

[JoCodes]

I'm new to Docker. Using locally on my macbook and its the linux image.

[twright-msft]

OK, that's pretty surprising then that you are getting this particular error message then because SQL Server on Linux out of the box is not configured to be Windows integrated auth only for obvious reasons. Did you make a configuration change to Windows integrated auth or did it just start out like that?

[JoCodes]

I have not changed anything but was not working . Later I tried with Integrated Auth without any success either.

[twright-msft]

Can you please try to login using the sa login from your app?

[JoCodes]

Im trying to connect through my app only using the connection string "Default": "Server=localhost ; Database=ERPDb; User=sa; Password=Technocrat123;" .

[twright-msft]

"User" needs to be "User ID". That's hopefully the problem here.

[JoCodes]

I have used "sa" which is the user id created using the command . While trying to connect through the Terminal its connecting and shows $ mssql -s localhost -p Technocrat123 Connecting to localhost...done But while connecting the app ( with connectionstring ) it fails.

[JoCodes]

This shows ( refer Environment Variable Header ) the admin user id is 'sa' https://hub.docker.com/r/microsoft/mssql-server-linux/

[twright-msft]

@JoCodes Did you change the "User" to "User ID" in your connection string?

It should be this: "Default": "Server=localhost ; Database=ERPDb; User ID=sa; Password=Technocrat123;" .

Not this: "Default": "Server=localhost ; Database=ERPDb; User=sa; Password=Technocrat123;" .

[JoCodes]

Tried but didn't work :-(

[twright-msft]

Do you get a different error now or the same one? The error you had originally would be consistent with the attribute name being wrong in the connection string.

Login failed for user ''.

Since there is no value in the quotes there it seems that is because there was no value in your connection string for User ID because you had it as User.

Now that you have changed it I would expect the error to be different now.

[JoCodes]

Now it shows
Cannot open database \"ERPDb\" requested by the login. The login failed.\nLogin failed for user 'sa'."

[twright-msft]

OK now we are making progress. Is there more to the error message?

[JoCodes]

No , this much . ERPDb is the Migration DB coming along with the applicaion ( ABP )

[oriolgut]

I have the same problem. I'm working on a windows machine but using linux in docker as so i'm running a mssql-server-linux but my application can't login via user id=sa help?

[gsainagendraprasad]

Just paste this below content in to docker-compose.yml (use notepad++ to create this yml file)

db: image: mysql environment: MYSQL_ROOT_PASSWORD: "" MYSQL_ALLOW_EMPTY_PASSWORD: "yes" ports:

• "3306:3306"

Then got to cmd window, go to the folder which has this yml file and run command “docker-compose up”

You can use mysql workbench to connect to it.

[gsainagendraprasad]

HERE IS HOW YOU DO IT FOR SQLSERVER:

Prerequisites : have the sqlserver image and initial db scripts as attached. Having a sqlserver running on local docker container and accessing it using sqlserver management studio. We had and issue , inspite of port being set as 1433:1433 in docker compose file, still container’s HOST is not listening on port 1433.So we had to explicitly run the docker run command with a different port as shown below: Run: docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=Yukon900 -p 1401:1433 -d nameofimage In SQL Server Mgmt Studio: · Server: localhost,1401 · User: sa · Pass: Yukon900 · SQL Server Authentication

Links: https://github.com/twright-msft/mssql-node-docker-demo-app https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker#a-idrequire mentsa-requirements-for-docker Prerequisites: Increase the Docker memory to more than 5GB. Dockerfile: FROM microsoft/mssql-server-linux:latest

Create app directory

RUN mkdir -p /scripts WORKDIR /scripts

Bundle app source

COPY /scripts /scripts

Grant permissions for the import-data script to be executable

RUN chmod +x /scripts/import-data.sh RUN chmod +x /scripts/entrypoint.sh EXPOSE 8080 CMD /bin/bash ./entrypoint.sh Entrypoint.sh

start SQL Server, start the script to create the DB and import the data

/scripts/import-data.sh & /opt/mssql/bin/sqlservr Import-data.sh

wait for the SQL Server to come up

sleep 90

run the setup script to create the DB and the schema in the DB

/opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "${SA_PASSWORD}" -d master -i setup.sql

import the data from the csv file

/opt/mssql-tools/bin/bcp CIT.dbo.Location in "/scripts/Locations.csv" -c -t ',' -S localhost -U sa -P "${SA_PASSWORD}" Docker compose file: version: '3.1' services: sqlserver: image: "dtr.xformdevpoc.com/302/sqlserver:latest" #${APP_VERSION}" deploy: replicas: 1

resources:

limits:

cpus: '0.75'

memory: 4G

reservations:

cpus: '0.5'

memory: 3.75G

restart_policy: condition: on-failure delay: 5s max_attempts: 3 window: 120s placement: constraints:

• "node.role == worker"
• "node.labels.type == sqlserver" environment: ACCEPT_EULA: "Y" SA_PASSWORD: "${password}" volumes:
• sqlserver-volume:/var/opt/mssql ports:
• "1433:1433" networks:
• Consul_consul volumes: sqlserver-volume: networks: Consul_consul: external: true using command line go the folder that has the dockerfile, then Run: docker build –t . Run: docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=Yukon900 -d nameofimage Command to enter in to Container and run SQL Server 2017: docker exec -it 2f13d19a035f /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P Yukon900 query to execute: 1> USE CIT SELECT * FROM dbo.Location 2> go

[EdMendezNC]

Just came across this error message tonight, not sure if this has been resolved, but for me the issue was since i was using mssqlserver in a linux container, the database name needed to be surrounded by [ ] because the database name was mixed case. It is a Linux thing, that we from the windows world take for granted. so using ERPDb as an example, It should be this: "Default": "Server=localhost ; Database=[ERPDb]; User ID=sa; Password=Technocrat123;" .

[reexmonkey]

This problem can also occur if you attempt to connect to MS SQL Server, when it has not fully started.

On my machine, a timeout of 10s guarantees a successful initialization of the server. Surprisingly, your docker container may be up and running but MS SQL Server may still be catching up and if you happen to call the server, it will throw a garden variety of error messages (e.g. the sa logon problem) depending on the readiness state of the server.

Therefore, to avoid the problem, please do the following:

• Your docker compose file for the usual culprits (published ports, sa_password etc).
• Increase the startup time of the docker container, in order to allow the hosted MS SQL server have enough time to be up and running.

I don't know how to configure such in docker compose but in my case I programmatically called the Docker SDK through the .NET wrapper (Docker Dot Net ) to create and start a container while blocking its usage for 10 seconds after it has been started.

Hope this helps.

PS: The docker_compose Project of Visual Studio is not quite reliable in setting up and starting the docker container that hosts MS SQL Server. After many frustrating trials today, I discovered it was the culprit behind the unsuccessful initialization of the server.

[SulemanMuzaffar]

I dont know if the issue is resolved yet. I was stuck at the same issue for 4 days. Finally found the solution while reading this thread, so i thought i would share with you people for anyone who gets this issue in the future.

I resolved the issue by changing User to UID and Password to Pwd.