Here is a Transact-SQL query that will return (almost) everything one needs to know about the version of a SQL Server instance:

SELECT SERVERPROPERTY('machinename') AS 'Server Name',
         SERVERPROPERTY('instancename') AS 'Instance Name',
         SERVERPROPERTY('productversion') AS 'SQL Server Version',
         SERVERPROPERTY('productlevel') AS 'Product Level',
         SERVERPROPERTY('edition') AS 'SQL Server Edition',
         SERVERPROPERTY('collation') AS 'Default Collation',
         SERVERPROPERTY('licensetype') AS 'License Type';

The results returned by this query are:

Server Name  -  The name of the computer on which SQL Server is installed.

Instance Name  -  If SQL Server was installed as the default instance, this value will be NULL.  Otherwise, it will name of the instance.

SQL Server Version  -  The version of the SQL Server instance.  This can be used to determine what updates have been applied to the instance.

Product Level  -  The current installed Service Pack.  If this value is RTM (Release To Manufacturing), then no service pack has been installed.

SQL Server Edition  -  This is the edition of the SQL Server instance.  Possible values are “Express Edition”, “Workgroup Edition”, “Web Edition”, “Standard Edition”, or “Enterprise Edition”.  The edition determines what features are available in the SQL Server instance.

Default Collation  -  The default server collation value that was selected when the SQL Server instance was installed.

License Type  -  The type of client licenses used by the SQL Server instance.  If this value is DISABLED, then the license type is per-processor (which is anonymous access).

This query works with all editions of SQL Server 2005 and higher.

I had the opportunity to have an Internet email account prior to the first spam message.  It was definitely a different age, when mail server administrators ran open relays as a convenience to Internet at large.  Today, mail server administrators are engaged in an arms races against the spammers and use whatever means necessary to help barricade the walls.  One those tools turns out to be something rather simple built into the basic structure of the Domain Name System (DNS): PTR records.

PTR records are pointers from an IP address to a canonical name.  It’s often called a reverse DNS lookup, because unlike regular DNS lookups that provide a canonical name and return an IP address, a reserve lookup provides an IP address and returns a canonical name.  And unlike normal DNS lookups, where if the lookup fails, your browser doesn’t take you anywhere or your email cannot be sent, nothing really depends on reverse DNS lookups.  The lookup can fail, return the correct canonical name, or even the wrong canonical name.  The Internet, in general, doesn’t care.  But because of who is in charge of the PTR records, it’s become an important tool in the war against spam.

Everybody is familiar with domain names.  They are the canonical name we use in our browsers that take us to websites, and in email addresses that say where the message should be delivered.  PTR records are associated with domain names as well, but they all exist within the subdomains of one special domain, the in-addr.arpa domain.

I should note the in-addr.arpa domain is used only for IPv4 address reverse lookups.  IPv6 address reverse lookups use the ip6.arpa domain.  But since the concepts are the same for both, and most of the world is still using IPv4 addresses, I not going to mention the ip6.arpa domain again.

For every IP address, there is a matching domain where that IP address’s PTR record (if it has one) can be found.  The domain uses the first three octets of the IP address, in reverse order, as a subdomain of in-addr.arpa.  For example, given the IP address 192.168.74.153, the PTR record for this IP address exists in the domain 74.168.192.in-addr.arpa.  If we were to look in the zone file for this domain, we would see an entry similar to this:

153    IN  PTR    centaurifiles.com

The in-addr.arpa subdomains are all controlled by the organizations that hold ownership of the IP addresses.  They are the only ones who can create, modify, and delete these PTR records.  And this is the key to why they are important in the war against spam.  The spammers cannot change the PTR records on their own.

Yes, it’s possible that the spammers could actually own the IP addresses they use, and therefore be able to set the PTR records for these addresses.  But all spammers rely on stealth to hide the IP addresses they are using since once discovered, even with proper PTR records, they are quickly blacklisted.  Having the ability to control the PTR record because you hold title to the IP address puts the spammer in harsh glare of sunlight.  And like all cockroaches, they will quickly scurry for the shadows.

When a mail server that has a message to delivery makes a connection to the recipient’s mail server, it identifies itself using a host name.  The host name can really be any arbitrary string, but it’s normally the host name of the sending mail server.  What the recipient’s mail server does is to take the IP address of the sending mail server and do a reverse DNS lookup.  If the canonical name that’s returned matches the host name provided by the sending mail server, then delivery of the message can proceed, subject to other spam checks.  But if the canonical name doesn’t match, then the recipient’s mail server can automatically classify the message as spam and drop it in the recipient’s spam bucket, or (and this now becoming the default action on many mail servers) refuses delivery of the message and drop the connection.

Using the PTR record as a spam test primarily defends against home computers that have been infected by a virus and are now minions in a spammer’s botnet.  The virus acts just like any other popular mail server program trying to deliver its unwanted spam, but it has no control over the PTR record.  And residential Internet customers don’t get to set their PTR records.  Even if the virus matches its host name it uses to the PTR record that exists for the IP address, most residential Internet providers use easy to find identifiers as part of the PTR record’s canonical name that a spam filter can quickly spot and flag the message as spam. For example, such a PTR record might look like this:

192-168-23-147.dhpc.region.state.provider.com

Of course, business Internet connections often have the right to set the PTR records, as do organizations using dedicated or collocated servers.  And if a system on one of these IP addresses is compromised, then the spammers can use that system and it’s correct PTR record to deliver their spam.  Likewise, if a virus is able to take control of a home computer’s email program, it can send spam through the Internet provider’s email system, again bypassing the PTR record check.  So using PTR records is by no means a complete spam defense, but it’s an important one that’s used by almost every mail server today.

In my previous article, I introduced the NTP Pool Project as the recommended alternative to Microsoft’s time.windows.com public NTP service, and described how to configure standalone Windows servers to update their clocks from external NTP servers.  But on a standalone server, allowing the clock to drift out of sync, even days or weeks out of sync, is really more of an annoyance than a problem.  (In most cases.  But if a standalone server uses an external service that depends on time synchronization, then, yes, it’s a problem, not an annoyance.)  The same is not true for any server or workstation that is a member of an Active Directory (AD) domain.

Everything involving authentication within an AD domain depends on the kerberos tickets issued to computers and users by the domain controllers.  All kerberos tickets are time-sensitive, a feature designed to prevent an old kerberos ticket from being used to breach security.  On Windows, all kerberos tickets issued are valid for 5 minutes.  As long as all clocks on all the computers in an AD domain are within this 5 minute window, everything works just fine.  But if one computer’s clock drifts more than 5 minutes from the clocks on the other computers, especially the domain controllers, then authentication failures are not only likely, they’re almost certain.

This is known as a clock skew error and will be recorded in the Event Log with a “clock skew too great” error.  When this happens, whatever the computer or user was trying to access will not be allowed.  To prevent this, you must ensure that all the computer clocks in the AD domain are in sync.

The server that’s in charge of providing a reliable time source to an AD domain is the domain controller that holds PDC Emulator flexible single master of operation (or FSMO, pronounced “fizmo”).  (PDC stands for Primary Domain Controller and is a holdover from Windows NT domains.)  When a domain member computer queries the AD DNS servers for the NTP time server, the address of the PDC domain controller is one that’s returned.

The first task is to make sure that the PDC domain controller is updating time from a reliable external source.  And the NTP Pool Project is the source that I recommend.  To configure the Windows Time service on the PDC domain controller, we once again turn to the W32TM command.  This time, setting the NTP servers in the “Internet Time” tab of the Date and Time applet of the Control Panel won’t work since we need to options not available in this applet.  This is the command to use on the PDC domain controller (this command is one line and broken into two lines here for clarity):

w32tm /config "/manualpeerlist:0.us.pool.ntp.org 1.us.pool.ntp.org
   2.us.pool.ntp.org 3.us.pool.ntp.org" /syncfromflags:manual /reliable:yes /update

Here is what the options mean:

/config  -  queries or updates the Windows Time Service configuration

/manualpeerlist  -  specifies the NTP servers to use.  This is a space-delimited list of DNS and/or IP addresses and must be enclosed in quotes when more than one server is specified.

/syncfromflags  -  sets what sources should used.  Choices are MANUAL (use the manual peer list), DOMHIER (use an Active Directory domain controller), ALL (use both sources), or NO (don’t sync from an NTP server).  This must be set to manual because the PDC domain controller is the time source for the AD domain.  It cannot update its time from the domain.  When you think about this for a moment, you’ll understand.

/reliable  -  specifies if the server can be used as a NTP time source for other computers.  Again, this must be set to yes since it’s the time source for the domain.

/update  -  commits the changes.

For the configuration changes to take effect, you have to restart the Windows Time service, either from the Services MMC or using the NET commands as shown below:

net stop w32time
net start w32time

The other domain controllers for the AD domain (the ones that don’t hold the PDC Emulator FSMO) should be set to update time from the from the PDC domain controller.  This way, if time on the PDC domain controller drifts, the entire domain will drift with it.  For the kerberos tickets, it’s important that time on all the computers in the domain be in sync.  It’s the time relative to these computers that determines clock skew, not time from outside the domain.  This the command to use on these domain controller:

w32tm /config /syncfromflags:domhier /reliable:no /update

Here the /syncfromflags is set to DOMHIER, so it will use the PDC domain controller as the NTP source, and /reliable is set to NO because they don’t hold the PDC Emulator FSMO and therefore are not the reliable time source for the domain.  Once again, you need to restart the Windows Time service as shown above.

All member servers and workstations are automatically set to update time from the PDC domain controller when they are joined to the domain.  But if you need to set this, use this command, which works on Windows XP and higher:

w32tm /config /syncfromflags:domhier /update

One thing to note is that the PDC domain controller is the reliable time source for the AD domain.  In forests that have more than one AD domain, each domain has it’s own PDC domain controller, and therefore its own reliable time source.  Make sure that all of the PDC domain controllers for in the forest use the same external NTP service.  If not, it’s possible that time between the domains will drift out of sync, and kerberos tickets used to authenticate between domains will not be honored because of clock skew errors.

How Windows servers update, or does not update, their clocks doesn’t appear to be well understood among some administrators. In this article, I’m going to cover time on standalone (or single – couldn’t resist not using this title) Windows servers.  A standalone server is one that is not a member of an Active Directory domain.  I’ll cover time on domain servers in the next article.

Installed out of the box, every Windows server is set to update its time from time.windows.com.  This has rapidly become one of the most overloaded public time services on the Internet.  I’ve encountered countless errors in the System Event Log where the server was unable to contact time.windows.com.  The key to good timekeeping on Windows, therefore, is to find another reliable time source (short of purchasing your own Stratum 2 time server).

Naturally, the Internet long ago solved this problem with the NTP Pool Project.  It describes itself as “a big virtual cluster of timeservers providing reliable easy to use NTP service for millions of clients.”  These are systems located around the world acting as highly accurate time servers that allow anonymous connections via the Network Time Protocol (NTP).  The load is distributed across all the servers, so every time you connect, it will be to a different pool server.  All of which is handled through the magic of DNS.

The host name you use for the NTP Pool Project server allows to use any of the servers located around the world, or to restrict you to servers located in a particular country or in a particular region.  Naturally, there are more servers located in some countries than others (United States, Germany, France, and United Kingdom are the top four, in that order), so in the US or UK, you can safely restrict yourself to your country.  But in most other places, you’ll want to use the bigger regional pool or the entire world-wide pool.  This is especially true for Africa (15 servers), South America (43 servers), and Oceania (89 servers).  The country pools use the two-letter country code in the host name, and the regional pools use the region’s name in the host name.  If you exclude the country or region in the host name, all the servers in the world-wide pool are available to you.

Setting a Windows server to use the NTP Pool Project servers (or any other NTP server, for that matter) is best done using the W32TM command.  You can set the NTP server from the “Internet Time” tab of the Date and Time applet in the Control Panel, but the W32TM allows to set multiple time servers and configure other options.  This command, which works with Windows Server 2003 and higher, will set the server to update time from the NTP Pool Project US servers (this command is one line and broken into two lines here for clarity):

w32tm /config "/manualpeerlist:0.us.pool.ntp.org 1.us.pool.ntp.org
    2.us.pool.ntp.org 3.us.pool.ntp.org" /syncfromflags:manual /update

Here is what the option mean:

/config  -  queries or updates the Windows Time Service configuration

/manualpeerlist  -  specifies the NTP servers to use.  This is a space-delimited list of DNS and/or IP addresses and must be enclosed in quotes when more than one server is specified.

/syncfromflags  -  sets what sources should used.  Choices are MANUAL (use the manual peer list), DOMHIER (use an Active Directory domain controller), ALL (use both sources), or NO (don’t sync from an NTP server).  The DOMHIER option only makes sense on a server joined to a domain.

/update  -  commits the changes.

After you change the NTP server list, you have to restart the Windows Time service, either from the Services MMC or using the NET commands as shown below:

net stop w32time
net start w32time

NOTE:  On Windows Server 2008 and higher, the Windows Time service is not set to automatically run on standalone servers.  You need to change the Startup Type in the service properties.

You can verify that your server is updating its time from the NTP Pool Project servers by looking for Time-Service events (Event ID 37) in the System Event Log.

Windows Time Update Event - Event ID 37

 

Time on standalone Windows servers is not as critical as it is on domain member servers and workstations, as we will see in the net article.  But having the correct time makes everything that uses a timestamp (and there are a lot of them) much easier to understand, especially when troubleshooting problems that only happen during a particular time.