|
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 |
|
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... 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:
Note: If you connect as a different local user, you still will have to
(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';
|
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).