Login fails with ampersand in password SQLExecutor.

The & character is a special character in SQL (it's an AND operator) so it appears either FME or the database is interpreting that password as a query/statement and sort of forgetting about the &

I would raise this with Safe support if I were you. @LenaAtSafe handled a similar question (with an @ character and Oracle) a while ago so I'm tagging her, hopefully she can pick this up.

This was the similar issue recently

https://knowledge.safe.com/questions/48622/how-can-i-connect-to-oracle-if-my-password-contain.html

Hi @rrdlpl, I've been able to successfully connect with a password containing "&". Are you able to share the log file or message from the failed attempt? (I had success in 2016.1 and 2017.1, please let me know if the issue is in an earlier version.)

Hi @trentatsafe​,

I just came across this same issue with the embedded connection when the password includes an ampersand. Luckily it does work with the named connection, but I didn't know why it was failing to authenticate until I found this post. Is this still in the works?

FFME(R) 2020.2.0.0 (20201027 - Build 20787 - WIN64)

Looks like this problem is still occuring with FME(R) 2024.0.0.0 (20240304 - Build 24187 - WIN64).  Also, I don’t think the work around using a named connection is possible for a ODBC 3.x DSN.

Hi @philipwhitten 

I tried replicating this with the same build number with an ampersand in the password and it looks like everything is working on my end. 

Would you be able to share the error message you are receiving? 

Crystal 

@crystalatsafe.

The following messages come from a SQL Executor that has failed where the password has an ampersand:

 MULTI_READER(MULTI_READER): Will fail with first member reader failure
Using Multi Reader with keyword `MULTI_READER' to read multiple datasets
Using MultiWriter with keyword `MULTI_DEST' to output data (ID_ATTRIBUTE is `multi_writer_id')
Loaded module 'LogCount_func' from file 'C:\Program Files\FME\plugins/LogCount_func.dll'
FME API version of module 'LogCount_func' matches current internal version (4.0 20240212)
Loaded module 'QueryFactory' from file 'C:\Program Files\FME\plugins/QueryFactory.dll'
FME API version of module 'QueryFactory' matches current internal version (4.0 20240212)
Emptying factory pipeline
Router and Unexpected Input Remover (RoutingFactory): Tested 0 input feature(s), wrote 0 output feature(s): 0 matched merge filters, 0 were routed to output, 0 could not be routed.
Unwanted Router Input Nuker (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
[report].[vw_GISAssessmentLayer]_ODBC2_1 Splitter (TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)
Restoring 1 feature(s) from FME feature store file `C:\Users\whittp01\AppData\Local\Temp\wb-cache--eponNI\Main_Creator -1 3 fo 0 CREATED  0  e9603836747994a8ce85d8b549c7921b9c41af09.ffs'
Creating reader for format: ODBC 3.x
Trying to find a DYNAMIC plugin for reader named `ODBC2'
Loaded module 'ODBC2' from file 'C:\Program Files\FME\plugins/ODBC2.dll'
FME API version of module 'ODBC2' matches current internal version (4.0 20240212)
Performing query against ODBC2 dataset `DAC_Test'
Opening ODBC 3.x Reader
ODBC 3.x Reader: Dataset = DAC_Test. Username = dac-asql-ra-rhp-gis-reader\@dac.nsw.gov.au. Password = *****
Connecting to database...
Error connecting to database
Database Generated Error Information:
Database Error Message: [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Failed to authenticate the user 'dac-asql-ra-rhp-gis-reader@dac.nsw.gov.au' in Active Directory (Authentication option is 'ActiveDirectoryPassword').
Error code 0xCAA20003; state 10
Authentication failed

The following messages come from an ODBC 3.x Reader that uses the same connection parameters:

MULTI_READER(MULTI_READER): Adding ODBC2 Reader with keyword ODBC2_1
Using Multi Reader with keyword `MULTI_READER' to read multiple datasets
Creating reader for format: ODBC 3.x
Trying to find a DYNAMIC plugin for reader named `ODBC2'
Loaded module 'ODBC2' from file 'C:\Program Files\FME\plugins/ODBC2.dll'
FME API version of module 'ODBC2' matches current internal version (4.0 20240212)
Opening ODBC 3.x Reader
ODBC 3.x Reader: Dataset = DAC_Test. Username = dac-asql-ra-rhp-gis-reader\@dac.nsw.gov.au. Password = *****************
Connecting to database...
ODBC 3.x Reader: The DBMS name fetched by ODBC driver is 'Microsoft SQL Server'
ODBC 3.x Reader: The DBMS version fetched by ODBC driver is '12.00.5564'
ODBC 3.x Reader: Database version is '12.0.5564'
ODBC 3.x Reader: Current schema is 'dac-asql-ra-rhp-gis-reader\@dac.nsw.gov.au'

Note that the 5th character in my password is and ampersand and that in the messages for the SQL Executer there are only 5 asterixs for the password - I assume FME has dropped the remaining characters for the password. (i.e. Password = ***** for the SQL Executor Transformer instead of Password = ***************** for the ODBC 3.x Reader).

Hi @philipwhitten 

Just to confirm, are you using the ODBC 3.x format in the SQLExecutor? 

Has it worked with the Microsoft SQL Server Non-Spatial Reader and Microsoft SQL Server Non-Spatial format in the SQLExecutor? 

Hi @crystalatsafe,

Yes - we are using the ODBC 3.x format in the SQL Executor.

We can’t test using the Microsoft SQL Server Non-Spatial either Reader or SQL Executor as the database uses Azure Active Directory (also called Microsoft Entra) for authentification and FME only supports authentification types of “SQL Server” or “Windows”.  The only reason we set up the ODBC 3.x format was because we couldn’t get FME to authenticate for either Microsoft SQL Server Reader or SQL Executor with Azure Active Directory.

Hi @philipwhitten 

It looks like a similar issue was fixed in a newer version of FME (2024.1).

I’ve tested the ODBC connection with an ampersand in the password in 2024.1 using the ODBC 3.x reader and using the ODBC format in the SQL Executor and everything seems to be working well. 

If you want to test this out you can download the beta from our downloads page.

Please let me know how this goes. 

Crystal 

Hi @crystalatsafe,

I have just tried again with FME(R) 2024.1.0.0 (20240628 - Build 24612 - WIN64) and the problem is still occuring.  Should I be trying a newer beta version?  Sorry for taking so long to test - I had lost Administrator permissions to my laptop when my employment contract rolled over so had to wait for them to be reinstated.

I have some additional information.  When you open the “SQL Editor” within the SQL Executor Transformer and press the “Run” button then the authentification is successful.  Similarly, when you use “Populate from SQL Query” to expose attributes within the transformer the authentification is succesful.  The authentification only appears to fail when you run the transformer as whole - and the password when it fails is cropped to the length of the ampersand position.

Hi @philipwhitten 

Thanks for providing that additional information, it was very helpful. I apologize for the delay this took a bit longer to test than expected. 

Our team is considering implementing a more permanent solution for this issue [internal: FMEENGINE-9436], but I do have a workaround for now.

If you are ok with the password being in plaintext, one option would be to create a password attribute with an attribute creator before the SQL Executor. 

You can then use this attribute for the password in the SQL Executor. 

Please let me know if this works for you. 

Hi ​@philipwhitten 

Our team has implemented these changes so you should now be able to use an ampersand in the password for any build newer than or including FME 2024.2 (build 24725).