MSSQL trusted connection

[gioloi]

Hi everybody,
I'm trying to connect to a MSSQL 2005 Express Database.
I'm using the ZConnection component with mssql protocol
(I use ZLib (6.6.5-stable) with Lazarus, so I can't use ADO, right?)
Well, when i try to connect I get this error: "User isn't associated to a SQL Server trusted connection".
The SQL server is configured to use Windows authentication and I can't change this behaviour because the server is used by a Web app using windows auth..
I think i need some connection string to put in the Properties setting in the ZConnection, but I don't know...
Can anyone help me?

Thanks

gioloi

[TedDallas]

I am having the same issue with ZLib (6.6.6-stable) with Lazarus on Windows.

Connecting to MS SQL 2005 using SQL authentication works fine but my company's IT policy dictates that we use trusted connections.

Any help would be appreciated. But considering the staleness of this thread, I get the sense that there is no solution other than to start hacking at the Zeos component source.

-Cheers,
Ted

[mdaems]

Ted,

I'm afraid you'll have to do that indeed. But I can already try to point you to the right functions to start with.

I think you'll have to start with the functions opening the connection. Depending on the protocol you use (ADO or mssql) you might give the TZDBLibConnection.Open or TZAdoConnection.Open procedures a look (ZDbcDbLib.pas or ZDbcADO.pas). There the actual connection to the database is set up. I know neither of the protocols and have no idea about how trusted mssql connections should be called, so that might be your first job : find out how the used API must open a trusted connection.
If you find what's the problem and the solution, please post here. I still can merge your solution, even if I can't test it myself when it looks reasonable.

Mark

[TedDallas]

Thank you Mark for pointing me in the right direction. There is no ZeosLib code change necessary to enable trusted MSSQL server connections. Trusted connections are actually supported via a mssql connection (non-ADO) with Lazarus in Windows. Unfortunately, if this is documented anywhere, it is buried deep.

The trick is that you need to add a value to the "Properties" property in your ZConnection component. Setting any one of the following "Properties" values should enable a trusted connection (please note that these values ARE case sensitive):

NTAuth=True
or
trusted=True
or
secure=True

BTW - The code that enables trusted connections (via DBLIB) is in the ZDbcDbLib unit in the TZDBLibConnection.InternalLogin method. I am currently using Lazarus 0.9.28.2 with ZeosLib 7.0.0 alpha and I am connecting to a MSSQL 2005 server. I think this should probably work with the 6.6.6-stable version of ZeosLib.

Now I am going over the Lazarus forum to post this. Happy coding.

-Cheers,
Ted

[mdaems]

Here's a small part from the 'Parameters Documentation' (in the doc directory of the official zeoslib distro)

5
5.1. Connection parameters

* appname=<application name> - The given application name is sent to sqlserver.
* workstation=<workstation name> - The given workstation name is sent to sqlserver
* secure=[yes,no] - This means that SQL Server will use Windows Authentication security.
* trusted=[yes,no] - This means that SQL Server will use Windows Authentication security.
* language=<national language name> - The given language is sent to sqlserver. If language support is installed in the server, error messages are returned in the designated national language.
* fallback=[yes,no] - Enables or disables failover support
* timeout=<seconds> - Is the time-out value, or the number of seconds that DB-Library waits for a login response before timing out. A time-out value of 0 represents an infinite time-out period. The default time-out value is 60 seconds.

Mark

[ikutluay]

hello. Im working on lazarus 1.0.12 and last version of db lib and zeos 7.1.1 rc. Mssql server is installed on our server.

Im setting zconnection and setting parameters and it is connected to server.

then i put a zquery and datasource and grid and set the options. when a set active to the query i see the result rows in dbgrid. but when i press the run button application doesnt connect to server.

it says

"DB Error 10004 :Unable to connect: Sql server is unavailable or does not exist. Unable to connect. Sql server does not exist or network access denied OSError1326 :connection open.connect ()"

But, i can connect my server with trusted mode or sql auth mode with aother tools and there is no problem to work with our sserver from network.

(i have tried both dblib dll and ntwdblib.dll and both exists on app dir and system32 dir.)

So what is the problem ?

EDIT

I have copied exe to server and it has worked (runtime) without a problem. So why is has not connect to server at runtime from my pc.

[Alecarrka]

Hi Guys,

I tried to have a look around here but it seems anybody has got this problem.

I would really appreciate your help to fix this problem.

I have a wp site with some credential DB, USERNAME, PWS and it work fine.

However if I try to create a connection to the db myself, using the same credential the connection doesnt work and i get this error:

Database connection failed: Access denied for user my_userlocalhost using password: YES

I have also tried to create a new db and a new user but it seems have the same problems.

From my php panel I have associated te user to the db grant all privileges.

Any help?

Thanks a lot

[marsupilami]

Hello Alecarrka,

which protocl / Zeos-Driver do you use? The mssql driver is deprecated. Microsoft doesn't support the used ntwdblib.dll since ages. Does that happen with FReeTDS too?

With best regards,

Jan