Twelve Angry Techs

Life-defining trials come to us at the most unexpected times. And in a Windows administrator’s life come trials that define the very soul of who he or she is professionally. Often the admin has to stand against the whole IT organization and not cave to the mob thinking of the rest of their peers.

Let’s enter one of these episodes, where high drama meets high tech. A major project was stalled because of authentication errors in the Web application. The CTO assembled his 12-person IT staff. “We must, no, we WILL get this problem solved by weeks’ end, or the sponsor of the project will be embarrassed or even fired when he tells the CFO that we’re over budget.” The IT team was assembled in their conference room with their laptops, coffee cups, and several white boards.

Kessler (the project manager: “Here’s what we have. SQL Server Reporting Services can run this report on one machine fine. Here’s the report on my laptop:

Now we know that works great. I’m a non-admin user in the Domain forest, and this is how the report looks now. But here’s how the report looks from the CFO’s laptop. This is a show-stopper.”

Marshal: “You always state the obvious. Why don’t you offer a solution?”

Beagly (an IBM mainframe veteran): “It’s those developers!”

Weeb: “Hey, what’s up with developers? I’m a coder!”

Link: “Has anyone looked at a network trace yet?”

Janet: “Well, it’s a Web page error, and I feel responsible. Let me look at the site settings again. I don’t want any of you guys telling anyone that I messed up. I always cover for you!”

Blank Disk: “Man, will you look at the time! It’s 5:17 already, and the traffic is gong to be crazy out there.”

Paine: “OK, let’s see where we went wrong on this project. We told that CRM consultant at the Microsoft VAR that we read the white paper. But reading a white paper doesn’t always tell you how to deploy a system in every environment. So we have to stop blaming one another and ask ourselves questions about our environment.”

Kirby: “So here’s a question. How does the Web page authenticate? We’re only running IP on our network. We have several forests that are connected by trust. The forest in the home office is Windows 2000 Server SP4; we’re Windows Server 2003 SP1. The white paper said we needed Active Directory and not much else. Where did we mess up?”

Burnet: “You folks have to fess up and tell the CTO that we need help.”

Blank Disk: “Hey it’s 6:42. I’m going to miss American Idol!”

Lookup: “I just googled the problem, and some guy mentioned Kerberos authentication.”

Paine: “Yep, I was getting the same HTTP 401 error on my laptop. Then I uninstalled the Google toolbar, and now I can reach the reports.”

Janet: “Remember the white paper said that pop-up blockers would prevent the page from being displayed? The toolbar has a pop-up blocker. So we have one fix so far. Kessler, does that fix it on the CFO’s environment?”

Kessler: “No, it’s still the same. Something is very wrong here.”

Sweeny: “Have any of you looked at our network before we started the install? For instance, its authentication problem. We can authenticate to the reports. The folks in the other forest can’t. Do they have rights?”

Lookup: “Good point, I just checked on that. They can see everything in our forest that they need to see. The admin guys over there can see everything. There’s still no change with the report problem.”

Joe: “Marshal, what did those diagrams in the white paper talk about?”

Marshal: “Something about delegation and constrained delegation.”

Kirby: “That was the Kerberos issue. We fixed that when we raised the forest functional level to native mode and trusted the Web server for delegation.”

Janet: “Yes, that’s right. I had checked to make sure the Web site was using Integrated Security. I used the cscript command: C:\>cscript c:\inetpub\adminscripts\adsutil.vbs get w3svc/1720207907/root/NTAuthenticationProviders. It showed a result of Negotiate, NTLM, which meant that it was set for Kerberos authentication.”

Kessler: “Why is Kerberos authentication so important now?”

Lookup: “I just found an article on Kerberos authentication. Looks like it’s used to secure back-end communication to a database.”

Janet: “Lookup is right. Before we installed CRM, I created an ODBC connector to the SQL server. We have a two-server setup. A Web/application server and the databases and SQL Reporting Services are on the SQL server.”

Weeb: “Yeah, back in the NT 4.0 days with IIS 4.0, we used anonymous access to pass credentials back to the database server. We were using SQL Server 7.0 then. But I heard it’s different now.”

Kessler: “But we’re using SQL Server 2005 now.”

Marshal: “Yep, and Janet, that’s running IIS 6.0, too, right?

Janet: “Yes it’s one of the prerequisites. A scaled-down version of Visual Studio is on there as well. So IIS 6.0 is running the SQL Reporting Services on SQL Server, and the Reporting Services databases are on the SQL server along with the Microsoft CRM databases.”

Sweeny: “That’s true, Kessler. We installed the CRM to the default instance of SQL Server and the default Web site.”

Blank Disk: “It’s 8:37. Is it OK if I go out for Chinese? Who wants egg rolls?”

Burnet: “I see your brain is on the project.”

Blank Disk: “Who can troubleshoot on an empty stomach?”

Joe: “I want some wonton soup.”

Marshal: “So how do we know if we’re using Kerberos to authenticate?”

Janet: “It’s set by default. Remember, I looked at the NT Authentication providers.”

Paine: “You can use the Setspn.exe tool to check on Server Principal Name (SPN) resolution.”

Weeb: “Yeah, I saw you use the ADSI Edit tool on the DC to check the SPNs on the Server account object.”

Kirby: “It wasn’t until we did this project that I saw how important the attributes were on the object.”

Lookup: “I found the article relating to our problem. It’s a KB on Double-hop Kerberos authentication. It seems that we did everything right. We assigned the account that starts the SQL Server service on the SQL server to have the SPN for the SQL service and delegated it to resolve all Kerberos authentication.”

Marshal: “So it’s working for us.”

Weeb: “Yeah, if you have your browser set right. Add the CRM site to your trusted sites, use the advanced tab in Internet Explorer (IE) to set the browser to use integrated security, and set IE to use the currently logged-in account. It’s working great on my laptop now.”

Burnet: “But the CFO isn’t in our forest.”

Link: “Well, if it’s Active Directory, then it’s port 389. We’re not blocking that at all on the Cisco routers between the sites.”

Sweeny: “I just ran Setspn on the CFO’s laptop. It’s in the other forest. I used the command setspn.exe -l \\crmserver. It just hangs there. Clearly, he can get to the port 80 page on the default Web site.”

Janet: “The CRM page is port 5555.”

Sweeny: “Yeah, I see that. The laptop gets that far but not to the reports.”

Link: “I’m doing a trace with Wire Shark. Marshal, try reaching the site and the reports from your laptop, and Paine, hit the site and the reports from the CFO’s machine and account.”

Paine: “No problem. Nothing like looking in the pipes to see what’s really going on.”

Blank Disk: “I’m back. Who gets the shrimp fried rice?”

Joe: “That’s mine. Janet, you want some?”

Janet: “We’re kind of busy here, Joe. It’s after 9 already.”

Kessler: “So Link, is the trace done?”

Link: “Yeah. I’m checking for traffic now, and there’s not one packet of Kerberos traffic between the Windows 2000 site and the Win 2003 site.”

Marshal: “But wait, we have a two-way trust between sites.”

Link: “Yes, well there the trust works great, but there’s plenty of RPC traffic.”

Lookup: That’s what this guy on the Microsoft forum just said. Windows 2000 trust uses RPCs to communicate, but not Kerberos.”

Kessler: “So we’ve got a problem. Seems like no matter what we do, no one in the Windows 2000 forest will be able to use the reports.”

Burnet: “Unless we upgrade all the other forests to Windows 2003.”

Marshal: “Not going to happen. No budget for that. We still have development costs coming up. These reports are designed to help us make our next revenue goal. So we have to get them to work.”

Paine: “Well, let’s ask ourselves a few questions. “The Web server asks for an authentication. But then it takes my authentication and does what?”

Janet: “It takes the packet and impersonates you or your account to the SQL server. Since you’re a CRM user in the database, it checks out who you are in AD and authenticates you.”

Kirby: “And we know AD attempts to use Kerberos but can fall back to NTLM.”

Blank Disk: “So will the communication work with NTLM?”

Marshal: “Blank Disk! You made a contribution. Are you going to make this a habit?”

Joe: “He was just thinking out loud.”

Kessler: “Keep thinking, Blank Disk.”

Paine: “I saw a TechNet article about best practices with Web-to-SQL authentication. Let’s do a Web search for it.”

Lookup: “I’ve got it. It says that if the site is very busy, you can consider using NTLM authentication back to the SQL server.”

Marshal: “So will the Microsoft CRM allow that?”

Janet: “I’m in the Microsoft CRM Community forum now. Seems like a few people have hit this problem before. There’s an MVP who has something on it….”

Paine: “Yep, it’s a registry setting that will let us tell the CRM server to speak to the SQL server via NTLM.”

Weeb: “Yep, first, you log into our CRM Web server and run regedit.exe. Navigate to this location: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM. Then create a new DWORD called NTLMForSQLRSServer. Give it a value of 1, and reboot the server. What did we do here? Well, we told the Web server to speak to the SRS services to use only NTLM to authenticate with the Reporting Services and the SQL Server databases.”

Burnet: “But there’s a catch guys. We can’t schedule reports to be delivered to users, with the CRM system.”

Sweeny: “But you know, I think I can tweak a SQL Server job and SQL Mail to do something. It would be better than having to upgrade three sites around the world and deal with unforeseen problems with the other networks.”

Weeb: “OK, I made the change in the registry, and I bounced the box.”

Marshal: “Is it up yet? Let me know when I can try it.”

Marshal: “OK, I’m logging on from the Win2k forest. Let’s try a report. Say, the reports list, and… Yes! They run!”

Janet: “Son of a gun, look at that.”

Paine: “Try the other domains. Don’t scream victory yet.”

Link: “They’re all working, Paine.”

Lookup: “That’s a pretty obscure workaround!”

Weeb: “I got some soy sauce on your laptop, Sweeny.”

Kessler: “I’ll email the CFO now and let him know we got this working.”

Marshal: “Trying to score points again?”

Kessler: “Hey, I’m the PM.”

Weeb: “So, Beagly… developer error?”