FreeTDS Development Group

[Alberto Pulvirenti <alberto.pulvirenti AT crowdengineering.com>]

Dear all,

I have some additional info about this problem, so I prefer to give all
details again, hoping in some help from anyone.

So, the problem is the following.
I have developed an executable which tries to log into a SQL Server 2008
instance using dblib provided by freeTDS.
The problem is that I don't manage to log into this server because it has a
logon trigger procedure which fails when I try to access it.

The problem, in this logon trigger is that it has set to OFF the following
three variables:

set ansi_warnings off
set ansi_padding off
set concat_null_yields_null off


and the admin of this instance told me that he will NEVER switch them to
ON. Then I must find a way to access this instance having these setting as
they are currently.

I managed to reproduce the problem with the following procedure in my local
instance of SQL Server 2008 Express. It is enough to add these instructions:

CREATE TRIGGER [filter_test]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @data XML
declare @data01 varchar(100)

SET @data = EVENTDATA();
-->set @data01= @data.value('(/EVENT_INSTANCE/ClientHost)[1]',
'varchar(100)');
END;

I found that the line indicated with the arrow '-->' is the one that causes
the problem. In fact, if I re-define the trigger commenting this line, the
logon trigger is executed properly and I manage to login to the instance,
while if IT don't comment this line, it fails. Then, there must be
something that goes in the XML provided by EVENTDATA() when it is executed
after the login through the db-lib that has something which must be set
properly.

As all of you know, when one compiles the freetds-0.91 library, besides
having the library libsydb.so (which I use), the compilation provides an
executable which is called "tsql".
I tried to access the same DB instance using this executable (then, now and
in the following, when I say "tsql" I am referring to the executable, NOT
the protocol).
Well, if I log into this SQL Server instance using tsql with the suitable
parameters, then the login succeeds perfectly.
Then I assume that I should set something in the login packet in order for
it to be properly configured to allow the logon trigger to process it
correctly when loggin in. Something which surely is properly set in "tsql"
but not when setting up the LOGINREC object which is needed by the dbopen()
function which one invokes when using dblib.

BTW: I also tried to access this SQL Server instance using the other
executable "bsqldb" which is provided in the library and I saw that it
FAILS exactly like my application, then it must have to do with some
non-standard settings which must be put in the login.
I hope that it is not anything which is not managed/feasible using this
library.

Thanks for all help and best regards

Alberto


2012/9/27 Alberto Pulvirenti <alberto.pulvirenti AT crowdengineering.com>

> Hello,
>
> 2012/9/27 James K. Lowden <jklowden AT freetds.org>
>
>> On Tue, 25 Sep 2012 18:15:38 +0200
>> Alberto Pulvirenti <alberto.pulvirenti AT crowdengineering.com> wrote:
>>
>> > > Session properties aren't set by dbopen().  They're set via
>> > > T-SQL's SET command.  Read up on that, and on @@options.  (Nowadays
>> > > there's a function to query session properties individually, not
>> > > just as a bit mask.)
>> >
>> > This last paragraph I didn't fully understand.
>>
>> What may not be clear is that the problem you're seeing doesn't
>> manifest itself when you first open the connection.  It shows up when
>> you issue that particular query.  A simple query like
>>
>>         SELECT 1 as 'one'
>>
>> will not fail.  But one that requires e.g. CONCAT_NULL_YIELDS_NULL
>> will.
>>
>
> This is perfectly clear. Then, something which is sent by my dbopen()
> invocation maybe triggers some query that relies on this.
> I am just wanrdering what can this be, which is not done by the executable
> "tsql".
>
>
>>
>> CONCAT_NULL_YIELDS_NULL is a session property.  Session properties in a
>> T-SQL session are set using the T-SQL SET command, cf.
>> http://msdn.microsoft.com/en-us/library/ms190356.aspx.  Among the many
>> arcane things affected by session properties are the ability to create
>> indexed views and the ability to *use* an existing index of an indexed
>> view, cf. "Requirements for the CREATE INDEX Statement" at
>> http://msdn.microsoft.com/en-us/library/aa933148(v=SQL.80).aspx.
>>
>> > In any case, I suppose that I can issue such a SET command only once
>> > the DBPROCESS is properly initialized after dbopen().
>>
>> English is a little ambiguous here.  You can issue any number of SET
>> commands for the duration of the session, just as any query, after
>> dbopen() and before dbclose().
>>
>
> I am very sorry since maybe I don't manage to express myself clearly
> enough.
> Anyway, that's the point. My application fails when I call dbopen().
> Then, whatever needed to solve this problem I need to apply BEFORE calling
> dbopen(). That's what is driving me crazy.
>
> Thanks,
>
> Alberto
>
>
>>
>> HTH.
>>
>> --jkl
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>
>