Monday, June 18, 2012

Connecting Excel 2010 to SQL Server Analysis Service 2012 as a Domain User on a Non-Domain Laptop.

I am using Windows 7 on my laptop and needed to test out dimensional level security using Power Pivot\Excel 2010 that connected to a SQL Server 2012 Analysis Services cube.  The cube was setup to filter on a business unit dimension.   Users could only view measures in which they belonged to that particular business unit.  I needed a way to connect as different domain users to verify the dimensional security in SSAS was properly setup.  I wanted to connect to the client domain, that I was not a member of, with my laptop that belonged to my home office domain.  Essentially, I wanted to connect using am\tom.puch while logged on to my laptop as pla\tpuch.  

I came across an excellent posting by James Kovacs where he connects to SQL Server database engine using SQL Server Management Studio using a different domain user than the one he was logged on to this local machine. Devin Knight also had a nice posting where he used the EffectiveUserName property in the connection string  I wanted to demonstrate how James Kovacs’ technique could work for Power Pivot/Excel 2010 when connecting to a SQL Server Server 2012 Analysis Service Cube.

I opened PowerShell and used the following command.

runas /netonly /user:AM\tom.puch "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.exe"


Excel started up.  I then  confirming the data connection in Excel, I can now see I am connecting with am\tom.puch. 


A quick check of SQL Server Profiler confirms that the correct account is used.