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

asked 11 Jan '13, 18:26

Jeff%20Gibson's gravatar image

Jeff Gibson
1.4k284454
accept rate: 21%

edited 11 Jan '13, 19:05

Mark%20Culp's gravatar image

Mark Culp
22.6k9129265

1

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).

(11 Jan '13, 19:13) Mark Culp

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...

permanent link

answered 12 Jan '13, 12:43

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

edited 12 Jan '13, 16:15

2

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.

(15 Jan '13, 14:21) Karim Khamis
Replies hidden

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.

(22 Jan '13, 12:48) Jeff Gibson

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.
(22 Jan '13, 15:18) Volker Barth

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

(23 Jan '13, 03:31) Volker Barth

I think there are more examples in my blog, but here's an excerpt from an MSS 2008 template script; the EXTERNLOGIN is pretty much a prerequisite for connecting to MSS...

CREATE SERVER mss 
   CLASS 'MSSODBC' 
   USING 'DSN=MSSTEST';

CREATE EXTERNLOGIN DBA  
   TO mss  
   REMOTE LOGIN "sa" 
   IDENTIFIED BY 'j68Fje9#fyu489';

CREATE TABLE proxy_customer  
   ( pkey INTEGER NOT NULL PRIMARY KEY,
     data INTEGER NOT NULL )
   AT 'mss.test.dbo.mss_customer';
permanent link

answered 22 Jan '13, 16:36

Breck%20Carter's gravatar image

Breck Carter
27.0k424582831
accept rate: 21%

edited 22 Jan '13, 16:37

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×406
×46

question asked: 11 Jan '13, 18:26

question was seen: 7,136 times

last updated: 23 Jan '13, 03:31