Most of us have heard the expression, “Don’t shoot the messenger” or “Don’t shoot, I’m only the piano player.” That’s still a good admonition when it comes to modern applications that we deal with. Often those ugly errors that we see from application are like a “damsel in distress” calling for help as the freight train speeds toward her.
Let’s take a case of an application that’s used by the A/P and A/R folks at a certain company in the desert southwest, where things get pretty thirsty if the local saloon is shut down. They use Microsoft Dynamics Great Plains, which uses a Microsoft SQL Server-based back end. The server application piece can be installed on its own server or on the SQL Server system for smaller enterprises, and there’s a workstation client piece that’s very much what we IT administrators would refer to as a fat client.
It came about that, although everything was working fine for a year, the billing department called the local administrator with a message of panic about a work stoppage: “We can’t connect or work, so we’re out of business until you get this fixed. Have a nice day!”
At about this time, the administrator emailed me about the problem: “A sidewinder of an error really bit me. I am receiving a message 2759 was missing error on the GP server. The clients cannot connect; they get the error Could not register database trigger. Any ideas, partner?”
I support the client, so I connected remotely and began the “CSI”-like forensic.
First stop was the SQL Server system. For the accidental DBA out there, triggers are a SQL Server object attached to SQL Server table. Something like a stored procedure. So I knew that the error was associated with access to a SQL Server table. Since the Great Plains client was loaded on the server as well, I had the admin join me via a Terminal Services session, and we logged on to the Great Plains application while logged into the SQL server directly without a hitch. I also rechecked all the SQL Server security settings and made sure that the users were members of the DYNGRP and Public groups on those databases regarding the Great Plains Application.
Now we knew that the freight train was not coming from the server. It was time to look at the client machines. A quick look at the SYSTEM DSN on ODBC connections showed us that mixed-mode authentication was indeed working.
I used the Microsoft Dynamics GP Utilities to resync the Great Plains client-configuration files to the servers, but got the same trigger error. The shotgun-blast approach of rebooting the services and the client machines did nothing to resolve the issue.
It was time to ask ourselves deeper questions. The back end is SQL Server 2005. How does a client communicate with the server? And since it was working yesterday, what changed? Perhaps the “surface” of the SQL server was now unreachable. I ran the Surface Area Configuration wizard on the SQL Server 2005 server and zoomed in on Remote Connections.
This configuration ran fine until today. I surmised that this was not an application problem or a SQL Server problem, but rather a network problem. I enabled the option to use both the TCP/IP and named pipes. Named pipes, which are part of the Server Message Block (SMB) suite, are often associated with Windows NT 4.0 authentication.
After I restarted the SQL Server services and clearing the NBT and DNS caches on one of the Great Plains client machines, the authentication went right through. “Whoa! Partner, it’s workin’, and I thought for sure it was the application!” I responded, “Well, sometimes you have to remember not to shoot the application.” I reminded him to check out his network and forest traffic with WireShark and get a rope around what his DCs are doing.
“What say we go over to the saloon and have ourselves a sarsaparilla and watch Rio Bravo on the big screen?” said the admin. “I’ll take a rain check,” I said. “I have to write up the bill, and you have to untie that damsel in distress."