server to ODBC DSN

i have a DSN created on the SQL Server machine itself. Using my favorite
ODBC sql query tool, i can select the System DSN, specify a login and
password, and type a select statement and it all works. i go into SQL
Server, and want to create a linked server to this same ODBC DSN:
Linked server = "BALLYS"
Provider = "Microsoft OLE DB Provider for ODBC Driver"
Data Source="WC400B Bally CMS Testing"
Security-Be made using this security context:
Remote login= [Login Name]
With Password= [Password is blank]
Then i connect to the SQL Server using QA, and try to run my query (that
works in my favorite generic ODBC query tool):
SELECT * FROM OPENQUERY(BALLYS, 'select * from cspcm')
And i get an error message. Now, the error message really shouldn't matter.
My question is, why does it not work? Why didn't the linked server work? If
any generic 3rd party tool can login and run queries fine, why isn't SQL
Server?
But if you care, the error message is:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]General error.]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's
SQLSetConnectAttr failed]
[OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver
(32-bit)][DB2/400 SQL]Communication link failure. Comm RC=4 - CWB0999 -
Unexpected error: unexpected return code 4]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
returned 0x80004005: ].
Now obviously the DB2 ODBC driver works fine, since i can use it fine with
ADO in my own programs, as well as any other program that knows how to use
ODBC.
So what is SQL Server doing wrong?
Here are all the settings for my linked server setup:
Provider name: Microsoft OLE DB Provider for ODBC Drivers
Product name: [blank]
Data source: WC400B Bally CMS Testing
Provider string: [blank]
Location: [blank]
Catalog: [blank]
Provider Options:
Dynamic parameters: Unchecked
Nested queries: Unchecked
Level zero only: Unchecked
Allow InProcess: Checked
Non transacted updates: Unchecked
Index as access path: Unchecked
Disallow adhoc accesses: Unchecked
Be made using this security contect:
Remote Login: [The login]
With password: [blank - the password is empty]
Server Options:
Collation compatibile: Unchecked
Data Access: Checked
RPC: Unchecked
RPC Out: Unchecked
Use remote connection: Checked
Collation Name: [blank]
Connection Timeout: 0
Query Timeout: 0
Everything is defaulted, aside from the OLEDB Provider Name, the DSN, the
login, and password.
Which of these defaults are wrong, and are preventing SQL Server from
connecting to a remote ODBC data source?Error when i try various things:
Collation Compatible o o x o x o x o x o x o x o x o x
RPC o o o x x o o x x o o x x o o x x
RPC Out o o o o o x x x x o o o o x x x x
Use Remote Connection o o o o o o o o o x x x x x x x x
Data Access o x x x x x x x x x x x x x x x x
=============================================================================Error 1 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2 2
1. Error 7411: Server "BALLYS" is not configured for DATA ACCESS
2. Previous Error