Webservices SSRS from SSIS

• Mike Is Here

  Hall of Fame

  Points: 3348

  More actions

  December 21, 2010 at 2:04 pm

  #232186

  Yes, I did cross post this in the SSRS and Integration Services forum.

  I have a webservice that I am testing. When I use the personal asp.net webserver, the one with the random port everything works fine. It uses my credentials when I pass DefaultCredentials to an ssrs server to run a report.

  When I tried to move it to my local IIS server, the DefualtCredentials UserName is blank and the ID that gets pass to the SSRS server is ASPNET.

  I turned off Annon access and only Integrated Windows Authentication is on.

  In the web.config authentication is set

  <authentication mode="Windows" />

  <identity impersonate="true" />

  The outer webservice calls SSRS webservice and now it works fine in the browser. But when I call the outer webservice from within SSIS, it does not work, when I step through the code the call to the outer webservice occurs but when I make the call to SSRS i get a 401 Authentication error.

  I have valid credentials in the HTTP Manager. I have the package protection level at EncryptSensitiveWithUserKey.

  Again if I call the outer webservice from a personal ASP.net webserver, even the SSIS works fine. I just have to download the wsdl every time because the port changes and then I have to re-setup the webservice task. I am concern when I go to production.

  I know I am getting authenticated properly because

  User.Identity.Name

  returns my login name in the webservice, but when I assign defaultCredentials to the reporting service service it still gives me 401 error. I even tried to impersonate the log in that I was logged in as

  Dim win As System.Security.Principal.WindowsIdentity = CType(User.Identity, System.Security.Principal.WindowsIdentity)

  Dim context As System.Security.Principal.WindowsImpersonationContext = win.Impersonate()

  reportService.Credentials = System.Net.CredentialCache.DefaultCredentials

  The code also works fine from a script task, like the following

  public void Main()

  {

  // TODO: Add your code here

  try

  {

  ProcessExecution1.ProcessExecution p = new ProcessExecution1.ProcessExecution();

  p.Credentials = System.Net.CredentialCache.DefaultCredentials;

  p.CreateFileShareReport("/Subway/Accounting/Notes Receivable/Notes Interest Register"

  ,ProcessExecution1.enuContentType.PDF

  , "Interval;M"

  , @"\\thales\PC Programming Group\AR\TestData\SessionRegisters\EFTotesCode");

  Dts.TaskResult = (int)ScriptResults.Success;

  }

  catch (Exception)

  {

  Dts.TaskResult = (int)ScriptResults.Failure;

  }

  }

  But it appears that the credentials are not being passed from inside the service

  reportService.Credentials = System.Net.CredentialCache.DefaultCredentials

  So the question I have are

  Does anyone have any ideas why the Web Service Task is acting this way?

  OR

  In SSIS how can I dynamically set a web reference address through a variable?

  Or have a common Script component that all other scripts will access?