Antarctica
Posts: 110
Joined: Tue Jul 08, 2014 9:24 am

Special characters in SQL password

Tue Sep 06, 2016 11:24 am

I have received complaints that some special characters are not working in SQL server passwords. For a quick test, I have produced a user named sonderzeichentest with password °!"§$%&/()=?`*'_:;

I can connect to the sql server via SSMS using that password, but not through Advanced Installer. After changing the password to abcdefg login works in Advanced Installer as well.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Special characters in SQL password

Wed Sep 07, 2016 8:27 am

Hello,

To escape special characters in an ODBC SQL connection string you must enclose them between { and }. So, can you set your password in Advanced Installer project like this:

Code: Select all

{°!"§$%&/()=?`*'_:;}
or, if the password is set into a formatted field like this:

Code: Select all

[\{]°!"§$%&/()=?`*'_:;[\}]
and see if this helps?

Take a look on our "Test SQL Connection" article for more details.

If this is still of no help please send us your AIP (project file) to support at advancedinstaller dot com so we can further investigate its settings.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Antarctica
Posts: 110
Joined: Tue Jul 08, 2014 9:24 am

Re: Special characters in SQL password

Thu Sep 08, 2016 1:58 pm

Hello Daniel,

your two examples are providing the password as literals, but I have made it more like the TestODBCConnection example:

In a custom dialog, our users can set four properties MYAPPSQLSERVER, MYAPPSQLUSER, MYAPPSQLPASSWD, MYAPPSQLDB (I guess the same happens with your default dialog).

In the "Next" button of the custom dialog, the published events are:
  • Set [SqlConnectionString] property to

    Code: Select all

    Driver=[\{]SQL Server[\}];Server=[MYAPPSQLSERVER];Uid=[MYAPPSQLUSER];Pwd=[MYAPPSQLPASSWD]
  • Execute Custom Action TestODBCConnection
  • Display a specific dialog VerifyReadyDialog if SqlConnectionOK = "1"
This is quite similar to the tutorial you linked to. The result is that the password with special chars is not ok.

Antarctica
Posts: 110
Joined: Tue Jul 08, 2014 9:24 am

Re: Special characters in SQL password

Thu Sep 08, 2016 2:17 pm

I have made a new, empty project and added everything required:
ODBC Connection Test.aip
(15.29KiB)Downloaded 760 times
I can log into my server with the same credentials using SSMS; but the connection test in AI fails.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Special characters in SQL password

Fri Sep 09, 2016 10:32 am

Hello,

Please find a fixed copy of your project which should work as expected.
ODBC Connection Test.aip
(15.29KiB)Downloaded 635 times
The [SqlConnectionString] property is set on [Next>] button just like this:

Code: Select all

Driver=[\{]SQL Server[\}];Server=[MYAPPDBSERVER];Uid=[MYAPPDBUSER];Pwd=[\{][MYAPPDBPASSWD][\}];
If you have any questions just let us know.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Antarctica
Posts: 110
Joined: Tue Jul 08, 2014 9:24 am

Re: Special characters in SQL password

Fri Sep 09, 2016 10:58 am

Thank you.

Would be useful to change the description on your help page.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Special characters in SQL password

Tue Sep 13, 2016 8:42 am

You're welcome.

We will try to improve our user guide article too. Thank you for your suggestion.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Antarctica
Posts: 110
Joined: Tue Jul 08, 2014 9:24 am

Re: Special characters in SQL password

Thu Mar 22, 2018 11:43 am

The problem has come up again - the next customer has tried with a SQL password with a semicolon.

Turns out the ODBC Connection test now works, but the execution of the SQL scripts fails.

Image

Image

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Special characters in SQL password

Fri Mar 23, 2018 2:38 pm

Hello,

Most likely this happens because you do not used the same rule (i.e. escape the password string value using the {} chars). Can you please proceed like this:

1. go to "Dialogs" page and select the [Next] button of your SqlConnection dialog
2. add as the first event in the list a published event like this:
  • Property: ESCAPED_PWD_PROP
    Argument: [\{][PASSWORD_PROP][\}]
where the "PASSWORD_PROP is the property whose value you use to configure the password parameter of your SQL connection.

3. go to "SQL Databases" and select your sql connection item
4. in the right pane set the "Authentication -> Use a property to set password -> Property" field like this:

Code: Select all

ESCAPED_PWD_PROP
5. rebuild and test again the setup installation

Let us know if this helped.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: Special characters in SQL password

Mon Dec 20, 2021 9:56 am

Hi,

Starting with Advanced Installer 19.0 we included an improvement and special characters in SQL password are automatically escaped by Advanced Installer on the fly (at install time).

So, when using v19.0 or newer versions there is no need to further use the previous workaround posted in this thread.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”