using windows Authentication in SDAC connection conponent

eprince · Post by **eprince** » Fri 23 Feb 2007 09:48

I am using SDAC connection component in my application

I have a unique scenario where I want to use Windows Authentication mode for accessing SQL server Databases,
But
I don’t want to use the current login username to login.
i.e.: I want to explicitly pass "windows username" and "windows password" for authentication.

Also this "windows username" and "windows password" need not be the same as logged in windows username and password.

Can you suggest me how to build this connection string?

Jackson · Post by **Jackson** » Fri 23 Feb 2007 15:33

There is no possibility to do this with SDAC.

eprince · Post by **eprince** » Sat 24 Feb 2007 08:41

Oh I see.

But sir tell me something,

"Is it possible at all? In any other environment?
Isn't there a work-around for this particular problem?"

Jackson · Post by **Jackson** » Mon 26 Feb 2007 11:26

You can run your application in the security context of another user.
For more information please see description of the CreateProcessAsUser function in MSDN.

eprince · Post by **eprince** » Tue 24 Apr 2007 13:21

Thank you very much for your suggestion sir,

I have handled that situation using Impersonation.

I'm attaching a sample code for Further refrence.

Code: Select all

procedure MSConnection1BeforeConnect(Sender: TObject);

  var
    hToken:Cardinal;

  //Used to impersonate Application user if not same as Windows Logged in User.
  function PerformLogon(const User, Domain, Password: string): Cardinal;
  begin
    if not LogonUser(pChar(User), pChar(Domain), pChar(Password),
      LOGON32_LOGON_INTERACTIVE,
      LOGON32_PROVIDER_DEFAULT,
      Result) then
      RaiseLastOSError;
  end;

begin
  { If Application user is different than the Logged in Windows user
    we need to impersonate database connection in security context of the
    application user. }
  try // Get user token for application user if application user is different than logged in windows user
    if (ApplicationUserName  WindowsUserName) then
    begin
      hToken := PerformLogon(ApplicationUserName, DomainName,Password);
      try //Impersonate before connecting to database
        ImpersonateLoggedOnUser(hToken);
      finally
        CloseHandle(hToken);
      end;
    end; //    if (ApplicationUserName  WindowsUserName) then
  finally
    //
  end;
end;

procedure MSConnection1AfterConnect(Sender: TObject);
begin
  RevertToSelf;
end;

Where MSConnection1 is a SDAC connection component

eprince · Post by **eprince** » Fri 27 Apr 2007 13:33

Sir
I am facing a peculiar problem using the above mentioned approach.

I'll try to describe the problem as I'm not able to reproduce it in a dummy project.

While doing impersonated login I am getting an error while opening one query-
Scenario e.g: ---
Windows Logged on user is 'XYZ' - This user Does not have access to my Database
User to be Impersonated is 'ABC' - This user has (Read + Write) Database Access

So I have impersonated 'ABC' and opened a connection (With all required parameters:

Database, Server Name, etc) under the security context of this 'ABC'

Now I am opening all my queries using this connection.

So for opening one particular query I am getting an error as:

"Login Failed for user 'XYZ'
Cannot open 'database' for requested by the login"

where as user 'XYZ' should not be in picture for impersonated connection.

Also this query is common to two different Applications, and it is working fine (Same

scenario) in one application but failing in the second application.

When I debugged I found out that this error was coming from the following function in

OLEDbAccess.pas

Code: Select all

TOLEDBCommand.CheckAndAnalyze(const Status: HRESULT)

Where "Status" comes as -2147467259" for this particular query,
while for other queries it comes as "Zero"

Also this status value comes from -

Code: Select all

ICommand(FICommandText).Execute(nil, RequestInt, FParamsAccessorData.ExecuteParams, 
FRowsAffected, Result)

This "ICommand(FICommandText).Execute()", I guess is a Interface.

Can you kindly help me out with this issue?

If you require any more inputs, please let me know.

Jackson · Post by **Jackson** » Fri 27 Apr 2007 15:22

Do you use TMSQuery.FetchAll = False mode?
Execution of queries in the TMSQuery.FetchAll = False mode blocks the current session. In order to avoid blocking, OLE DB creates an additional session which may cause such problem.

eprince · Post by **eprince** » Wed 02 May 2007 05:36

Thanks a lot sir for solving my problem once again

Actually all my queries have TMSQuery.FetchAll = False
So do i need to make all of them True just in case this problem comes somewhere else.

Do you forsee any other impact?

Jackson · Post by **Jackson** » Thu 03 May 2007 09:16

Yes, you should use the FetchAll = True mode.
You can also have problems with using the connection pooling.