Troubleshooting SQL connection issue on NS 6.0
This week I had a remote session to help a customer troubleshoot SQL Connectigvity issues. This was a ressurgence of an hold issue, namely the Client Message Dispatcher service would throw regular errors indicating that the Sql Connection is not available or was closed.
The customer had migrated from a local SQL instance to a remote SQL Server (which had multiple benefits, including the switch to a 64-bit platform there) and the problem was apparantly resolved. However it came back this week, so we took a fresh look at it.
First we needed to instrument the Client Message Dispatcher, to acertain whether the issue was really impacting or not. If you are a regular reader of my blog post you will know what my standard answer to these kind of issues is: Altiris Profiler.
Only that the profiler would throw an exception on invokation, of type Cryptographic ... We solved that one issue thanks to Google and the Symantec KB, by simply crafting a new Sql Connection string for the profiler (on the registry).
With the profiler working we could then capture all message processing events and see that the client message dispatcher was working at a fair rate, handling 2 to 10 NSE per second.
But the SQL Connection errors were still showing in the Log Viewer. We then checked for the amount of SQL connections open by the AeXSvc and found... none! We did a global search using netstat and we only found 1 open connection to the server, fro, the AtrsHost process... So, what was prevennting us from seeing the SQL operations? To find out we ran Procmon to capture all network activities and found a lot of traffic going from a Service Host process to the SQL server.
So I asked if we could check the SQL Server connectivity configuration. It turned out that the server was configured to accept connections using TCP-IP and Named-pipes. We removed the settings, restarted the Altiris Service and could see the AeXSvc making connections to the SQL Server on tcp port 1433.
After this single change we monitored the log viewer but had no DB connectivity issues.
One more thing for today, on named-pipes: it is more efficient on local systems but incurs extra overhead connecting to a remote pipe  (as this is done in tcp-ip) so this should really be avoided for remote SQL instances.