How to configure a trusted SQL Server connection (ZEOS Lib 7.1 / MS SQL 2005)

[Mike.Cornflake]

G'day,

I'm using ZEOSLib 7.1.0-alfa.    Lazarus 1.0.8/SVN 40573/win32/fpc 2.6.2.  OS: Win 7, 32 bit.

With TZConnection.Protocol set to either mssql, FreeTDS_MsSQL>=2005 or FreeTDS_MsSQL-2000 I am unable to work out how to connect to a database configured only for "Windows Authentication"

This isn't a DLL issue.  It took me an embarrasingly long time to solve that (nicked nwdblib.dll from SQL Server 2000 SP4 for Protocol=mssql, and added msvcr100.dll along with msdblibr.dll for Protocol=FreeTDS_XXX)

I can make a connection to a test database if I specify a database user/password.  Unfortunately the database I need to connect to is only configured for "Windows Authentication Mode", and simply leaving User/Password blank on TZConnection raises the error "The user is not associated with a trusted SQL Server connection".

Also, this isn't a database issue.  I've confirmed with a variety of tools, including an ADO app written in Delphi a few years back, that my currently logged in user is fully able to connect to the database without passing any configured credentials through. 

Back in Delphi, to get ADO to work, I needed to add "Integrated Security=SSPI;Persist Security Info=False" to the connection string.

I've tried adding the above to the TZConnection.Properties, didn't work.  Makes sense - they're for ADO.   I've tried "Trusted_Connection=Yes".  That didn't work, which also makes sense, that's for ODBC.

So, anyone any idea what I should be passing into TZConnection.Properties?  Or if there is something else I should be trying?

Although I'm currently not doing this in code (IDE only) the equivalent code snippet of what I'm trying is:

Code: [Select]

ZConnection1.HostName := '192.168.100.111';
ZConnection1.Database := 'Pipeline_2013';
ZConnection1.Protocol := 'mssql';  (I'm assuming this is a string and not an enumerated type, which it may be - as I say, I'm playing with IDE only for now)

// ZConnection1.User := 'sa';   // When uncommented works on a test database, sa not configured for the database I NEED to connect to...
// ZConnection1.Password := 'sa1234';

ZConnection1.Connected := True; // <---  Error raised here...

Many thanks

Mike

[taazz]

Take a look on ZDbcDbLib.pas file there is some code to detect 'NTAuth' and 'trusted' which changes the way the connection is made. Be forewarned I do not use ZEOS I just made a quick google search to see what is out there, ad come upon a commit on the ZEOS SF site, it should point you to the right direction though.

[paweld]

Code: [Select]

ZConnection1.Parameters.Add('trusted=yes')
//or
//ZConnection1.Parameters.Add('secure=yes')

[Mike.Cornflake]

G'day,

With protocol set to mssql, adding Trusted=yes (or secure=yes, or NTAuth=yes) to Properties now raises a "NULL DBPROCESS pointer encountered" exception (which isn't freaking me, I can chase down exceptions).

With Protocol=FreeTDS_XXXX I'm still getting the "The user is not associated with a trusted SQL Server connection" error.  The code in ZDbcDbLib is very specific to mssql.  But you've given me a few clues, I'll see if there's similar code buried somewhere else.

I'll let you know how I get on.

Cheers

Mike

[Mike.Cornflake]

What a miserable night :-)

Unable to resolve the NULL DBPROCESS pointer issue.   I'm assuming either a bug in Zeoslib (and I've repeated this post on their forum), an wierdly invalid database configuration, or an incorrect DLL version. 

Unable to get FreeTDS to connect if Windows Authentication is used.   There's something about FreeTDS I'm just not getting my head around.  Why don't they distribute compiled DLL's on their site?   Windows Authentication is supported by FreeTDS > 7.0, but the DLLs I have have no version support info compiled in, so I've absolutely no clue which version of FreeTDS I'm using.  My DLLs are from ZeosLib/sourceforge, and all I can find is a compile date (Jun 2012).

[BigChimp]

There's a fairly recent freetds dll available in the FPC contribs directory as a courtesy to sqldb mssqlconnection users.
This page has a link to it:
http://wiki.lazarus.freepascal.org/mssqlconn#Installation

You can have a look at the original ms sql thread on the forum to see what FreeTDS version it is, but I think it's either the newest, 0.91 or 0.90, with some patches.

[Mike.Cornflake]

Thanks, I'll give that a look.  I had found those dll's, but assumed that the difference in filename was deliberate (zeoslib looks for msdblibr.dll, sqlcon looks for dblib.dll) and so they couldn't be switched out one for another.  I'll give it a go though. 

On that note, I ploughed through the mssqlconn code yesterday, looking to see how they handled Windows Authentication.  mssqlconn uses $DEFINES to switch between either the microsoft dll or the freetds dll.  Regardless of which define you have selected, the setsecure call does nothing and is commented something along the lines of //freetds does not support this.   What I did find interesting was the constants for SETSECURE in each package contained different values.   Constants are named slightly differently in each package though, so I may not have been comparing correct values. (and sure, I tried different values in the ZEOSLib routine, still the NULL DBACCESS error)

Now I've ploughed through the FreeTDS code of each package there was nothing obviously different about each implementation.  I'll rename the dlls and plug them in.  See how that goes.

FreeTDS documentation is pretty thin, but the good news should be that their implementation of Windows Authentication should have meant that code changes are not required in any package.  Usually (for ODBC, ADO and the native MS connection 'ntwdblib.dll') you pass through a blank username and password, and set some value in the connection string (ie trusted=yes).  FreeTDS documentation implies it doesn't work like that.  You still need to supply a username/password.  If the username is of the form DOMAIN\Username, then Windows Authentication is used, if the username doesn't have a '\' in it, then database authentication is used.   Problem is, I can't connect using DOMAIN\Username either...

Life they say, is a learning curve :-)

[BigChimp]

I seem to remember documenting this...

Ahh: have a look here, this should help:
http://www.freepascal.org/docs-html/fcl/mssqlconn/tmssqlconnection.password.html

[Mike.Cornflake]

Apologies for the noise. Everyone else can ignore this - this is reminder to myself :-)

Back in 2013 I couldn't get this working in either SQLDB or ZEOSDB - as I said, setsecure in SQLDB was an empty function with a comment saying this wasn't implemented.  I've just rechecked the source code, and I note that setsecure is now populated.  You need to ensure the ntwdblib define is set.

Code: [Select]

unit dblib.pp
function dbsetlsecure(login:PLOGINREC):RETCODE;
begin
  Result:=dbsetlname(login, nil, DBSETSECURE);
end; 
So essentially - I've now got to find time to test this :-)

[BigChimp]

Ehrm... or you just leave username and password blank as I posted above? However, whatever floats your boat & please report (preferably) success or failure...

[Mike.Cornflake]

I seem to remember documenting this...

Ahh: have a look here, this should help:
http://www.freepascal.org/docs-html/fcl/mssqlconn/tmssqlconnection.password.html

Ehrm... or you just leave username and password blank as I posted above? However, whatever floats your boat & please report (preferably) success or failure...

Well, at the time this was a ZEOSlib question :-)   

No excuse, I should have replied.

In 2013, I simply couldn't get Windows Authentication working, under either ZEOSLib or SQLDB, using any combination (and you'll see immediately before your post (also in the very first post) I had already confirmed blank username/password wasn't working for me)

I can now confirm it's working just fine under Lazarus 1.2.4/fpc 2.6.4/SQLDB (using dlls swiped from LazSQLx (thanks @flameron) as ftp appears to be blocked from my current location), simply by leaving Username/Password blank.  I'm having another (minor) issue in that hostname only works if I put a IP address in there, not the actual hostname (even for 127.0.0.1), but that's by the by (my PC name has a combination of uppercase/lower characters and a hyphen, that might be causing a problem).  Windows Authentication under SQLDB is working.

UPDATE: And it's working just fine for ZEOSLib 7.2.0-alfa in entirely the same way - leave username/password blank.  (Protocol = mssql, add trusted=yes to ZConnection.Properties)  Hostname is working perfectly well, no matter how I enter it (uppercase, lowercase, localhost, 127.0.0.1)...