How to fix errors connecting to MSSQL Server

With the addition of the database query monitoring feature of Overseer Network Monitor, I’ve had a couple users come to me with errors connecting to their MSSQL server using Overseer. This is primarily because the default configuration of Microsoft SQL Server disables TCP/IP communication for security reasons. When Overseer is unable to access the MSSQL server, you may get an error like this:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding

To properly configure MSSQL server to listen on TCP/IP, you’ll have to follow these steps:

1. Find and run the “SQL Server Configuration Manager” on the start menu. This should be under “Microsoft SQL Server 2008 R2->Configuration Tools”.

2. Once you open the SQL Server Configuration Manager, navigate to SQL Server Network Configuration->Protocols for MSSQLSERVER. Find ‘TCP/IP’ on the right side, and double-click it:

3. On the TCP/IP Properties page, make sure ‘Enabled’ is set to Yes, and ‘Listen All’ is set to yes, per this screenshot:

4. Also, make sure the port is specified on the ‘IP Addresses’ tab, under the “IPAll” section. This should typically be 1433:

Once this is done, you’ll have to restart the MSSQL Server service, and then attempt to connect to it with Overseer again. If things still don’t work, you may have a firewall blocking port 1433, either on the MSSQL Server machine, the Overseer machine, or potentially a hardware firewall in between– make sure port 1433 is allowed/forwarded as needed.

Being notified when a computer is connected to network

Although Overseer Network Monitor is primarily used to notify administrators of problems when a resource goes down, some administrators have other requirements as well. A customer recently contacted me needing to be notified whenever one of his mobile users connected his laptop to the corporate network. He needed this so he could push policies to them– but this could be useful for many things, such as general network security.

The primary way this is done with Overseer, is with the new ‘Reachable’ setting for a ping resource. There are a few other settings that are best to tune as well. Here’s a step-by-step on setting up a system like this.

First, let’s create a schedule. This defines when these computers should be checked for and how often notifications should be sent. You can define when they are checked by setting values on the ‘Monitoring Frequency’ and ‘Monitoring Exceptions’ screens. For notifications, something like this would probably be ideal:

As you can see, this instructs Overseer to send notifications immediately(as soon as it detects the laptop is connected), do not notify repeatedly(setting of 0 minutes), and to stop sending after 1 notification has been sent. I’ve also unchecked the ‘Send notification when resource recovers’, as this doesn’t matter for us. Name this schedule something appropriate, such as “Mobile Computer Detection”.

Next, let’s create a resource group for these laptops to keep them separate from our other resources. This can be done by going to manage->resource groups and adding one. Give it an appropriate name, such as “Mobile Computers” below:

Next, let’s create an actual resource. Go to New Resource->Ping. Create a resource with settings something like below:

As you can see, I’ve created a resource labeled “Derek’s Laptop”, assigned it to the ‘Mobile Computers’ group I made, assigned the ‘Mobile computers connected’ schedule, and assigned it to a “Laptop Notification Group” notification group I also created(which includes my Email, but not my cell phone, for example). I set the host name to the name of my laptop, and set the fail if setting to ‘Host is Reachable’.

Click Save, and now Overseer will send you a notification whenever that host is connected to the network. Based on our schedule settings, we will also not be notified repeatedly, nor when the computer is removed from the network– but we’ll be notified again if the computer is hooked up after being disconnected. Note that the laptops will need to have their firewalls configured to allow ICMP/Ping requests, if they aren’t already.

Memory usage or process counts are always logged in the ‘data’ field for process monitor checks, regardless of failure/success

Most resource types in Overseer log some sort of data to the ‘data’ field, accessible by looking at the resource history. This can be useful for temperatures, response times, etc. With Overseer 5.0, the Process monitor was changed to properly log the memory usage or process counts to the data field with each check. This makes monitoring windows processes with Overseer a little more useful, even if you don’t have any failures.

Another “Network Path was not found” gotcha

When testing Overseer 5.0, I got stuck on a “Network Path is not found” issue. I was testing with workgroup-based computers instead of domain-based computers, and I was quite certain it was due to this setup that the problem was happening– as my domain based computers continued to work fine, even connecting to workgroup-based computers…

Eventually, I tracked this down to using usernames such as “.\administrator”… Sometimes Windows uses these, so you’d think it’d be OK– but when connecting FROM a workgroup-based computer to another workgroup-based computer(and maybe domain computer; I didn’t test that), you’ll get a “Network Path was not found” error– totally useless for an obvious **username** issue… While there are many causes of the network path was not found error, this is one that I didn’t know about until today.

Oddly enough, I did a network tap when trying to figure this out, and I saw Windows attempting to connect **on port 80** to the computer, versus using SMB ports(139, 445, etc.)… This was crazy, yet I could find no information online about this– obviously, it’s using the WebClient service instead when it sees a username like this…I’m still not sure why.

So, bottom line– if you’re receiving a “network path was not found” error, and you’ve gone through the checklist, make sure you’re not using .\username when logging in.

If you’re using Overseer network monitoring software, Overseer will automatically change a username without a domain qualifier into the appropriate MACHINE\username– so simply enter the username, and you’ll be OK.

How to prevent ‘false’ notifications

Sometimes Overseer customers will Email me and tell me that Overseer notifies them that their server is down, yet they login and everything is fine. This is something that will occur from time to time– Overseer detects a failure, but this may be caused by a network hiccup(particularly over WAN connections), or occasionally an OS issue. The first thing to do, is obviously investigate by looking at event logs, do some basic network tests(ping [host] -t to look for packet loss), etc.

However, even if something is found, it’s possible that there’s nothing you can do about it right away– but you really don’t want to be bothered by Overseer. Thankfully, there’s a feature in Overseer created just for this purpose. Simply edit the schedule for the resources in question and edit the ‘After resource has been down’ setting, as shown in this image:

Now, Overseer will wait 15 minutes before sending the first notification. So, when Overseer is checking resources using this schedule, if it fails it will see it’s been down 0 minutes, and wait. 5 minutes later, it will check again– if it’s still down, it will be down 5 minutes– which is also less than 15. It will wait until it hits that 15 minute mark(as configured above), and once it does, it will notify the administrators.

Windows 2008 R2 issues with remote monitoring

Windows 2008 R2, and possibly some other later versions of Windows, have a problem with being remotely monitored by non-domain accounts. This blog post will show how to work-around this Windows “feature” that disables such monitoring.

Recent versions of Windows introduced UAC– User Account Control. While this may be useful on some workstations, most server admins will disable it so they don’t spend half their day clicking ‘yes’ on prompts. What many don’t realize, however, is that disabling UAC on the server doesn’t disable “remote UAC”– most people don’t even know such a thing exists…

Well, it does– and it causes remote monitoring tools like Overseer to **not work** when utilizing local accounts. Domain accounts will still work to monitor the computer, but if you’re trying to monitor a W2K8R2 computer that is not a member of a domain(and therefore using local accounts), you may run into this issue. This is further obscured by the fact that this remote UAC does not appear to affect the built-in ‘Administrator’ account– only separate users that should normally be allowed(members of Administrators group).

Well, the solution is here. To disable this ‘Remote UAC’ feature, you can add this DWORD registry value, setting it to 1:

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\LocalAccountTokenFilterPolicy = 1

No restart of the server or any services is required. Note that this registry change must be done on any servers being monitored by Overseer– not just the computer running Overseer itself.

For convenience, you can use the .reg file below to add this registry key, or you can do it manually using regedit.

DisableRemoteUAC.reg

MSSQL connection monitoring

Would you like to monitor the number of SQL connections to your MSSQL server? Possibly the number of SQL connections to a specific database? This can easily be done with Overseer Network Monitor’s MSSQL Monitoring capabilities.

A previous post of mine showed you how you can monitor MSSQL database sizes. In this post, I’ll use a similar mechanism to what I used there. First, create a DB Query resource type in Overseer:

We used this SQL query:

select d.name,p.* from master.dbo.sysprocesses p join master.dbo.sysdatabases d on p.dbid=d.dbid

This gets the count of the current connections from the MSSQL master database. You can add “WHERE name=’databasename'” if you’d like to only track connections for a specific database. You can then use the scalar result comparison settings to determine when you’d like to be notified of this count.

Is this helpful to you? Are you looking for another SQL query to perform a specific MSSQL Monitoring task? Please contact us using the support link above, and share what queries you may have used, or what type of query you might be after.

Thanks.

Monitoring remote resources through a firewall

Numerous people have asked how they can monitor their resources through a firewall with Overseer Network Monitor– which ports do they have to allow, etc. For HTTP or EM1 resources, this is simple– forward tcp port 80(or 443 for https/SSL). If you’re monitoring ping resources, simply forward ICMP echo packets. For Windows-based resources, the answer is a little more involved.

Overseer monitors windows resources using standard Windows Networking protocols. The exact ports vary in different versions of Windows, and there’s some negotiation thrown in to find one that works. Typically forwarding tcp ports 135-139 and/or 445 will do the trick. I often caution people not to forward these ports on the public internet, however.

It is my professional opinion that Windows Networking protocols are not internet safe. While some of them may contain appropriate security mechanisms to prevent network sniffing, I prefer to **never** open a windows protocol port to the open internet. If you look at windows security updates, you’ll notice that the vast majority of the security holes are in regards to Windows networking(and IE)– it’s therefore inherently safer to not allow these ports to be opened to the internet…

If you must open these ports to the internet, be sure to lock down the firewall rule to only allow authorized IPs access. You should also have strong passwords on your network(this is generally a good idea everywhere, but not always followed).

Now, the preferred way to monitor remote Windows resources is using a secure VPN. Many routers/firewalls support site-to-site VPN tunnels, and these are incredibly useful for this sort of thing. Please consult your network administrator for the best way to create a site-to-site VPN tunnel for monitoring purposes.

Event Log Duplicate Filtering

Multiple customers have requested the ability to filter duplicate event log notifications when they’re monitoring Windows Event Logs. At first glance, this may seem like a simple thing, but it’s a little more complex than that.

If you want to enable duplicate event log filtering, you’re going to have to enable this feature on your event log resource(s):

As you can see, you must check the box to prevent duplicates, and then specify a number of minutes as the ‘duplicate’ time window. After this amount of time, if the event is seen again, you’ll get another notification. This is critical, as otherwise you may not get event log entries because you had the same event logged days, weeks, or even months ago.

The second value is the ‘Detection Method’. Windows Event Logs use a source name(such as the name of an application) along with an Event ID. This event ID is a number defined by the source of the event. The last value is the message. This message is usually unique to the specific time an event happens. The event ID usually indicates the type of event happening(i.e. “unable to write to file”), and the message indicates some data specific to that event(i.e. which file cannot be written to). This option lets you control how duplicates are detected.

Categories: