Service Account Password Change + Kerberos + Excel Services + Data Connections
Mike Walsh, please let me know how to handle this as it has an entire section that deals with Excel Services, but I do not believe it can be answered/resolved by just resolving Excel Services in a vaccuum. I would like to cross-post the ECS stuff by itself in its own forum, but please advise on how you'd prefer me to do it.
This is a LONGGGGGGGGGGG and drawn out story where I can't even remember everything I've tried, so bear with me:
- 2-server MOSS Enterprise farm (WFE/DB) on VMs for pilot testing with WS03 32-bit, SQL 2005, MOSS Enterprise, Service Accounts for everything, all patches/updates/service packs current
- 6 months of continuous operation with no problems and running full Kerberos across all 4 web apps (Central Admin, MySites, SSP, Intranet) - each with its own app pool identity and separate SPNs
- Just this week got to the point of demonstrating/testing/piloting Excel Services. It works great for regular workbooks in trusted locations, and it worked decently at first when using a data connection file, but that no longer works, and here is why with more detail:
- Created an SP List using columns and data from an existing workbook. Exported that list back to a spreadsheet and generated a pivot table + chart on separate sheets. Configured the data connection to always use the DC file, use Windows Authentication, and exported it to a DCL on same site, then published the workbook to a Report Library on the same site. I then created a new page that has a web part showing the list and showing the bar chart through Excel Web Access. The hope was to be able to create/modify records in the SP list on that page and see it reflect in the chart (plus other charts later) all on one page using a 1min auto-refresh or letting the user manually refresh from the EWA Update menu.
- Ok, so this all worked in that the chart would display nicely on the page, but it would not refresh. I kept getting the common error that says you can't update the data connection (found the same error on countless sites/blogs), and it named the exact data connection I was using. My single WFE has Central Admin, my Intranet, and ECS all running, so I didn't think I would have any double-hop issues. Plus, I have Kerberos running anyway.
- After reading tons of blogs and articles that talked about setting ECS security for Negotiate and lots of other things, nothing had changed. If I opened the DC directly from the DCL, it always pulled in the SP list data fine. If I opened the workbook from the report library, it always warned that it couldn't refresh the data, then it would show cached data. Likewise, the EWA chart would show whatever the workbook was showing. If I opened the workbook in Excel, then it would update just fine, and I could save it back, then show updated data in the EWA chart, but this obviously wasn't the end goal. After much troubleshooting, I decided to stop and re-start ECS in Central Admin. For some still unknown reason, this sent me down the path of destruction.
- After restarting ECS, I started getting the dreaded "An error has occurred" when opening workbooks via Excel Services. This is where my brain is so jumbled that I can't remember small details. I can't remember if it only happened on the book with the DC, or if it happened to all workbooks. What I do know is that I decided to try and make a new SSP to test ECS there, but this is where all hell broke loose:
- Unlike most implementations I do, I was not able to create the service accounts myself. I requested them and was given the long secure password. When creating an SSP, it requires the password, but it kept failing even though I was using the one I was given. That account got locked out, and it was after hours, so I had to get the after-hours support tech to unlock it. Unfortunately, this did me no good, because I still didn't know the password, and no iteration of the PW was working, so I had to change the password to the one I always thought it was. This, of course, is not fun when dealing with service accounts. I followed all the recommended steps for changing not only a service account password (Central Admin > Service Accounts), but also an app pool (stsadm updateaccount - how does this work when it doesn't specify which app pool?), SSP password (stsadm editssp), and I think some others that aren't off the top of my head.
- Before doing these steps, my SSP app pool kept crashing, but afterwards, it worked fine and still does. However, I could no longer browse to my ssp admin site due to kerberos issues. I spent at least 10 hours...maybe 15...just trying to fix this, because I felt it was necessary before even trying to troubleshoot ECS. I learned that the Office Server App Pool and site were what the SSP uses to expose web services like ECS, and that app pool runs under the Network Service account. I have never touched that app pool and never knew what it was for. I also realized then that I couldn't access the ECS.asmx on port 56737 (Office Server default port) due to the same kerberos issues even though it uses network service.
- Anyway, I fixed my SSP kerberos issue by switching it from <machine>:<port> to a host header on port 80 and resetting my SPNs accordingly. I can now browse to it just fine and have confirmed Kerberos is working. I was hoping this would cure my ECS issue, but it didn't
- ECS still works when not using a DC in the workbook, but my simple workbook with the SP List ODC file always says "an error has occurred." There is no associated event log entry, but I do seem to get some funky stuff in the ULS logs related to that workbook, one of which is the common reference to being unable to access http:<machine>:56737/ssp1/ECS/ECS.asmx (short-hand).
Thoughts? Ideas? Only my 2nd thread out of many posts, so I'm hoping I get lucky. BTW, there are several other threads on this ECS topic with similar problems, but no solutions worked for me, and I've tried them all, so I think my issue has to do with the relationship between the SSP, the Office Server app pool, ECS, and Data Connections with Kerberos.
すべての返信
As we don't (ever) want parallel threads in different forums (because replies to one are not seen by readers of the other so we potentially get wasted and/or duplicated effort) the only solution if you are convinced that the problem goes beyond Excel Services would be to make a short post here (System/Admin) giving extremely brief details of the non-Excel Services aspect of the problem and then asking people to post their follow-ups to the Excel Services post (give the link) if they have any input on the problem.
Mike
P.S. The system would work the other way too (Post here (Sys/Admin), but request input to a post in the Excel Services forum), but given the number of times ECS is mentioned it's much better there imo.
-------------
Moving to Excel Services ...
WSS FAQ sites: WSS 2.0: http://wssv2faq.mindsharp.com WSS 3.0 and MOSS 2007: http://wssv3faq.mindsharp.com
Total list of WSS 3.0 and MOSS 2007 Books (including foreign language titles) http://wss.asaris.de/sites/walsh/Lists/WSSv3%20FAQ/V%20Books.aspxMike, considering the # of views and replies, I think it's a moot point. =P Anyway, here is an update:
- Made significant networking changes so that all of my web apps have their own IP and own "A" record in DNS
- Reset my SPNs for all
- Got Kerberos working fully on all 4 web apps without fail and can access my SSP admin site now without any issue
The downside? Now Excel Services doesn't work AT ALL! How in the world can it just completely not work when all I did was some networking/dns changes? I have AAMs for the FQDN and NetBIOS of SSP, and my Intranet stuff works great with FQDN, but I have no idea why it would suddenly cause Excel Services to never work. I have rebooted the machine, made new workbooks, made new trusted locations, restarted ECS, added permissions on the SQL DB, etc., but nothing works. I only got ECS to work again by creating a new SSP and associating my Intranet web app with it. After that, my regular workbooks rendered perfectly, and my one SharePoint-List-with-Data-Connection workbook finally went back to telling me it couldn't refresh the data from the ODC, which at least got me back to the very beginning scenario that started it all.
Apparently, ODCs to SharePoint Lists just simply cannot be refreshed in EWA. I have no idea why that wasn't a basic fundamental of ECS, but it's apparently just not supported in version 12. I even went so far as to use None w/Unattended Account, SSO with an AppDef, but all of that worked the same as Windows Auth mode.
So, I guess I could live with not being able to reach my ultimate goal of having my sharepoint list data auto-refresh in EWA, but I at least need to get my original SSP working like normal. I fear it is corrupted or has some metabase issues, since I did a lot of metabase changes while trying to troubleshoot. Another interesting thing I saw is that when I created SSP2, the Office Server Web Services node for SSP2 referenced the SSP2 app pool instead of the Office Shared Services App pool like SSP1 does. I don't know why it's different.
It is the weekend. There are a lot less people reading posts for most of Friday and of course especially on Saturday and Sunday than there are Mon-Thu. There are also probably less MS support people around than usual because it is the Chinese New Year holiday.
I would as I suggested make a summary post of the non-ECS issues in Setup/Admin referring to this one and have it there ready for the start of day Monday in the Far East/Australia.
It is probably all you can do for a combination with probably as few users as this one (i.e. not me either) - that, and hope.
Do not give up quite yet.
WSS FAQ sites: WSS 2.0: http://wssv2faq.mindsharp.com WSS 3.0 and MOSS 2007: http://wssv3faq.mindsharp.com
Total list of WSS 3.0 and MOSS 2007 Books (including foreign language titles) http://wss.asaris.de/sites/walsh/Lists/WSSv3%20FAQ/V%20Books.aspxBump for a new set of eyes. If anyone reads it all and comprehends enough to answer back, I'll give you a cookie. :)
SharePoint Architect || My Blog
