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.
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:
It seems still obviously unanswered - but extern logins seem to be the solution...
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';