none
excel, msmdpump.dll and double authentication on ad RRS feed

  • Question

  • Hi,

     

    I've successfully set up msmdpump.dll on IIS7 for SSAS 2k8 R2 with Basic as authentication. Excel user's are located outside of domain so they have to enter username and password before connecting. The problem is with number of authentication attempts. When clicking just once 'connect' in Excel I get two event records on my ad machine which tells me there was 2 authentication attempts. When I enter correct password I get 2 successful attempts and when I type invalid I get 2 failed attempts.

    This is annoying because I have 'account lockout threshold' set to 3 and after two invalid entries my user is blocked on ad.

    Is this a bug in msmdpump.dll or in Excel?

     

    Kind regards,

    Fritz

    Monday, August 8, 2011 7:13 PM

All replies

  • I have a suspicion that it may be an Excel issue. Have you tried with another client? If you open up an MDX window in SSMS you can enter the URL for msmdpump.dll as the server name and then in the optoins under the additional connection parameters you can add the username and password with the following syntax "User ID=<My Username>;Password=<My Password>" - if that only gives you one authentication attempt then the issue is Excel.
    http://darren.gosbell.com - please mark correct answers
    Monday, August 8, 2011 11:56 PM
    Moderator
  • Darren, thanks for your reply. I'm afraid I cannot simulate this using SSMS because option Authentication is disabled and Windows Authentication is selected. Yes, I've entered "User ID=mydomain\myusername;Password=mypassword" under Additional Connection Parameters, but I get "The remote server returned an error 401 Unauthorized" which tells me that my credentials did not pass :(

    I'm using SSMS on same computer as the Excel.

    Tuesday, August 9, 2011 8:24 AM
  • Can you try creating a new ODC file in Excel which stores their password in the ODC file? I've documented the instructions here. Curious if this could help:
    http://olappivottableextend.codeplex.com/workitem/16958
    http://artisconsulting.com/Blogs/GregGalloway
    Tuesday, August 9, 2011 10:50 AM
    Moderator
  • The authentication option in SSMS only supports Windows Authentication or SQL authentication. To do basic authentication you need to use the Addition Connection Parameters.

    Try using just the username with out the domain. I think it may pick up the domain from the web server. On my local machine I don't need to specify a domain.

    Even if it's not working - are you seeing one failed AD request or 2?


    http://darren.gosbell.com - please mark correct answers
    Tuesday, August 9, 2011 11:06 AM
    Moderator
  • Darren, I've looked up MSDN site and it says user id parameter accepts domain\username form as valid so that's why I specified it. I tried also without it, no success.

     

    I see only one failed request on AD when using SSMS. This is it:

     

    - <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
    - <System>
     <Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" />
     <EventID>4776</EventID>
     <Version>0</Version>
     <Level>0</Level>
     <Task>14336</Task>
     <Opcode>0</Opcode>
     <Keywords>0x8010000000000000</Keywords>
     <TimeCreated SystemTime="2011-08-09T11:56:39.572668500Z" />
     <EventRecordID>64149229</EventRecordID>
     <Correlation />
     <Execution ProcessID="572" ThreadID="1000" />
     <Channel>Security</Channel>
     <Computer>dc-02.mydomain</Computer>
     <Security />
     </System>
    - <EventData>
     <Data Name="PackageName">MICROSOFT_AUTHENTICATION_PACKAGE_V1_0</Data>
     <Data Name="TargetUserName">mydomain\myusername</Data>
     <Data Name="Workstation">my-excel-machine</Data>
     <Data Name="Status">0xc0000064</Data>
     </EventData>
     </Event>

     

    Ok, now I see only one request which would indicate that problem is with excel and not with msmdpump.dll? But there's another question. Why I cannot connect with SSMS and why I get 401 error even if I type correct password? Do I need to escape somehow my password? My password contains "#" character.

     

    Thanks!

    Tuesday, August 9, 2011 11:59 AM
  • On IIS machine I've seen one more event (task category = logon) which holds this data:

     

    An account failed to log on.
    
    Subject:
    	Security ID:		NULL SID
    	Account Name:		-
    	Account Domain:		-
    	Logon ID:		0x0
    
    Logon Type:			3
    
    Account For Which Logon Failed:
    	Security ID:		NULL SID
    	Account Name:		mydomain\myusername
    	Account Domain:		
    
    Failure Information:
    	Failure Reason:		Unknown user name or bad password.
    	Status:			0xc000006d
    	Sub Status:		0xc0000064
    
    Process Information:
    	Caller Process ID:	0x0
    	Caller Process Name:	-
    
    Network Information:
    	Workstation Name:	my-excel-machine
    	Source Network Address:	10.64.34.58
    	Source Port:		61597
    
    Detailed Authentication Information:
    	Logon Process:		NtLmSsp 
    	Authentication Package:	NTLM
    	Transited Services:	-
    	Package Name (NTLM only):	-
    	Key Length:		0

    Details:

    - <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
    - <System>
     <Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" /> 
     <EventID>4625</EventID> 
     <Version>0</Version> 
     <Level>0</Level> 
     <Task>12544</Task> 
     <Opcode>0</Opcode> 
     <Keywords>0x8010000000000000</Keywords> 
     <TimeCreated SystemTime="2011-08-09T12:04:23.227963100Z" /> 
     <EventRecordID>76192</EventRecordID> 
     <Correlation /> 
     <Execution ProcessID="528" ThreadID="3824" /> 
     <Channel>Security</Channel> 
     <Computer>iis-machine.mydomain</Computer> 
     <Security /> 
     </System>
    - <EventData>
     <Data Name="SubjectUserSid">S-1-0-0</Data> 
     <Data Name="SubjectUserName">-</Data> 
     <Data Name="SubjectDomainName">-</Data> 
     <Data Name="SubjectLogonId">0x0</Data> 
     <Data Name="TargetUserSid">S-1-0-0</Data> 
     <Data Name="TargetUserName">mydomain\myusername</Data> 
     <Data Name="TargetDomainName" /> 
     <Data Name="Status">0xc000006d</Data> 
     <Data Name="FailureReason">%%2313</Data> 
     <Data Name="SubStatus">0xc0000064</Data> 
     <Data Name="LogonType">3</Data> 
     <Data Name="LogonProcessName">NtLmSsp</Data> 
     <Data Name="AuthenticationPackageName">NTLM</Data> 
     <Data Name="WorkstationName">my-excel-machine</Data> 
     <Data Name="TransmittedServices">-</Data> 
     <Data Name="LmPackageName">-</Data> 
     <Data Name="KeyLength">0</Data> 
     <Data Name="ProcessId">0x0</Data> 
     <Data Name="ProcessName">-</Data> 
     <Data Name="IpAddress">10.64.34.58</Data> 
     <Data Name="IpPort">61597</Data> 
     </EventData>
     </Event>


    Tuesday, August 9, 2011 12:06 PM
  • I've got some progress. I added 'Domain' in my msmdpump.ini file so I don't have to enter it in connection string inside SSMS. Now I removed 401 error, but I get another one: unsupported data format.

    When looking at event viewer on IIS machine I see 3 entries. Audit Failure for Credential Validation, Audit Success for Special Logon and Audit Success for Logon.

    Tuesday, August 9, 2011 12:31 PM
  • Excel still requires to specify domain under username filed. If I specify it, I can connect to SSAS. Seems like Excel doesn't use msmdpump.ini domain parameter? Is this possible? :-(

    This is confusing :(

    Tuesday, August 9, 2011 12:36 PM
  • Excel still requires to specify domain under username filed. If I specify it, I can connect to SSAS. Seems like Excel doesn't use msmdpump.ini domain parameter? Is this possible? :-(

    This is confusing :(


    Actually both formats of username work for me, but then I'm running everything against my localhost, so that may not be exactly representative.

    There are definitely multiple requests sent by Excel (in order to populate the pivot table and the field list), but as far as I can tell it all appears to run over the one connection, so it should only authenticate once. I'll check with some of my contacts and see if they have any ideas as to why the multiple auth calls are happening.


    http://darren.gosbell.com - please mark correct answers
    Wednesday, August 10, 2011 7:03 AM
    Moderator
  • Excel still requires to specify domain under username filed. If I specify it, I can connect to SSAS. Seems like Excel doesn't use msmdpump.ini domain parameter? Is this possible? :-(

    This is confusing :(


    Actually both formats of username work for me, but then I'm running everything against my localhost, so that may not be exactly representative.

    There are definitely multiple requests sent by Excel (in order to populate the pivot table and the field list), but as far as I can tell it all appears to run over the one connection, so it should only authenticate once. I'll check with some of my contacts and see if they have any ideas as to why the multiple auth calls are happening.


    http://darren.gosbell.com - please mark correct answers

     

    Thank you very much Darren. This really troubles me and I don't want to increase "account lockout threshold" on whole system because of this issue :(

     

    Wednesday, August 10, 2011 7:17 AM
  • Darren, I found one more thing. In Excel when adding data source even though I enter valid credentials and pass first authentication when second window appears 'multidimensional connection 10.0' I got couple of AuditFailure events on AD with EventId 4771 (kerberos authentication service). I didn't even press button Next on the site. Seems like there's issue with that part? After I type credentials again, but now is this window - I get PivotTable and everything seems ok except that I get badPasswdCount increased :((

    I've upgraded Office 2010 with Service Pack 1.

    Wednesday, August 10, 2011 1:50 PM
  • i know this is old, but anyone ever have resolution with this?  We are using Excel connecting to ssas using the msmdpump.dll.  Everything works fine, but in IIS logs, there are 100 requests to refresh a pivot in excel.  Logs look like:

    2013-11-19 17:52:35 192.168.15.15 POST /msmdpump.dll - 443 - 192.168.15.30 MSOLAP+10.5+Client 401 2 5 1525
    2013-11-19 17:52:35 192.168.15.15 POST /msmdpump.dll - 443 domain\username 192.168.15.30 MSOLAP+10.5+Client 200 0 0 1989

    this times 100 each time you refresh. This makes the refresh go very slowly.  If change the connection to windows auth and connect locally, obviously much faster.  Is this type of behavior specific to the olap driver for excel? Any other clever ways to set this up to speed things up?

    I guess i should also point out, that the authentication is set to basic within Excel.  This is for users, who have a domain username and password (in a client domain).  We don't want to set up some sort of vpn for each of the clients, so this seems the only option for this kind of connection from excel to cube.
    • Edited by Dave_Odom Tuesday, November 19, 2013 6:15 PM
    Tuesday, November 19, 2013 6:10 PM