Security of PWDCOMPARE and SQL Hashing

Few people are as concerned about security as they should be, and fewer still are proficient in it. Even if you are, while you might be able to tackle the major security flaws, there’s always going to be something new that can get you, unless you implement an unhackable system – turned off and unplugged.

In SQL 2008, Microsoft added an interesting new function PWDCOMPARE. The idea was to provide DBAs with the ability to check if existing passwords were too simple, then require them to be changed. I was immediately concerned after reading about this. Researching the topic for this post actually alleviated most of my concerns, but there are a few things to know.

Function Explanation
PWDCOMPARE works by comparing a provided plain-text password against a password hash, both of which are provided as parameters to the function. If the result returns a value of 1, the password matches, while a value of 0 means the password is incorrect. You have to type the password exactly to get a match, that doesn’t seem so dangerous, right?

SELECT
name
,password_hash
,is_disabled
,[Pwd] = 'App!3'
,[IsMatch] = PWDCOMPARE('App!3',password_hash)
FROM sys.sql_logins

All users have access to query sys.sql_logins. Also, by default, they can see their own login as well as the SA account. We all (should) know that best practice is to disable and rename the SA account. (Just knowing it’s the right thing to do isn’t enough though, you should also be doing it!) Renaming the account doesn’t help here; users can still see the account with principal_id = 1, regardless of what it’s called and whether or not it’s disabled.

Now let me walk you through my original concern.

Curious User George logs in and performs a query on sys.sql_logins. He notices that the SA account is visible and someone accidentally left it enabled. Curious User George happens also to be Malicious User George. He sees a password_hash column in his query results. Here’s where SQL redeems some security though. If a user does not have the permission CONTROL SERVER, this field is left NULL. George cannot even see his own password hash.

SelectNullPassSA

When George attempts to use PWDCOMPARE, the result is also NULL. Crisis averted, basic users cannot decrypt passwords this way.

CompareNull

However, all those elevated users that should not exist, but do, in small companies? They have the power. They already have all the power, so this happens to be one of the smallest concerns.

PWDCOMPARE Concerns
MSDN explicitly states that “The PWDCOMPARE function is not a threat against the strength of password hashes because the same test could be performed by trying to log in using the password provided as the first parameter.”

However, if you try to login with a bad password, you should get an alert. I’m assuming you are auditing failed logins? If you try to do it repeatedly, you’ll be locked out of the account. Again, assuming routine security practices are in effect. If you compare a billion passwords using PWDCOMPARE, no alerts are fired, no one gets locked out. Even if it takes forever. How is that the same thing?

I created a temp table in six seconds that contained a million randomly generated passwords. It took 12-15 minutes to compare seven million rows and return the one password that matched. This really isn’t a viable method due to the time it takes for anything other than comparing some common passwords. But then again, all it took was a process running for a fraction of my day on a small development box. A targeted list will have significantly better results than using random GUIDs like in my below example.

Create a temp table with a million random passwords (no not reasonable passwords, just passwords)

WITH Test AS
(
SELECT [ID] = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
)
SELECT TOP 1000000
ID,[Pass]=NEWID()
INTO #Password
FROM Test

Find weak passwords; provide script to force users to change
This script will provide you with scripts that will force users with discovered passwords to change their password when next logging in. It’s still up to you to educate them on better password policies though.

SELECT
	 name
	,Pass
	,[IsMatch] = PWDCOMPARE(Pass,password_hash)
	,[ForceChange] = 'ALTER LOGIN ' + QUOTENAME(name) + '
	 WITH PASSWORD = ''' + Pass + ''' MUST_CHANGE,CHECK_POLICY=ON'
FROM sys.sql_logins
	CROSS JOIN #Password
WHERE PWDCOMPARE(Pass,password_hash)=1

Hashing Concerns
My friend, integgroll, is a Penetration Tester. As suggestive as that title is, he is essentially an Ethical Hacker. When I asked him about cracking passwords, he provided me with a lot of interesting information. There are quality, free programs out there that will force a password in less time and with less effort than using PWDCOMPARE, and without using SQL Server to even test against. Such a test/attack is especially effective if you use a Mask Attack (targeted list) instead of just a simple Brute Force Attack (every possible combination).

SQL Server uses the twelve-year-old encryption method of SHA-512, which is slowly being broken through collision attacks. SQL appends a 32-bit salt to complicate the hashed password. However, my friend pointed out that salting does not mean that passwords are suddenly unable to be cracked, it just takes more time. With a salted hash, he estimated it would only increase the time required to force the password by around 20%. Not too bad when most users follow a password pattern and you can potentially test 414 million combinations a second if you have a quality graphics card to boost Hashcat’s performance.

With sufficient permission, one could viably steal any hashed password this way. Just another reason to stick to Windows Logins. Windows logins do not display at all when using sys.sql_logins. They only return by using sys.syslogins, which still has a password field, but this is left NULL for windows accounts, keeping the hash secure (at least from SQL).

I have yet to rationalize why we need to see a password hash at all. So long as there are password complexity requirements, I’d rather keep the chances for stealing and cracking passwords to a minimum, meaning that I’d rather not be able to see a plain-text or even a hashed password of any user.

EDIT: It was pointed out to me, and then I immediately remembered I’ve done it many times in the past, that password hashes let us script out logins without knowing the original password. I didn’t think about that while posting this, probably because I was so caught up in the disturbingly simple methods of password cracking.

The most important thing you can do here is improve password strength. Decrypting a hashed password is disturbingly easy and quick, but each digit you add to a password exponentially increases the cracking time required.

Administrators:

  • Implement a minimum password
    • Eight digits is too short for a reasonably secure password, more is better
  • Do not have a maximum length (or at least make it huge)
    • If users want a 75 digit password, let them. Their password will be more secure than your admin account.
  • Allow special characters and numbers
    • This substantially increases possibilities and thus increases cracking time, anytime I see policies which still only allow alpha character, I get angry or depressed – depending on how much energy I have.
  • Do not advertise your password policy on failed login or password creation
    • Displaying the policy is convenient for users, but it also lets hackers know how they can structure their Mask Attack
    • Controversial and may not really help vs. a determined Mask Attacker

Users:

  • Every digit added to your password makes it exponentially harder to crack
  • Do not follow common patterns!
    • Timothy1950 is a common password pattern. Most passwords only contain a capital letter for the first value. Mix things up, don’t end with a year. Even changing the password to 19Tim50othy makes a Mask Attack a lot more time-consuming.
  • Do not share passwords between accounts
    • If one is cracked, hackers normally test other popular websites
    • Even if you follow a pattern, they now have a very powerful Mask Attack to use against you – changing a number or two is easier than guessing 15 characters.
Advertisements

Monitoring Backup and Restore Times

Backups and restores are arguably the most important aspect of a DBA’s job. There are few times when a manager will be breathing down your neck more impatiently than during a restore process. Of course, one of the worst things you can say during such an event is, “I have no idea when it will be complete, maybe another ten minutes?” When the process is still running twenty minutes later, it’s difficult to say if the fury will ever end. Let’s explore some obvious and not-so-obvious methods to monitor progress.

GUI Luckily, the GUI Backup/Restore operation has you partially covered. When you start a process with it, the completion percentage will be automatically tracked in the bottom left hand corner, but you surely already know this. While this does not supply you with a time estimation, at least there is something to track.

ProgressBar

Scripted A scripted backup/restore operation using the GUI Script Action reveals the option that provides the progress bar, STATS. The default value is 10, which increments in, no surprise, values of ten percent. The value can be delayed on tiny databases.  If you are getting such odd numbers, it’s probably processing so fast that no one is concerned with completion time anyways. Exceptionally long backups might warrant a STATS value of 5, but personally, I’ve always used 10.

Sample Backup Script Using STATS

BACKUP DATABASE [ToBeDeleted]
TO DISK = N'C:\SQL\BACKUP\ToBeDeleted.BAK'
WITH COPY_ONLY, RETAINDAYS = 1, NOFORMAT, NOINIT,
NAME = N'ToBeDeleted-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

If you write a backup/restore job by hand, always include the STATS option. It can save you from going mad during a long operation. What do you do if you forgot to include it though? Sitting around for an hour or more waiting blindly on a restore is a horrible situation. There will be much wailing and gnashing of teeth, at best.  Did it hang? Is it just verifying now? Who knows!?

With a little bit of math and estimation using the progress, (mathimation perhaps?) you can predict a fairly accurate completion time. That estimation may be wildly wrong though, and not many people estimate well under pressure. The last thing you want to do is under-estimate when the entire company is waiting for your magical fix.

All that being said, if you are creating a backup/restore job that will be automated via a job, STATS should not be used. You aren’t actively monitoring it, so all it would do is fill the logs with relatively useless information. On a related note, you should consider using Trace Flag 3226 to suppress successful backup messages as well. Scheduled backups can fill an error log, stealing precious space while making it hard to find the real problems.

Monitoring The solution to this lies in a short T-SQL script. Open a query window and paste the following script in. It will provide you with any databases currently being backed up or restored along with some useful information.

USE [master]
GO
SELECT
	 [Database] = d.name
	,[RunningMin] = r.total_elapsed_time / 60000
	,[RemainingMin] = r.estimated_completion_time / 60000
	,[EstimatedCompletion] = DATEADD(MILLISECOND,estimated_completion_time,GETDATE())
	,[%Complete] = r.percent_complete
	,[Command] = t.[text]
FROM
	sys.databases d
	INNER JOIN sys.dm_exec_requests r ON r.database_id = d.database_id
	CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) t
WHERE
 	r.command LIKE '%backup%'
	OR r.command LIKE '%restore%'
GO

Some of the fields are self-explanatory. The current duration and estimated remaining duration default to milliseconds, so they are converted to minutes in the script. The estimated time of completion is a convenience column, because when I’m under pressure, I don’t normally add times perfectly. The command is especially useful when you were not the one who originated the process but need to verify that the script is not poorly performing due to the script used.

Remember, the estimates on this script will not be 100% perfect, its based on the I/O being performed at query time along with expected I/O. There are many things that can affect the duration, such as I/O throughput, compression, encryption, and contention. Even so, the estimates should be closer than anything you would guess by pulling a random number out of the air. Saying, “Sql estimates another 30.5 minutes” carries a lot more weight than, “I feel that based on the winds and astrological alignment that it should be completed in 23 minutes.”

Script Explanation The sys.databases view is one that everyone should be very familiar with and using rather than the deprecated sysdatabases, but the other two objects might be a bit more mysterious. The first Dynamic Management View, dm_exec_requests, has awesome troubleshooting potential. The view returns all commands that are currently being processed by SQL Server, so you could use it to find long running processes, specific queries, etc. The other object, dm_exec_sql_text, is a Dynamic Management Function and is used to find the full sql script executed using the sql_handle parameter. In this usage, the sql_handle is supplied from dm_exec_requests command field through a CROSS APPLY.

Apply was added in 2005 (along with the DMOs), but is largely still a mystery to many people. I admit, I still don’t understand the optimal times to use the operator, but per TechNet, the primary purpose is to invoke table-valued functions for each row returned by a query. Well, that’s exactly what we need here.  Apply also has some great applications for TOP X queries and aggregates, but that’s outside the scope of this article. Using apply here is cleaner than using an inline subquery. Strictly speaking, the performance shouldn’t matter on such a small script, but it’s best to learn as much as possible. Finally, the script is filtered through the WHERE clause to only return backup and restore commands. If you leave that filter off, you’ll end up returning every currently processing command on the server, again, not the intention of this solution.

Summary A colleague tipped me off to this monitoring script while I was waiting for a particularly large backup to finish so I could set up mirroring. I made a few modifications, including the cross apply and estimated completion time. You should make your own variations to suit your environment and tastes. I’m just happy to have a way to check progress in case I forget to throw STATS into a hastily scripted restore operation in the middle of the night.

 

T-SQL Tuesday #065 – Teach Something New – APPLY

TSQL2sDay150x150_388014A5

This is my second contribution to T-SQL Tuesday. This month’s topic is hosted by Mike Donnelly (blog | twitter) with the subject of learning/teaching something new.

The topic this month is straight forward, but very open ended. You must learn something new and then write a blog post explaining it. One of the reasons I present and blog is because it forces me to really learn a subject if I am going to explain it to someone else. I am now giving all of you that same opportunity. You’re welcome.

I didn’t see this topic until Friday afternoon, so luckily it is a very open-ended topic. I have to find a better way to track T-SQL Tuesday. Right now the plan is to hope that I see something appear about it in my Twitter Feed. Anyways, I had to scramble together an idea while finding something I could learn and write about in one day. I recently scheduled another article that used the APPLY operator in one of the examples, but it’s something I have yet to use much. This seemed like a great time to explore it.

APPLY has two basic forms, CROSS APPLY or OUTER APPLY. CROSS APPLY works essentially like an INNER JOIN, whereas OUTER APPLY is like a LEFT OUTER JOIN. The primary purpose of the APPLY operator is to evaluate a Table-Valued Function against every row returned by a query. The expression acts as the left input while the function acts as the right input. OUTER applies will return NULL values on the column evaluated by the function and CROSS applies will exclude NULL results – again, just like joins.

The following example is fairly simple, but it displays the SQL script used for currently running processes. The CROSS APPLY example will return fewer results as it filters spids without a sql_handle, basically system processes. The OUTER APPLY will still return the processes, just with a NULL Text column. The APPLY operator allows you to avoid performing subqueries to evaluate Table-Valued Functions.

1) APPLY for Table-Valued Functions

CROSS APPLY

SELECT
 session_id,[status],blocking_session_id,wait_type,
 wait_time,wait_resource,transaction_id,t.[text]
FROM sys.dm_exec_requests r
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) t

OUTER APPLY

SELECT
 session_id,[status],blocking_session_id,wait_type,
 wait_time,wait_resource,transaction_id,t.[text]
FROM sys.dm_exec_requests r
 OUTER APPLY sys.dm_exec_sql_text(sql_handle) t

2) Reuse Column Aliases

First we will create a temp table with some numbers for sample data.

CREATE TABLE #Numbers (Num1 INT NOT NULL,Num2 INT NOT NULL)
INSERT INTO #Numbers VALUES (1,3),(3,5),(5,7),(10,12)

Traditional Script (No Aliasing)

SELECT Num1,Num2,
[SumNums] = Num1+Num2,
[SumNumsTimes3] = (Num1+Num2)*3,
[SumNumsTimes3Plus2] = (Num1+Num2)*3+2
FROM #Numbers

Window Function to Reuse Column Aliases

WITH Alias AS
(
SELECT Num1,Num2,
[SumNums] = Num1+Num2
FROM #Numbers
)
SELECT Num1,Num2,
SumNums,
[SumNumsTimes3] = SumNums*3,
[SumNumsTimes3Plus2] = SumNums*3+2
FROM Alias

APPLY to Reuse Column Aliases

SELECT Num1,Num2,
SumNums,
SumNumsTimes3,
[SumNumsTimes3Plus2] = SumNumsTimes3+2
FROM #Numbers
CROSS APPLY (SELECT Num1 + Num2) a (SumNums) 
CROSS APPLY (SELECT SumNums * 3) b (SumNumsTimes3)

Obviously if you have to calculate anything complex, not being able to use a column alias would require a lot of copying and pasting and room for error. Using a CTE to alias requires nested CTEs for multiple aliases and much more typing. Using an APPLY operator provides the most flexibility and could provide a lot of code reuse.

3. Unpivot

Again, first thing is to create some sample, denormalized data.

CREATE TABLE #ContactInfo
(
 ContactInfoID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 Phone1 INT, Phone2 INT, Phone3 INT,
 Email1 VARCHAR(50), Email2 VARCHAR(50), Email3 VARCHAR(50)
)
INSERT INTO #ContactInfo VALUES
(5555510,5555511,5555512,'ASmith@Test.com','ASmith1@Test.com','ASmith2@Test.com'),
(5555520,5555521,5555522,'BJones@Test.com','BJones1@Test.com','BJones2@Test.com'),
(5555530,5555531,5555532,'CDoe@Test.com','CDoe1@Test.com','CDoe2@Test.com')

Traditional Unpivot

SELECT
ContactInfoId,ContactPriority,PhoneNumber,EmailAddress
FROM (SELECT * FROM #ContactInfo) p
UNPIVOT ( PhoneNumber FOR ContactPriority IN (Phone1,Phone2,Phone3) ) up1
UNPIVOT ( EmailAddress FOR EPriority IN (Email1,Email2,Email3) ) up2
WHERE RIGHT(EPriority,1) = RIGHT(ContactPriority,1)

A basic unpivot won’t work here, as we need to unpivot multiple columns. Luckily you can unpivot twice, but the syntax is a bit weird and you’ll probably have to look it up every time you try to unpivot like this. The WHERE clause at the end filters the results so the result doesn’t become a cartesian join. The RIGHT operator has the results match on just the numerical values of the Email/Phone Priority.

Unpivot Using Apply

SELECT
ContactInfoId,ContactPriority,PhoneNumber,EmailAddress
FROM #ContactInfo
CROSS APPLY (VALUES(1,Phone1,Email1),
(2,Phone2,Email2),
(3,Phone3,Email3)) p (ContactPriority,PhoneNumber,EmailAddress)

Unpivoting this way might take a bit of practice, but the syntax seems a bit more intuitive to me for occasional use. Just remember that you have to define the new column names at the end of the statement. This scales a lot smoother than using unpivot.

4. Select TOP N Results

Using Window Functions

WITH Columns AS (
SELECT
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,
[RN] = ROW_NUMBER() OVER(PARTITION BY TABLE_SCHEMA,TABLE_NAME ORDER BY ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
)
SELECT
t.TABLE_SCHEMA,t.TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.tables t
INNER JOIN Columns c ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE c.RN <=3

Using APPLY

SELECT
t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME
FROM INFORMATION_SCHEMA.tables t
OUTER APPLY (SELECT TOP 3 *
FROM INFORMATION_SCHEMA.columns c
WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
) c

Comparing syntax alone, APPLY is a bit easier to type out, but performance-wise, it depends on your use case, SQL version, etc. I’m used to writing CTEs, so I’ll probably stick with that syntax.

Summary These were all the uses I’ve been able to find so far for the APPLY operator, but please feel free to identify any others you know of. Sometimes the syntax using APPLY is more intuitive than the alternative, but the main use is definitely the originally intended purpose, evaluating Table-Valued Functions.

PowerShell Solution to Java Version

I have to verify many patch statuses each month. A few days ago, I needed to get the current version of Java installed on all of my servers. Obviously I wasn’t going to log into every box, so I needed a remote method. Of course PowerShell could come to my rescue, but which method would be best?

After a little bit of research online, I discovered that there are numerous of ways to find the version of Java installed, but these are not all created equally, I’ve mentioned a few below, along with my final script at the bottom.

WMI Performing a WMI query was by far the worst method to get Java. I can’t actually tell you if it works, because the query did not complete. I even tried running it again the entire time I was writing this post. I didn’t get to the point of trying to filter or refine the result to fit my needs, let alone make it work remotely. Just pass over this code and continue to the useful ones below.

Get-WmiObject -Class Win32_Product | Where { $_.Name -like "Java" }

Registry Key Reading the registry though Get-ItemProperty is quick and easy if you want to pull the local server. It gets much tougher if you want to read remotely though. My remote registry read will work here as well, but the registry key was not showing the version level detail I needed. Java 1.8.0_xx isn’t enough, I need to know each computer has Java 8.0.xxx.xx installed; I need the build number.

Local Only Query

$Key ='HKLM:\Software\JavaSoft\Java Runtime Environment'
(Get-ItemProperty -Path $Key -Name Java7FamilyVersion).Java7FamilyVersion

Remote Query

$Computer = "$Env:ComputerName"
$Reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine',$Computer)
$RegKey= $Reg.OpenSubKey("Software\JavaSoft\Java Runtime Environment")
$RegKey.GetValue("Java7FamilyVersion")

Get-ChildItem The idea of this method seemed strange to me, but it actually worked the best, so it’s what I used for my final solution. In its purest form, you just get the details of the java executable and then display the version info.

(Get-ChildItem "C:\Program Files\Java\jre*\bin\java.exe").VersionInfo.ProductVersion

Below is my full version of the script, designed with remote querying in mind and error handling.

Final Solution

# Update $Computers to reflect your server list
$Computers = (Get-Content C:\Users\$Env:UserName\Desktop\AllServers.txt)
ForEach ( $Computer in $Computers )
{
$PingStatus = Gwmi Win32_PingStatus -Filter "Address = '$Computer'" | Select-Object StatusCode
IF ($PingStatus.StatusCode -eq 0)
{
IF (Test-Path ("\\$Computer\C$\Program Files\Java\jre*\bin\java.exe"))
{
$Java = gci "\\$Computer\C$\Program Files\Java\jre*\bin\java.exe"
$Java | Select @{n='Computer';e={$Computer}},@{n='JavaVersion';e={$java.VersionInfo.ProductVersion}}

}
ELSEIF (Test-Path ("\\$Computer\C$\Program Files (x86)\Java\jre*\bin\java.exe"))
{
$Java = gci "\\$Computer\C$\Program Files (x86)\Java\jre*\bin\java.exe"
$Java | Select @{n='Computer';e={$Computer}},@{n='JavaVersion';e={$java.VersionInfo.ProductVersion}}

}
ELSE
{
$Computer | Select @{n='Computer';e={$Computer}},@{n='JavaVersion';e={'-'}}
}
}#EndIf
ELSE
{
$Computer | Select @{n='Computer';e={$Computer}},@{n='JavaVersion';e={'ConnectFailed'}}
}
}#EndForEach

Most of the final script is just error handling. My first If statement tests if the server is pinging, then If statement nested within that tests for Java on a 64 or 32 bit system. This way, I know what version of Java is installed, if no Java is installed, or if the connection failed entirely.

Getting Cluster Nodes through T-SQL

One of my most frustrating moments is when I need to log into the active node of a cluster. I inevitably choose a passive node first, then wind up opening the Failover Cluster Manager to find the active node then finally log in to the correct server. Well, I finally took some time to get it right by using my trusty Central Management Server and T-SQL.

Running all of these scripts on a CMS will allow you to hit every SQL server in your domain. When I first decided to pull Server data using a script, I immediately thought of @@SERVERNAME. However, this returns the cluster name (for clusters), not the active server node. What I really needed took a minor amount of research.

Another possible choice included sys.dm_os_cluster_nodes, but this had surprising limitations. While it does provide node names, if you try to query all servers using a CMS, the script can fail to merge the result sets, displaying the following error message. In my case, it also failed to return results for one entire cluster.

An error occurred while executing batch. Error message is: The result set could not be merged because the result schema did not match the schema from the first responding server.

The next option was to use SERVERPROPERTY to find the actual server name hosting SQL. When using a CMS, the script will also return the results of @@SERVERNAME as a bonus.

SELECT [ActiveServer] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

While this is good enough for some scenarios, I wanted a more targeted answer, so I kept digging. First, I wanted a script that would only show the active nodes in each cluster. I’ll mostly use this query to ensure I’m avoiding the primary server during maintenance and to verify which server is the active node when developers or management start asking questions and want immediate answers. (You know, as opposed to when they aren’t in a hurry…)

To get the exact results I want will require a bit of string parsing. I suggest you review SUBSTRING and CHARINDEX if you aren’t positive on how they work. In short, Substring will display the server name up to where Charindex determines the ‘\’ appears. This will provide a column consisting of only the cluster name. The next column will display the active node. I then filter out all servers other than clusters and exclude default instance names as well.

SELECT
[ClusterName] = SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME))
,[ActiveNode] = SERVERPROPERTY('ComputerNamePhysicalNetBIOS')
WHERE SERVERPROPERTY('ComputerNamePhysicalNetBIOS') <> SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME))
AND @@SERVERNAME <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

This final query returns every node in each cluster, with the last column denoting the active node. Useful when you need to remember which servers belong to what and your documentation has gone missing – or doesn’t exist yet.

SELECT
[ClusterName] = SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME))
,[Nodes] = NodeName
,[IsActiveNode] = CASE WHEN NodeName = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') THEN '1' ELSE '' END
FROM sys.dm_os_cluster_nodes
WHERE SERVERPROPERTY('ComputerNamePhysicalNetBIOS') <> SUBSTRING(@@SERVERNAME,0,CHARINDEX('\',@@SERVERNAME))
AND @@SERVERNAME <> SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

Summary I shared these scripts with my colleagues, who were very excited about not having to play “Guess the Active Node” anymore. There might be more eloquent ways to determine cluster nodes, but this method is perfect for my environment. I suggest saving the query to a code repository project in Solution Explorer or as a Snippet so that it’s always easily accessible.

I’m participating in the many SQL Blog challenges this month, and I’d like to thank each of them for the push and community support they provide.