John K N
# Intro
There was a situation over on [The Heap™ – Consultancy ©®](https://chat.stackexchange.com/transcript/message/51137869#51137869) where McNets was having an issue with a connection string.
I blast off a quick:
> I hope your connection strings password doesn't contain either of these: _ % @ ?
...and McNets was quick to reply with:
> there is an @ in the password
As it turned out the @ (at sign) was breaking the connection string in McNets application.
# Observations
I had previously encountered an issue with the % (percentage) sign in a SQL Server silent installation file. The command I was running was similar to the following:
E:\setup.exe /ACTION=INSTALL /CONFIGURATIONFILE=%~dpn0.ini /IACCEPTSQLSERVERLICENSETERMS /SAPWD="MyPassword%WillFail"
Because the SA password contained a % sign, the installation routine would not display the password in the corresponding dialog. The `%WillFail` was treated as an additional "DOS" parameter.
# Generating Passwords for Database Logins & Systems
Over the years I have generated a lot of passwords and some have failed, while others have worked. It depended on:
- where the password was being used
- which RDBMS system I was accessing
This can be:
- Oracle Connection Strings
- SQL Server Installations
- ODBC Connection Strings
- DOS Commands
- Other Connection Strings
- C#
- Visual Basic
- MySQL
- PostgreSQL
# Universal Password Generator
In order to improve the procedure of generating passwords I started generating long passwords (15+ characters length) with the password generator supplied in KeePass.
My password generator excludes most of the following characters:
``` lang-none
= equals sign
" double quote
' single quote
& ampersand
% percentage
^ caret (accent circumflex)
* asterisk
? question mark
¨ diaeresis (umlaut)
´ forward tick (accent grave)
` back tick (accent acute)
/ forward slash
\ back slash
@ at sign (commercial at)
[ left square bracket
] right square bracket
{ left curly bracket
} right curly bracket
( left round bracket
) right round bracket
; semicolon
_ underscore
# hash
```
# Reasons for Breaking
Some of the reasons for excluding certain characters are only because of certain RDBMS whereas other characters break silent installation files. I will try and list some of the obvious reasons and some other maybe not so obvious reasons why some characters fail when used in passwords.
### ```= equal```
- In an ODBC connection string the equals sign is used to separate parameter from value.
- In SQL Server silent installations an equal sign will break the password.
### ```" double quote```
- Breaks the password for parameter=value settings in ODBC connection strings when the parameter starts with a double quote.
- Breaks connection strings in various programming languages depending on the language used.
### ```' single quote``` (similar to double quote)
- Breaks the password for parameter=value settings in ODBC connection strings when the parameter starts with a single quote.
- Breaks connection strings in various programming languages depending on the language used.
### ```% percentage```
- Fails in SQL Server silent installations when used in a conjunction with a BAT/CMD file that runs the `setup.exe` which supplies the password for a login.
E.g contents of a SETUP.BAT file:
E:\setup.exe /ACTION=INSTALL /CONFIGURATIONFILE=%~dpn0.ini /IACCEPTSQLSERVERLICENSETERMS /SAPWD="MyPassword%WillFail"
### ```@ at sign (commercial at)```
- Breaks Oracle connection strings when using the notation: `user/password@oracle_sid`. The @ in the password is interpreted as the terminator between `password` and `oracle_sid`.
- Breaks some connection strings as observed by McNets.
### ```& ampersand ```
- Used to break ODBC connection strings.
### ```? question mark```
- If I recall correctly, this used to break very old ODBC connections strings (or was it OLE DB), because the question mark was _"asking for a parameter"_.
### ```_ underscore```
- I'm not 100% sure why the underscore eventually made it into my list of undesired characters for database passwords, but I think it was to do with posting an HTTP request with an underscore in the password for a certain web application. (_shout out to **Andriy M.** for asking in the comments_)
### ```# hash```
- Apparently acccording to a comment, the hash can be used as a password in MySQL, but when it is stored in a configuration file, then anything after the hash is interpreted as a comment. (_shout out to **Scott N** for mentioning this in the comments_)
### ```} right curly bracket```
- Will break the connection string in SQL Server ODBC connection as mentioned by OP in the question over on DBA.SE titled [Password character issues for } and ' in SQL connection string](https://dba.stackexchange.com/q/324927/15356).
# Other Characters
The other characters in my collection have made it there due to observations during my 26+ years in information technology and as a database administrator. They are probably not conclusive, but might help others when encountering errors trying to connect to a database. They might also help prevent issues when generating passwords and/or connection strings for various RDBMS and users.
If you have made your own observations, then please feel free to leave a comment and I will add the details to the Blog post. Thanks.