Remote Server to SQL Server 2008 issue from SQLA12

[former_SQLA_member1694871]

I'm running into an issue trying to establish a remote server connection from our consolidated SQL Anywhere 12 database to a SQL Server 2008 R2 database running on a seperate server.

I've installed the client software for SQL Server 2008, and from there, created an ODBC connection that connects from our SQL Anywhere server to the SS2008 database running on its server.

I'm able to create the DSN. I use SQL Server authentication using a login ID and password entered by the user. I'm using the sa user and our password we set on that. I make sure that the default database is set to the correct one, and then select finish. When I test the datasource it works perfectly.

Microsoft SQL Server Native Client Version 10.00.1600

Running connectivity tests...

Attempting connection  
Connection established  
Verifying option settings  
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

After that, I open up SQL Anywhere 12 and select Remote Servers. I name the remote server, I select that I want a Microsoft SQL Server remote server, use the ODBC type of connection, and then type the name of the DSN into the connection information. I step through the rest of the selections making no changes and I click finish. The remote server is created.

Now here's my problem. When I right click on the remote server to test it, I get

Connection Failed  
Unable to connect to server 'servername': [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DBA'.

Here is the detail...

[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'servername': [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'DBA'.
SQLCODE: -656
SQLSTATE: HY000
SQL Statement: SELECT FIRST table_name FROM dbo.sp_remote_tables( 'servername', NULL, NULL, NULL, 1 ) ORDER BY 1

Now my connected user on the SQL Anywhere side is the DBA user, so my assumption is that this problem stems from the fact that it's trying to pass DBA as the user. But the DSN is marked clearly to use SA.

Anybody have any suggestions or additonal information I can add to the connection information field when I'm setting up the remote server? I'll have multiple users connecting to this, and they will all be using their unique user names and passwords, which don't even come close to the ones used in SQL Server 2008.

I'm sure it's just something minimal I'm missing, but I looked through the help file, and didn't find much there when it comes to SQL Server 2008.

TIA!!

Jeff Gibson
Intercept Solutions - Sybase SQL Anywhere OEM Partner
Nashville, TN

[MarkCulp]

Have you tried turning on remote diagnostic tracing by connecting to the SA database and then "set option cis_option = 7" on the connection, and then use Sybase Central to try your remote server. The diagnostics information will show up in the SA server console log - it should show you what it is doing under-the-covers and will likely lead you to a solution. (If not, post the output here and we will try to help).

[VolkerBarth · Accepted solution]

I would create an externlogin for SA user "DBA" to connect under the remote user name "sa". I guess Karim has made some answers here on the relationship of local and remote user names, and possibly the DSN user information is not used at all when using remote data access... (that's just a guess).

At least, we do always create extern logins for RDA.

---

EDIT: I have not found a Karim answer - but Breck has raised the basic question here:

Does remote data access ever make use of a userid and password recorded in a DSN?

It seems still obviously unanswered - but extern logins seem to be the solution...

[Former Member · Accepted solution]

Volker is absolutely correct, the userid and password in the DSN gets overridden when making remote data access connections. You should instead create an externlogin mapping DBA to sa.

[former_SQLA_member1694871 · Accepted solution]

In my tests, I ended up creating a connection ID in SQL Server that matched the user id and password coming from SQL Anywhere. Connection worked at that point. I also had to make sure my permissions were set correctly on the SQL Server side. Just running into an issue accessing the data now, but I'll put that in a new question.

[VolkerBarth · Accepted solution]

I guess that's the default when you don't use an externlogin: The username and password of the current user is used to connect to the remote server, so if you make them match, then all is fine.

The docs explain it a bit unclear IMHO:

By default, SQL Anywhere uses the names and passwords of its clients whenever it connects to a remote server on behalf of those clients.

Note: If you connect as a different local user, you still will have to

• use an externlogin for that local user to map it to the existing remote user or
• make sure that that different local user does also have matching remote credentials.

[VolkerBarth · Accepted solution]

@Karim: FWIW, I have "back-ported" your answer to the cited question...