asked on

sql server Reporing services connection from delphi?

Hello,
i'have question about reporting service reportservices,I'm trying to connect to the reporting web service sql server but an exception is raised, password or user are wrong error 401 unauthorized .
But i'm sure that they are correct this is how i use it :
I have imported a wsdl and i have got reportservice2005.pas.
Why i can't Authentify knowing that i can with browser how to use the methode LogOnUser() ? or THttpRio? to authtify.
this is the procedure i'm using :
procedure TfMenu.Button1Click(Sender: TObject);
const
defURL = 'http://khali-pc:80/ReportServerSQL2016/ReportService2005.asmx';
var
Report : CreateReport;
ReportingService : ReportingService2005Soap;
stream : TFileStream;
tailleBuffer,I : Integer;
UserLog : logonUser;
Rio1 : THttpRio;

begin

Rio1 := THttpRIo.Create(self);
Rio1.HTTPWebNode.UserName := 'SomeDomain\SomeUser';
Rio1.HTTPWebNode.Password := 'xxxxxxx';

ReportingService := GetReportingService2005Soap(false, defURL, nil) ;

/////********** Second way to authenificate*********************// PS: and the first way to athentificate is to include password and user in RIO (THttpRio) above

/// second method to authetify to SSRS
// UserLog := LogonUser.Create;
// UserLog.userName := 'SomeDomain\SomeUser';
// UserLog.password := 'xxxxxx';
// UserLog.authority :='' ;
// ReportingService.LogonUser(UserLog);

// to create report in web service from file .rdl ////////////
Report := CreateReport.Create;
Report.Report := 'MyReport';
Report.Overwrite := true;
Report.Parent := ('/WRP');
Report.FDefinition_Specified := true;
stream := TFileStream.Create('C:\Users\k.khali\Desktop\MyReport.rdl',fmOpenRead or fmShareDenyWrite);
SetLength(report.Fdefinition,stream.Size);
tailleBuffer := stream.Size;

try
stream.Position := 0;
stream.ReadBuffer(report.Fdefinition[0], tailleBuffer*SizeOf(byte));
finally
ReportingService.CreateReport(report);
end;

the last part of code concerning create report but i can't know know if it execute well because of authentication.

Thanks in advance.

khali khali

ASKER

what do you mean?, I've added ssrs topic, and logon information are not secret.
Are you understanding my problem?

Sinisa Vuk

There is a simple explanation on Embarcadero blog topic on why and how to set simple authorization with a soap. This example follow this directions... please try it. Connection/authorization to sql server have nothing with this.
This is a part of your web (soap) service on server side.

khali khali

ASKER

sinisa Vuk i tried the example but Always the error persists, you can see that i test after initialization of THttpRio by the function createReport in diffrence of example that test withWebService.WebServiceAction('0').SqlXml.

this is my code :
var : ReportingService : ReportingService2005Soap
ReportingService := GetReportingService2005Soap(false, defURL, RiO1) ;
ReportingService.CreateReport(report);
knowing that my ReportingService is of type ReportingService2005Soap after import sdl i got ReportingService2005.pas

the difference betewwen me and the example is that instead of the code above he use :
WebService: WebServiceSoap;// in my code is ReportingService2005Soap
WebService:= GetWebServiceSoap(False, '', rio) ;// in my code GetReportingService2005Soap
WebService.WebServiceAction('0').SqlXml; //in my code I test by creating report;

I see that our wsdl's import didn't give the same unit!
the error always 401 unauthorised i think the problem is in authentication not in creating report.
any suggestions?
thanks.

Sinisa Vuk

You did not follow solution....
Try something like :

type
  THTTPSEvent = class
    procedure OnBeforePostEvent(const HTTPReqResp: THTTPReqResp; Data: Pointer; const ASrc: TStream);
  end;

var
  //global unit
  FRIO : THTTPRIO = nil;
...
{ THTTPSEvent }

procedure THTTPSEvent.OnBeforePostEvent(const HTTPReqResp: THTTPReqResp; Data: Pointer;
  const ASrc: TStream);
begin
  if not InternetSetOption(Data, 
               INTERNET_OPTION_USERNAME, 
               PChar(FRIO.HTTPWebNode.UserName), 
               Length(FRIO.HTTPWebNode.UserName)) then
     ShowMessage(SysErrorMessage(GetLastError));

  if not InternetSetOption(Data, 
               INTERNET_OPTION_PASSWORD, 
               PChar(FRIO.HTTPWebNode.Password), 
               Length (FRIO.HTTPWebNode.Password)) then
    ShowMessage(SysErrorMessage(GetLastError));
end;

...
var 
ReportingService  : ReportingService2005Soap;
...
try
    FRIO := THTTPRIO.Create(nil);
    try
       FRIO.HTTPWebNode.UserName := 'username';
       FRIO.HTTPWebNode.Password := 'pwd123';
       FRIO.HTTPWebNode.OnBeforePost := HTTPSEvent.OnBeforePostEvent;
       ReportingService := (FRIO as ReportingService2005Soap);
       ReportingService.CreateReport(report);
...
   finally
      if (ReportingService = nil) and Assigned(FRIO) then FreeAndNil(FRIO);
   end;
except
end;

...

Open in new window

khali khali

ASKER

thanks Sinisa Vuk, you forgot FRIO.URL in your code it's the same thing with using directly GetReportingService2005Soap(false, defURL, RiO1) but i try it as you suggest, it's strange but it does not work (same exception)!, The exception raises at THTTPReqResp.Receive function in the handler error:
{ Handle error }

  if HttpQueryInfo(Context, HTTP_QUERY_STATUS_CODE or HTTP_QUERY_FLAG_NUMBER,
    @Status, Len, Index) and (Status >= 300) and (Status <> 500) then
  begin
    Index := 0;
    Size := MaxStatusTest;
    SetLength(S, Size);
    if HttpQueryInfo(Context, HTTP_QUERY_STATUS_TEXT, @S[Low(string)], Size, Index) then
    begin
      SetLength(S, Size div sizeof(Char));
      raise ESOAPHTTPException.CreateFmt('%s (%d) - ''%s''', [S, Status, FURL], Status);
    end;
  end;

Open in new window

Sinisa Vuk

Yea, I forgot to set destination url:

...
       FRIO.HTTPWebNode.UserName := 'username';
       FRIO.HTTPWebNode.Password := 'pwd123';
       FRIO.URL := defURL;
       FRIO.HTTPWebNode.OnBeforePost := HTTPSEvent.OnBeforePostEvent;
       ReportingService := (FRIO as ReportingService2005Soap);
       ReportingService.CreateReport(report);

....

Open in new window

khali khali

ASKER

Yes, but it still doesn't work !!read well my previous comment if you can see an issue to this problem :).
Thanks.

Sinisa Vuk

According to this (non-English forum) - there is something with Policies ... Please look at ....

khali khali

ASKER

Thanks sinisa : we have to add

 var 
...
LCResp : ReportService2005.ListChildrenResponse;
    LC : ReportService2005.ListChildren;
.....
    LC:= ReportService2005.ListChildren.Create;
    LC.Item:='/';
    LC.Recursive:=True;
    LCResp := ReportingService.ListChildren(LC);

Open in new window

before ReportingService.CreateReport(report);
i used the first link

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.