Debian 7.1 and CentOS 6.4

Can you please specify UID and PWD in the connection string on Debian, like this:

CREATE TABLE connect_table (
id int(10) NOT NULL
) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=MSSQLTestServer;UID=sa;PWD=sapwd' table_type=odbc block_size=10 tabname='connect_table';

Note, ConnectSE supports so called discovery, so in many cases it's ok not to specify table structure,
it should be detected automatically. Also, if the local and the remote table names are the same,
then no needs to specify the tabname option. This is the minimum possible create statetent:

CREATE TABLE connect_table ENGINE=CONNECT CONNECTION='DSN=MSSQLTestServer;UID=sa;PWD=passwd' table_type=odbc;

I will check with the native MS SQL driver for Linux and report back separately.

Btw, did you reinstall unixODBC to the version 2.3.0, which the MS driver requires ?
CentOS-6.4 seems to have unixODBC-2.2.14.

Yes i did custom compile unixODBC 2.3.0 as per instruction on the microsoft site and I was successfully able to do integrated authentication from the command line (on centos).

I will try again with the UID and PWD soon.

So that worked!!! I guess all i was missing was the credentials in the table definition.
Was that in the documentation?

Eric, excellent. Good to know. Olivier, please consider documenting this detail.

Eric, can you please clarify why you're trying to use the Microsoft native driver on the CentOS box,
instead of the FreeTDS driver. The latter is a part of the CentOS distribution, thus consistently works
with unixODBC and the other software involved (no needs to do custom compule etc).

Is there anything in the MS driver that FreeTDS does not support, or are you trying it
mostly out of curiosity?

I am mostly familiar with Windows ODBC (the original product) but unixODBC has probably the same features:

ODBC connections can be specified in two ways:
1) Specifying the driver to use (via DRIVER=... in the connection string)
2) Specifying a data source (via DSN=... in the connection string)
The first way is more complicated because all parameters required for the connection must be specified in the connection string.
When using the second way, ODBC uses the parameters defined for the data source and you have to add only the ones not yet defined in the data source description.
For unixODBC, the data source is defined as an entry in the odbc.ini configuration file. It is quite possible to define several data sources using the same driver. For instance:

[MSSQLTestServer]
Driver = ODBC Driver 11 for SQL Server
Server = 192.168.1.20
Port = 1433

[MyMSSQL]
Driver = ODBC Driver 11 for SQL Server
Server = 192.168.1.20
Port = 1433
UID = sa
PWD = sapwd

Doing so you should be able to create your table just saying:

CREATE TABLE connect_table ENGINE=CONNECT table_type=odbc CONNECTION='DSN=MyMSSQL';

The equivalent works fine on Windows, I did not yet test it on Linux (I am using ubuntu)

Note that the parameters required for a connection depend on the ODBC connector you use. They cannot be described in the CONNECT documentation and you should refer to the used connector documentation to know what they are.

Alex,
I was just troubleshooting and was just trying to see if the FreeTDS was the issue but it was not.
I don't even normally run CentOS i mostly use debian but the microsoft site only had rpms for their driver.

Olivier,
according to the FreeTDS documentation:
http://freetds.schemamania.org/userguide/odbcconnattr.htm
UID and PWD can only be on the connection string,
and cannot be used in odbc.ini in the DSN configuration.

It would be nice to have a new manual section with tips how to use various ODBC drivers.

Eric,

Have you solved all the problems you had? Can we close the issue?

Thanks for fast feedback!

Yes this problem is solved now. Thank you for the help.

However I am probably going to open up some other tickets because I am
having other issues. Like my where clause not being passed to SQL Server or
every time i do a query to SQL Server the first thing it does is a select
count which takes a long time on the sql server side and I am getting
some random crashes when i do a select * from table ..

On Wed, Oct 23, 2013 at 11:31 PM, Alexander Barkov (JIRA) <

–
Eric H.
eric.hardway@gmail.com

To access a table via ODBC, CONNECT rephrases the original query and send it to the
data source. The issue is that the entered query if firstly parsed by MariaDB and
therefore must obey the MySQL syntax, while the rephrased query must obey the data
source syntax.

Because both are using SQL, this is generally not a problem. However, because some
details of the syntax can differ, for instance the name of scalar functions, the
rephrased query just ask the used columns values. For instance:

select upper(`first name`), `Salary` * 0.95 from `Employee`;

could be rephrased as:

SELECT "first name", "Salary" FROM "Employee"

The scalar function and expression will be locally calculated by MariaDB. See below
for similar restrictions regarding the WHERE clause.

Where clause not being passed to the data source:
-------------------------------------------------
There can be two causes:
1 - To be able to retrieve the where clause, the storage engine function cond_push
must be called by MariaDB. Since MariaDB 5.5 the engine condition pushdown is OFF by default.
It is therefore necessary to set it ON, for instance by:

set optimizer_switch='engine_condition_pushdown=on';

Or starting mysqld with this parameter set to ON, for instance:

mysqld --console --engine_condition_pushdown=on

Note 1: specifying -console is important to have some error messages from CONNECT printed
because MariaDB does not always retrieve them.

Note 2: since MariaDB 10.0.4, the CONDITION_PUSHDOWN argument is no more accepted.
However, it is no more needed because CONNECT uses condition pushdown unconditionally.

2 - The technique used above (getting only column values) obviously cannot be used here because
the where clause must be executed by the data source. This is why CONNECT extracts only the
"compatible" part of query WHERE clauses and add it to the ODBC query. Currently, clauses
containing scalar functions, expressions or subselect are not passed to the data source.

select Count from table
--------------------------
This was done to answer the info internal engine function. However, this info is not really
important and returning a fixed guess is enough. Therefore, this will be suppressed in the
next version of CONNECT.

Random crashes
--------------
This must be treated by bug reports giving all details allowing to trace the cause of them.

New version
-----------
The new version of CONNECT (in MariaDB 10.0.5) will have many new features addressing in
particular most of the issues listed above.

Eric, I'm closing this ticket.
Please file separate tickets for the remaining problems.
Thanks.