0

We are using Excel 2010 into our office to produce some reports. We are using powerpivot plug in to connect to SSAS cube. The only problem is when, it is using windows authentication and I want to use SQL Server Service Account instead of windows account. I went to advance connection and tried to type my user id and password but it is greyed out?

Any help would be really appreciated.

I am using this powershell script which opens the Excel file and refresh it. 

``1. Dir "C:\Users\New folder" -Recurse | % {
    $ExcelObject = New-Object -ComObject Excel.Application
    $ExcelObject.Visible = $false
    $ExcelObject.DisplayAlerts = $false
    $WorkBook = $ExcelObject.Workbooks.OpenXML($_.FullName)
    $WorkBook.RefreshAll()
    $WorkBook.Save()
    $WorkBook.Close()
    $ExcelObject.Quit()
}
Justin
  • 393
  • 1
  • 7
  • 21

1 Answers1

0

SSAS only accepts Windows auth. But you can close all Excel windows then start Excel from a command line like this:

runas /netonly /user:REALDOMAIN\YOURDOMAINUSERNAME "c:\path\to\excel.exe"

Then when it connects to Windows auth resources remotely it will connect as the user you mention in the runas command.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Where I put password if I am running as another windows account ? I am refreshing reports using powershell !! – Justin Dec 13 '15 at 16:14
  • It will prompt you for password. But I don't thing scheduled refresh inside Excel is officially supported. You didn't mention that. – GregGalloway Dec 13 '15 at 16:19