Blog
faq add tag
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.


Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.