Pswrd contains # character as starting and ending character

Pravenai · Post by **Pravenai** » Thu Dec 04, 2014 5:46 pm

Hi All,

My password is having # as a starting character and as an ending character (e.g. password is like #abcd$#). I am using ODBC connector stage to connect to a SQL Server database.

I am getting below error:
Invalid property value /Connection/Password (CC_StringProperty::getValue, file CC_StringProperty.cpp, line 109)

I tried following things after reading through IBM website:
1) Enclosed password in double quotes and checked ("#abcd$#")
2) Enclosed password in double quotes and escaped special characters in password string ("\#abcd\$\#")
3) Enclosed password in double quotes and escaped special characters in password string except # strings in the start and in the end ("#abcd\$#")

For all the above cases, I get an error message:
ODBC function "SQLConnect" reported: SQLSTATE = 28000: Native Error Code = 18456: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Legacy Driver][SQL Server]Login failed

Password cannot be changed.

Regards,
PR

chulett · Post by **chulett** » Thu Dec 04, 2014 6:01 pm

The Connectivity Guide for that database should list how you handle that. Well, phooey... this is noted in the Oracle guide but only for column names rather than passwords. I see no such mention in the ODBC guide.

Hopefully someone else has a real answer.

qt_ky · Post by **qt_ky** » Thu Dec 04, 2014 6:44 pm

Assuming you know that DataStage uses the # sign to surround parameters, that's just about the worst possible choice for a password.

It is likely faster to actually have the password changed, or get your own separate ID with a compatible password, than if you were to try to convince support to produce a new enhancement or a patch, unless one is already existing.

You may also want to search the knowledge center on "password naming restrictions." The results are not specifically for the ODBC stages, however, it outlines many special characters that are not permitted for "All user IDs and passwords."

Good luck!

ray.wurlod · Post by **ray.wurlod** » Thu Dec 04, 2014 8:51 pm

When entering the password replace each "#" with "__035__".

chulett · Post by **chulett** » Thu Dec 04, 2014 9:15 pm

So... same as what is noted in the Oracle guide for column names it would seem.

Pravenai · Post by **Pravenai** » Fri Dec 05, 2014 11:26 am

Hi Ray,

I replaced # with __035__ and checked but it is still not working. Our password also contains $, I replaced that too with __036__ but still getting same error message.

I am connecting to a MSSQL database using ODBC connector. For another connection, password contained $ but no hash character. This job ran successfully without any modification to password field. I see this problem arising only when we have a hash character.

Unfortunately, this database is from a third party and we have no control over the password.

Is there any work around?

Regards,
PK

chulett · Post by **chulett** » Fri Dec 05, 2014 11:44 am

Check with your official support provider.

Pravenai · Post by **Pravenai** » Fri Dec 05, 2014 11:51 am

Just a correction to above post.

For the successful connection, we do have # character in the password. Password is something like $#abcd#$. This job connects to MYSQL without any modification required in password field.

It is only when password starts and ends with a #, we face this issue. We do know datastage uses ## to identify parameters, however password is set by some other team and we have no control over that.

Regards,
PR

ray.wurlod · Post by **ray.wurlod** » Fri Dec 05, 2014 1:44 pm

Perhaps you could supply the password as a job parameter, and have the value of the job parameter itself begin and end with "#" characters.