SQL Server fails login with password having semicolon

[ACarrera1971]

Hi, 

It looks like that SQL Server is not able to login when the password used contains a semicolon. I was able to create/update a user to the server defining the password with semicolon in it, however I am unable to login using such user, the system responds "Login failed for user XXXX.  Error 18456".  It is odd because I am able to set such password to the user. 

Recently I have been working on a development to properly process semicolon as part of the password in the connection strings but now I found this issue. I wonder if I need to change some configuration in the server in order to resolve this error, could you help please?  Maybe it is associated with the password policy? 

Thanks in advance

[#2]

Hi@ACarrera1971 -- What is the state code associated with the Error 18456?  Also, can you run the query below to ensure that mixed-mode authentication is enabled on the instance?  Take care.

SELECT CASE 
WHEN SERVERPROPERTY('isIntegratedSecurityOnly') = 1 THEN 'Windows-only authentication'
WHEN SERVERPROPERTY('isIntegratedSecurityOnly') = 0 THEN 'Mixed-mode authentication'
END AS AuthMode

[#3 · Accepted solution]

Hi @ACarrera1971 

It can help if you provide us with the queries you run, because the mind reading ability of some of us does not work well. When you have an issue, then first of all you should describe what you did and as possible provide the tool to reproduce the case.

In general BY DEFAULT there is no issue in using semicolon in password. You can use any UNICODE character as well and not just English - Note that "can" DONT NOT mean "should"!

USE master
GO
CREATE LOGIN [RonenרונןWithsemicolon;] WITH PASSWORD = '[PassWithsemicolon;AndHebrewעברית]';
GO

The above code uses semicolon and Hebrew characters and it run without any issue.

Note! It is highly recommend NOT to use special characters in the LOGIN name, but for password it is a good idea to do so, with that said using non-English characters is not recommended if you cannot confirm that you will have option to use such chars anyway and everywhere. 

[#4]

having semicolon

---

@ACarrera1971 , if you are using ODBC then it's a known issue. The semicolon is the parameter separator and if its used in user name or passwrod it causes a login error.

There are some more characters, see

KB484846: Special character restrictions of username and password in ODBC connection for Diamond and...

Olaf

[#5]

Hi @bake13 

Thanks for your answer. The authentication mode of the server I was testing on is "Mixed authentication mode", in fact I am trying to login using a SQL Server user, do you think it plays a role with this issue? 

Really I am creating the login and updating the password using the SQL Server Management Studio. 

I have been also trying to login using the SQL Management Studio and the error code 18456 is returned in the popup, I assumed it was the regular incorrect password. 

[#6]

Thanks for your answer,
Really I was not using any query, I was using the SQL Management Studio for carrying out the update/create of the login with semicolon in the password and also to login (although I tested using the ADO after having resolved the issue with semicolon in he connection string).
I fully agree with you that having semicolon in the password should not be a problem at all and this is the reason I wonder if the problem I am experiencing is associated with a special kind of configuration in the server.
I will try using the direct SQL sentences to create the login. Good to know you did not find any issue with such password.

[#7]

Hi Olaf,
Thanks for your reply, yes I used ODBC and ADO connections strings and, I think, I resolved the issues at this point. My problem is associated with the server that appears to deny passwords in this way.

[#8]

Thanks everybody for your answers to this simple question.
I can confirm that semicolon in the password actually worked as expected, I have been able to succeed on this after using direct scripts to set the pasword with semicolon.
I am now suspecting that the use of the SQL Server Management made me to set a wrong password in every attempt provoking the error.
Thanks again to everyone for your useful suggestions and comments.

[#9]

Hi @ACarrera1971 

There is no issue using SSMS with such password! Remember my sample above?

Here I can use the SSMS GUI to connect the server using this complex LOGIN and password

Your conclusion is wrong

Note! When using SSMS you do not add quotation marks or parentheses to the password or username. The software already adds these automatically when sending the query to the server. so if you use [password] instead of password then you will get an error message.

[#10]

Hi Ronen,
I did not explain myself, the fact that I use SSMS made me go wrong because I did not check properly the password (after several tries in fact) so I wrongly concluded the fact that semicolon was not allowed as password. SSMS is ok but the fact it did not display the password (as it must do).
I can confirm that SSMS worked well at this point, it was entirely my fault, thanks for your time.

[#11]

You are most welcome and I am glad to hear that all was solved 🙂