MSSQL server auth with windows authentication but different credentials

[Fabrizio_Brenchio]

Hi, I'm using XData Server and Aurelius with native driver in a local dev environment and it works great: the scenario is Microsoft SQL Server on a PC connected via LAN with SQL Server Authentication, something like this:

 with AureliusConnection do begin
      Params.Values['Server'] := '192.168.0.100';
      Params.Values['Database'] := 'DB';
      Params.Values['UserName'] := 'SYSDBA';
      Params.Values['Password'] := 'manager';
      Params.Values['TrustedConnection'] := 'False';
    end;

as pointed out in the aurelius guide https://doc.tmssoftware.com/biz/aurelius/guide/database.html#mssql-driver-microsoft-sql-server

the TrustedConnection is used to switch between Authentication Modes.

Now, the problem is that in production my customer uses a MSSQL server which uses authentication via Active Directory (no way to have SQL Server Authentication)... and the XData server is installed as a service on a Windows Server machine: I need to use a specific account (different from machine login) and it is possible only to use Windows Authentication mode in order to connect to the DBMS... how can I do that?

If I just use the params with TrustedConnection set to true the UserName and Password are ignored and the PC and the login credential are used...

 with AureliusConnection do begin
      Params.Values['Server'] := 'mssqlservername';
      Params.Values['Database'] := 'dbname';
      Params.Values['UserName'] := 'user';
      Params.Values['Password'] := 'pass';
      Params.Values['TrustedConnection'] := 'True';
    end;

I've also tried passing params in connection string using

Params.Values['OdbcAdvanced'] := 'UID=user;PWD=pass';

but it has no effect.

Any suggestion? Thanks in advance!

[wlandgraf (Wagner Landgraf)]

As far as I know, in SQL Server either you use Trusted Connection or User/Password, not both.

But you can configure your Windows Service running XData to run under a different user instead of the "machine login" (I assume you are referring to some system user that services run under)?

[Fabrizio_Brenchio]

It seems that it's not possible to craft a connection string so that you can open a connection to a MSSQL server using another user's windows credentials.
Either you specify a sql server account; or you just use no credentials at all and the OS passes along the current set being used on the domain - it's the windows way...

I found a workaround that simply "works", maybe not the best one but fast and easy using NSSM NSSM - the Non-Sucking Service Manager and setting an account name:

NSSM is a quick and easy way to turn an exe into a service and configure it with a lot of details. It's not the same as having a real service running on the machine... but not so bad .

Next step will be to convert the XData server, which is a Windows desktop application based on TApplication class, into a real service based on TServiceApplication class, as explained into Hand-on with Delphi vol. 3 in chapter 5.7.

Another way would be to use the runas command from the console window... but have never experimented with it.

[wlandgraf (Wagner Landgraf)]

Ok. Still I believe you can simply tell Windows the user to run the service with, for example:

Set user account for a Windows service

[Fabrizio_Brenchio]

Thanks for suggestion - I need to study Windows details, working mainly with macOS and linux since many years...
Have a nice weekend!