I've been playing with WSUS lately and I've been less than satisfied with the reporting. Since the information I need to get at exists in a SQL Server database, I've been writing my own queries to get it out. Here's a basic query to get out what systems in a particular group haven't checked in within the last week. There are 3 basic tables which have to be put together: tbComputerTarget (contains the information on the computers), tbTargetGroup (contains the information on the groups), and tbTargetInTargetGroup (join table between the two). I have found cases where a system will have checked in to WSUS so that we see it's computer name, IP address, etc., but it doesn't actually report a status time (nor any status information), hence an OR for LastReportedStatusTime is NULL to include those systems, too.

DECLARE @GroupName nvarchar(512)
SET @GroupName = '<Group Name>'

SELECT
    FullDomainName
  , IPAddress
  , LastReportedStatusTime
FROM tbComputerTarget CT
  JOIN tbTargetInTargetGroup TTG
    ON CT.TargetID = TTG.TargetID
  JOIN tbTargetGroup TG
    ON TTG.TargetGroupID = TG.TargetGroupID
WHERE TG.Name = @GroupName
   AND (DATEDIFF(dd, LastReportedStatusTime, GETDATE()) > 6
            OR LastReportedStatusTime IS NULL)

The network scanner Nmap has a new version out, 4.68. The GUI interface (Zenmap) which comes with the Windows installer version is pretty sharp. A lot of changes in this version.

 I just did a test run and it correctly identified OS and services on the boxes I just hit against. Used Zenmap and while it is a simple and straight-forward interface, it works.

Every so often I see a post in the forums where someone has stated they've used a Domain Admin level account to run the SQL Server service. The implications are that anyone who is a member of the sysadmin fixed server role is effectively a domain admin. That means if a developer is a member of this role within SQL Server, the developer can use SQL Server to execute with these rights. The same is the case of a SQL Server DBA in production. Typically DBAs don't have domain admin rights unless it's a small shop. But if SQL Server is configured to run under a service account that has such rights, the DBA effectively is as well. Not good.

 In all but the rarest cases this is absolutely unnecessary. Truth be told, SQL Server doesn't even need administrative privileges over the server it's running on. Therefore, it certainly doesn't need Domain Admin rights. In security there is the Principle of Least Privilege. It's a simple concept: give only the rights needed to do the job and no more. This just doesn't apply to people, it also applies to service accounts. When it comes to SQL Server, this principle should be applied to the SQL Server service account just like any other.

How can you determine if your SQL Server is running under a Domain Admin account? First, determine what service account SQL Server is using. This can be done through SQL Server 2005 Configuration Manager (Figure 1) or SQL Server 2000 Enterprise Manager. You can also use the Services applet under Administrative Tools (Figure 2).

Figure 1:

 Figure 2:

Once you know what the account is, check with your system or directory services administrator. If it's named [Domain]\Administrator, chances are likely that it is. If you have access to Active Directory Users and Computers, you can check the groups the service account is a member of (read access is all that's necessary and that's typically granted to all authenticated users in Active Directory). If you find the service account is a member of the Domain Admins group, do the research as to why. If there's a legitimate, unavoidable reason (and this should be extremely rare), seek to change the account immediately. This also applies to the SQL Server Agent service account.

Note: My snapshots are from a development laptop which isn't on a domain, hence the use of LocalSystem. Generally, though, LocalSystem isn't recommended and actually strongly suggested against. If this laptop had been on a domain, these accounts would be running under a local user account.

Andy Warren points to a TechNet article about Security by Obscurity and wanted me to post some notes. Let's start with the example they used.

Rename the Administrator account:

I agree with Roger's take. We intentionally rename the administrator account because it does stop the malware and scripts. We intentionally rename the administrator account because it allows us to alert easier. We see a hit against administrator, and we know 99% of the time it's not legitimate. That allows our reaction to be quicker.

This doesn't mitigate the need for strong passwords. They still must be there. And the argument that a GPO applies the same administrator rename is only partially true. If you segment your systems in different OUs or you use WMI filtering or you security groups to determine which computers can apply a GPO, you can have multiple GPOs with multiple renames.

What Security By Obscurity Gets You:

Security by obscurity gets you an advantage against automated scripts. Security by obscurity gets you a time delay against an attacker intending to break in. Depending on how the system is obscured (for instance, if you move the HTTP port, the attacker must do a port scan first... which, depending ont he environment, may be detectable). Security by obscurity can get you early notice. For instance, if you don't use administrator anywhere and you start getting audit failures against administrator, you know one of three possibilities is true:

1. You have someone legitimately trying to log on but who doesn't know the right account, such as a new system administrator.
2. You have an application or system configured wrong
3. You have a legitimate attack

Any of those three you want to know about.

Why It Isn't Where We Should Stop:

But security by obscurity doesn't absolve you of responsibility for taking all appropriate security measures. For instance, if you rename the administrator account but you still have a weak password, the account is still weak from anyone who can browse the system. Therefore, you still secure the password.

Let's apply this to SQL Server.

• Block UDP/1434. This is the SQL Server listener service port. If you can't access this port, you can't automatically determine the port for named instances.
• Move SQL Server off of 1433 for default instances.

A script or the worms we've seen will not be able to get to your SQL Server. But assume you have a blank sa password (a complete reliance on security by obscurity). You've stopped the easy stuff. But then you've got that one internal guy who knows the sa password is blank. Even if he doesn't know the port on SQL Server, if he can access the server, he can try a port scan, such as with Nmap. Most of these tools (Nmap falls into this list) allow a very slow rate of fire, meaning they won't get picked up on alerting. Once he finds the port, he can get in. And if this server contains data that's worth some money, he can afford to wait until he finds the port. And then the guy gets in, gets the data, and likely you have no audit trail. Not good.

Therefore, certainly hide your systems; make 'em harder to find. But don't neglect the other aspects of security.

I'm a little late on this one, but Cesar Cerrudo has announced he's going to demonstrate exploits to Windows Server 2008, IIS 7.0, and SQL Server at the Hack in the Box conference in Dubai:

Windows Server 2008, Still not totally secure 

The Windows Server 2008 exploits appear to be privilege escalation attacks. With IIS 7.0 it looks like an attack on ASP.NET, based on what was posted. He didn't get specific on SQL Server other than to say that it, too, was a privilege escalation attack as well. The Windows Server 2008 vulnerabilities are worrisome because the attacks use the lower privileged accounts to escalate to SYSTEM. This should prove to be an interesting month.