Login failed for user on portal and SSMS when username has “@” in it.

  • Article

This is very interesting case which I came across and was not sure why login is failing even though password is correct.

So we had SQL Azure server “abcxyz123.database.windows.net” which has user xyz@abc.com as administrator with password “abc123”

So when we try to login to this server from SSMS or if we click on manage on portal and give below credentials it gives error that login failed for user “xyz”

User Name: xyz@abc.com

Password: abc123

 

Error:

TITLE: Connect to Server

------------------------------

 

Cannot connect to abcxyz123.database.windows.net.

 

------------------------------

ADDITIONAL INFORMATION:

 

Login failed for user ‘xyz'.

This session has been assigned a tracing ID of 'd9c21eee-135c-4c08-86ab-1975bd4c607e'. Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 18456)

 

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server\&EvtSrc=MSSQLServer\&EvtID=18456\&LinkId=20476

 

------------------------------

BUTTONS:

 

OK

 

I looked into all possible logs to find out why it is not taking password even though it is correct but did not find anything interesting.

 

Later I realized that user name that it is using is “xyz” and not xyz@abc.com

 

SO it is breaking username as soon as it finds “@”

 

We worked on this at our and end fixed this issue but with small catch.

 

Now you have to use xyz@abc.com@abcxyz123 (basically username@servername) to connect instead of xyz@abc.com

Now it breaks at last “@” sign and not first “@” sign.

So even now if we use just xyz@abc.com it will break at last “@” and will consider “xyz” as user name so we have to use <xyz@abc.com@abcxyz123> as user name.

I Hope this helps to few such people who has “@” sign in their user name and struggling to understand why login is not happening.

Feel free to drop you questions in comments.