How to configure a trusted SQL Server connection

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

mike.cornflake
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 15.07.2013, 01:28

How to configure a trusted SQL Server connection

Postby mike.cornflake » 15.07.2013, 01:37

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.

Someone over on the Lazarus forum suggested I try added "trusted=yes" to the Connection Properties. When I do that I get a "NULL DBPROCESS pointer encountered". It was a great idea though as it got me tracing through the code, and finally got me here.

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 all

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';

ZConnection.Properties.Add('trusted=yes');

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


Many thanks

Mike

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Postby EgonHugeist » 16.07.2013, 13:21

mike.cornflake,

i must admit i was running into the same issue. There was a difference to set the lsecure Boolen in ZPlainDblibDriver.pas.

Check:

Code: Select all

function TZFreeTDSBasePlainDriver.dbSetLSecure(Login: PLOGINREC): RETCODE;
begin
  Result := DBLibAPI.dbsetlname(Login, nil, DBVariables.dbSetLoginRec[Z_SETSECURE]);
//  Result := FreeTDSAPI.dbsetlbool(Login, 1, Self.DBVariables.dbSetLoginRec[Z_SETSECURE]);
end;


If you find my mistake than please attach a patch..
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

mike.cornflake
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 15.07.2013, 01:28

Re: How to configure a trusted SQL Server connection

Postby mike.cornflake » 12.09.2014, 22:46

Honestly no idea what's changed, (well, I'm now using 7.2.0-alfa). I revisited this topic recently, and I'm happy to report that everything now works as expected.

Many thanks :-)

Mike

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1739
Joined: 31.03.2011, 22:38

Re: How to configure a trusted SQL Server connection

Postby EgonHugeist » 12.09.2014, 23:11

Pretty nice you share your experiences, not normal as usual. Thank you
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

Alecarrka
Fresh Boarder
Fresh Boarder
Posts: 4
Joined: 03.10.2016, 23:08

How to configure a trusted SQL Server connection

Postby Alecarrka » 18.10.2016, 15:04

I would like to know if I can insert data from csv file to a database table in SQL Server?

Thanks.


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest