on 2013 Jan 11 6:26 PM
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
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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.
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
@Karim: FWIW, I have "back-ported" your answer to the cited question...
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';
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
69 | |
9 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.