How can i create a remote server connection to mysql using SQL Anywhere 16

[former_SQLA_member1694931]

I am migrating our database from version 9 to version 16.

It seems there is something wrong with the authentication to the remote servers from SQL Anywhere 16.

I created a test case of creating a brand new Remote Server on both platforms ( v9 and v16 ).

On v9, each time I attempted the connection, I got an appropriate message from the MySQL ODBC driver about invalid login, until I supplied the correct Remote MySQL login and password.

However, on v16, whether I did or did NOT specify the remote SQL authentication, it reported the same error that 'dbo@....' failed to connect. The Remote login information is being ignored.

I have two sets of screen shots to demonstrate this - I hope I can attach them to my question!

[former_SQLA_member1694931]

Here are my testing results - I don't have privileges to upload screen shots:

============================================================================================= SQL Any 9 - No external Login - notice the user id is DBA@ below

[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'test 192.168.1.92 2013-12-05 20:52:53.933': [MySQL][ODBC 5.1 Driver]Access denied for user 'DBA'@'rrcs-50-74-220-82.nyc.biz.rr.com' (using password: YES) SQLCODE: -656 SQLSTATE: HY000 SQL Statement: DROP SERVER "test 192.168.1.92 2013-12-05 20:52:53.933"

============================================================================================== SQL Any 9 - external login - Login name set to 'us6678_sav03', password set to 'bogus'

[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'test 192.168.1.92 2013-12-05 20:53:25.326': [MySQL][ODBC 5.1 Driver]Access denied for user 'us6678_sav03'@'rrcs-50-74-220-82.nyc.biz.rr.com' (using password: YES) SQLCODE: -656 SQLSTATE: HY000 SQL Statement: DROP SERVER "test 192.168.1.92 2013-12-05 20:53:25.326"

====================================================================================== SQL Any 9 - external login - Login name set to 'us6678_sav03', password set correctly...

Connection Successful

======================================================================================== SQL Any 16 - no external login - notice the login below is 'dbo@...'

[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'test 192.168.1.92 2013-12-05 20:56:51.167': [MySQL][ODBC 5.1 Driver]Access denied for user 'dbo'@'rrcs-50-74-220-82.nyc.biz.rr.com' (using password: YES) SQLCODE: -656 SQLSTATE: HY000 SQL Statement: DROP SERVER "test 192.168.1.92 2013-12-05 20:56:51.167"

======================================================================================== SQL Any 16 - external login - with correct credentials as in step #3 v9 above:

Error message is the same as above:

[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'test 192.168.1.92 2013-12-05 20:58:02.313': [MySQL][ODBC 5.1 Driver]Access denied for user 'dbo'@'rrcs-50-74-220-82.nyc.biz.rr.com' (using password: YES) SQLCODE: -656 SQLSTATE: HY000 SQL Statement: DROP SERVER "test 192.168.1.92 2013-12-05 20:58:02.313"

[MarkCulp]

Please show us the SQL code that you are using?

[former_SQLA_member1694931]

I am using Sybase Central in both sets of tests

[former_SQLA_member1694931]

Absolutely weird ...

I had a 'light bulb' go off on the way to the bus tonight...

I decided to perform the same test on a BRAND NEW SQL Anywhere 16 database (not the one I migrated from SQL Anywhere 9).

Although it doesn't make any sense, creating the the remote connection to MySQL worked on the brand new database from Sybase Central.

So - What I'm going to do now is to script out the DDL for the new database, and then run those commands on the converted database.

Let's see where that leads us ...

Stay tuned!

[former_SQLA_member1694931]

And for the final mystery of the night .. and it's all coming together ...

Below is the DDL for the remote connections scripted out from the new database, which I ran on the converted database, and needless to say, the 'test connection' FAILS in the converted database with the same error message as I saw in Sybase Central (not a surprise)...

And just to add 'insult to injury', this problem is NOT limited to MySQL connections - it's even causing failure on SQL Anyhwere connections. The reason I demonstrated with MySQL is because the error messages returned from the MySQL driver are more detailed than the SQL Anywhere driver, which just says 'invalid user'....

So, the question is what setting in the converted database is causing this failure, and seemingly logging in to the remote as 'dbo' rather than the external login defined (as per the error message I posted from the MySQL driver...) ?

---

-- Create remote servers

CREATE SERVER "test" CLASS 'mysqlodbc' USING 'DSN=savweb-zenutech' go

CREATE SERVER "gtcwin32" CLASS 'saodbc' USING 'dsn=jobacct' go

CREATE EXTERNLOGIN "DBA" TO "test" REMOTE LOGIN "us6678_sav03" IDENTIFIED BY ENCRYPTED 'xffxffxffxffxffxffxffxff' go

CREATE EXTERNLOGIN "DBA" TO "gtcwin32" REMOTE LOGIN "dba" IDENTIFIED BY ENCRYPTED 'xffxffxffxffxffxffxffxffxffxffxffxffxff' go

[former_SQLA_member1694931]

Finally, just as a 'proof' ...

I re-opened the brand new database, dropped the two remote server connections, ran the script above, chose 'test connection' on both in Sybase Central, and voila ... both are alive and kicking.... ('connection successful' on both...)

I now humbly pass this to the real SQL Anywhere deep gurus...

[VolkerBarth · Accepted solution]

Just a very wild guess:

The new v16 "extern_login_credentials" option may make a difference here. To cite:

Controls whether remote connections are attempted using the logged in user's extern login credentials or the effective user's extern login credentials.

In your case, it seems v9 and v16 behave differently in the way which user is used to connect remotely.

For the details, Karim has explained this in this FAQ - and note, a brand-new v16 db and a migrated v16 db seem to behave differently by default:

• Why am I getting extern login errors and/or more than one connection to a remote server with SA 16?

[former_SQLA_member1694931 · Accepted solution]

Well .. your 'wild guess' was right on!

The obvious question is this:

Wouldn't logic dictate that the default functionality of a converted database with remote server to retain the ORIGINAL authentication method, to avoid all this?

Also, the documentation you referenced says that the option "takes effect immediately" .. which is not entirely true. I had to disconnect and then reconnect my Sybase Central connection in order to get the updated option. I guess when the docs say that it 'takes effect immediately' it means that the SERVER doesn't have to be restarted.

THANKS FOR YOUR HELP!

[VolkerBarth · Accepted solution]

Well, feel free to accept that answer then:)

FWIW, I can't explain why the upgrade path was chosen that way (I'm just another customer) though Karim has discussed that in his cited answer.

However, I think w.r.t. invoker/definer, the remote access login logic was contrary to the procedure call logic in pre-v16 databases: For procedures, "definer" was the default, and for remote access, invoker was the "default". Therefore I guess the attempt to choose only one default as upgrade path (via ALTER DATABASE UPGRADE...SYSTEM PROCEDURE AS DEFINER has its limitations - possibly one would have needed a further ALTER DATABASE UPGRADE...REMOTE ACCESS AS DEFINER clause with a contrary default:)

[Former Member · Accepted solution]

You make a very good point.

If you take a pre-16 database and upgrade it, you should get the old behavior, and that is what happens so that's good.

If you create a brand new 16 database, you should get the new (and might I add more secure/correct) behavior; and again that is what happens so that's also good.

If you take a 16 database and upgrade it, you should get the same behavior as the pre-upgraded database; and again that is what happens so that's good.

If you take a 16 database and unload/reload it, you should get the same behavior as the pre-unloaded database; and again that is what happens so that's good.

If you take a pre-16 database and unload/reload it, you should get the old behaviour, but that is not what happens so you are correct, there is a bug here. I will open a bug report and we will try to get the problem resolved in a future SP.

[VolkerBarth · Accepted solution]

If you take a pre-16 database and unload/reload it, you should get the old behaviour...

IMHO, that's the generally recommended approach to migrate a pre-16 database to v16, therefore it's certainly good when that path gets fixed:)