Skip to content

SQLCheck Account Fixes #83

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
Malcolm-Stewart merged 1 commit into from
Dec 5, 2024
Merged

SQLCheck Account Fixes #83

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Binary file modified SQLCheck/.vs/SQLCheck/v15/Server/sqlite3/storage.ide
View file
Open in desktop
Binary file not shown.
76 changes: 51 additions & 25 deletions SQLCheck/SQLCheck/Collectors.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -2190,6 +2190,7 @@ public static void CollectService(DataSet ds) // collects select services we ar
ManagementObjectSearcher searcher = null;

DataRow Computer = ds.Tables["Computer"].Rows[0];
DataRow Domain = ds.Tables["Domain"].Rows[0];
DataRow Service = null;

//
Expand Down Expand Up @@ -2229,7 +2230,12 @@ public static void CollectService(DataSet ds) // collects select services we ar
Service["Description"] = description;
Service["Path"] = mo.GetPropertyValue("PathName").ToString();
Service["ServiceAccount"] = mo.GetPropertyValue("StartName").ToString();
Service["DomainAccount"] = Utility.TranslateServiceAccount(Service["ServiceAccount"].ToString(), Computer["NETBIOSName"].ToString());

// better method of converting
// Service["DomainAccount"] = Utility.TranslateServiceAccount(Service["ServiceAccount"].ToString(), Computer["NETBIOSName"].ToString());
string NTAccountName = Utility.NormalizeNTAccount(Service["ServiceAccount"].ToString(), "");
Service["DomainAccount"] = Utility.ConvertLocalAccountToDomainAccount(NTAccountName, Computer.GetString("NETBIOSName"), Domain.GetString("DomainShortName"));

string startMode = mo.GetPropertyValue("StartMode").ToString();
Service["StartMode"] = startMode;
bool started = mo.GetPropertyValue("Started").ToBoolean();
Expand Down Expand Up @@ -2288,7 +2294,12 @@ public static void CollectService(DataSet ds) // collects select services we ar
Service["Path"] = mo.GetPropertyValue("ExecutablePath").ToString();
mo.InvokeMethod("GetOwner", owner);
Service["ServiceAccount"] = $@"{owner[1]}\{owner[0]}";
Service["DomainAccount"] = Utility.TranslateServiceAccount(Service["ServiceAccount"].ToString(), Computer["NETBIOSName"].ToString());

// better method of converting
// Service["DomainAccount"] = Utility.TranslateServiceAccount(Service["ServiceAccount"].ToString(), Computer["NETBIOSName"].ToString());
string NTAccountName = Utility.NormalizeNTAccount(Service["ServiceAccount"].ToString(), "");
Service["DomainAccount"] = Utility.ConvertLocalAccountToDomainAccount(NTAccountName, Computer.GetString("NETBIOSName"), Domain.GetString("DomainShortName"));

Service["StartMode"] = "Manual";
Service["Started"] = true; // boolean
Service["Status"] = "Running";
Expand Down Expand Up @@ -2365,9 +2376,10 @@ public static void CollectSPNAccount(DataSet ds)

foreach (string account in serviceAccounts)
{
// split the account apart
// split the account apart - these have all been normalized to domain\account, including UPN and local accounts
acct = account;
domain = "";
domain = SmartString.ChopWord(acct, ref acct, @"\");
/*
if (acct.Contains(@"\"))
{
domain = SmartString.ChopWord(acct, ref acct, @"\");
Expand All @@ -2381,24 +2393,29 @@ public static void CollectSPNAccount(DataSet ds)
// domain = Computer["ExpandedName"].ToString();
domain = Domain["DomainName"].ToString();
}
*/

try
{
// treat the account as a user account first
tempAccount = acct;
searcher = new DirectorySearcher(new DirectoryEntry($@"LDAP://{domain}"), $"samAccountName={tempAccount}", new string[] { "AdsPath", "cn" }, SearchScope.Subtree);
// treat the account as a user account first - we are no longer repressing the trailing $ so no need to test both ways
// tempAccount = acct;
// searcher = new DirectorySearcher(new DirectoryEntry($@"LDAP://{domain}"), $"samAccountName={tempAccount}", new string[] { "AdsPath", "cn" }, SearchScope.Subtree);
searcher = new DirectorySearcher(new DirectoryEntry($@"LDAP://{domain}"), $"samAccountName={acct}", new string[] { "AdsPath", "cn" }, SearchScope.Subtree);
results = searcher.FindAll();
if (results.Count == 0) // treat the account as the machine account if user account search fails
{
results = null;
searcher.Dispose();
tempAccount += "$"; // machine accounts have $ suffix for searching
searcher = new DirectorySearcher(new DirectoryEntry($@"LDAP://{domain}"), $"samAccountName={tempAccount}", new string[] { "AdsPath", "cn" }, SearchScope.Subtree);
results = searcher.FindAll();
}
/*
if (results.Count == 0) // treat the account as the machine account if user account search fails
{
results = null;
searcher.Dispose();
tempAccount += "$"; // machine accounts have $ suffix for searching
searcher = new DirectorySearcher(new DirectoryEntry($@"LDAP://{domain}"), $"samAccountName={tempAccount}", new string[] { "AdsPath", "cn" }, SearchScope.Subtree);
results = searcher.FindAll();
}
*/

// if not found log a message and continue around the loop - the foreach won't do anything in that case
if (results.Count == 0) Computer.LogCritical($"No accounts in domain '{domain}' have the samAccountName of '{acct}' or '{acct}$'.");
// if (results.Count == 0) Computer.LogCritical($"No accounts in domain '{domain}' have the samAccountName of '{acct}' or '{acct}$'.");
if (results.Count == 0) Computer.LogCritical($"No accounts in domain '{domain}' have the samAccountName of '{acct}'.");

// process results
foreach (SearchResult result in results)
Expand All @@ -2408,8 +2425,12 @@ public static void CollectSPNAccount(DataSet ds)
SPNAccount = ds.Tables["SPNAccount"].NewRow();
ds.Tables["SPNAccount"].Rows.Add(SPNAccount);
entry = result.GetDirectoryEntry();
SPNAccount["Account"] = tempAccount;
SPNAccount["Domain"] = Domain["DomainShortName"].ToString();
// SPNAccount["Account"] = tempAccount;
SPNAccount["Account"] = acct; // split from DomainAccount
// SPNAccount["Domain"] = Domain["DomainShortName"].ToString();
SPNAccount["Domain"] = domain;
// split from DomainAccount
SPNAccount["DomainAccount"] = account;
SPNAccount["DistinguishedName"] = entry.Path;
UAC = entry.Properties["UserAccountControl"][0].ToInt();
SPNAccount["UserAccountControl"] = $"{UAC} (0x{UAC.ToString("X")})";
Expand Down Expand Up @@ -2514,7 +2535,7 @@ public static void CollectSPN(DataSet ds, DataRow SPNAccount, string Distinguish

try
{
entry = new DirectoryEntry(DistinguishedName);
entry = new DirectoryEntry(DistinguishedName); // this is the AD "Entry.PAth" for the service account; used to get the account entry, again
pvc = entry.Properties["servicePrincipalName"];
if (pvc.Count == 0)
{
Expand Down Expand Up @@ -3237,9 +3258,11 @@ public static void ProcessSQLPathAndSPNs(DataSet ds, DataRow SQLInstance, DataRo
if (Computer.GetBoolean("JoinedToDomain") == true)
{
account = dr.GetString("DomainAccount");
if (account.EndsWith("$")) account = account.Substring(0, account.Length - 1);
// no longer remove trailing $
// if (account.EndsWith("$")) account = account.Substring(0, account.Length - 1);
SQLServer["SPNServiceAccount"] = account;
SPNServiceAccount = account;

spnPrefixF = $"MSSQLSvc/{Computer.GetString("FQDN")}";
spnPrefixN = $"MSSQLSvc/{Computer.GetString("NETBIOSName")}";
// SPNs for TCP/IP
Expand Down Expand Up @@ -3269,7 +3292,7 @@ public static void ProcessSQLPathAndSPNs(DataSet ds, DataRow SQLInstance, DataRo
}
}
}
else // don't suggest any for individual IP addresses
else // don't suggest any for individual IP addresses - TODO add this capability for FCI clusters
{
SQLServer.LogInfo("SQL Server is not listening on all IP addresses. Suggested SPNs not listed.");
}
Expand Down Expand Up @@ -3315,7 +3338,7 @@ public static void ProcessSQLPathAndSPNs(DataSet ds, DataRow SQLInstance, DataRo
}
} // end ProcessSQLPathAndSPNs

public static void CheckSPN(DataSet ds, DataRow SQLServer, DataRow SuggestedSPN, string SPNName, string accountName) // check that the SPN is on the SQL account name and no other
public static void CheckSPN(DataSet ds, DataRow SQLServer, DataRow SuggestedSPN, string SPNName, string accountName) // check that the SPN is on the SQL account name and no other; Contoso]sqlprod01$
{
DataRow Computer = ds.Tables["Computer"].Rows[0];
DirectoryEntry dupRoot = null, entry = null;
Expand Down Expand Up @@ -3346,7 +3369,7 @@ public static void CheckSPN(DataSet ds, DataRow SQLServer, DataRow SuggestedSPN,
{
SearchResult result = results[0];
entry = result.GetDirectoryEntry();
if (CompareAccounts(entry.Properties["samAccountName"][0].ToString(), accountName) == true)
if (CompareAccounts(entry.Properties["samAccountName"][0].ToString(), accountName) == true) // samAccountNAme does not include the domain name
{
SuggestedSPN["Exists"] = true;
SuggestedSPN["Message"] = "Okay";
Expand Down Expand Up @@ -3416,11 +3439,12 @@ public static bool CompareAccounts(string account1, string account2)
{
domain1 = SmartString.ChopWord(account1, ref account1, @"\");
}
/*
else if (account1.Contains(@"@") == true)
{
account1 = SmartString.ChopWord(account1, ref domain1, @"\");
}

*/
//
// Split account2 into a domain and account portions
//
Expand All @@ -3429,10 +3453,12 @@ public static bool CompareAccounts(string account1, string account2)
{
domain2 = SmartString.ChopWord(account2, ref account2, @"\");
}
/*
else if (account1.Contains(@"@") == true)
{
account2 = SmartString.ChopWord(account2, ref domain2, @"\");
}
*/

//
// Compare them - TODO - there might be a better way to match them, maybe their distinguished names???
Expand All @@ -3442,7 +3468,7 @@ public static bool CompareAccounts(string account1, string account2)
if (account1.Equals(account2, StringComparison.CurrentCultureIgnoreCase) && domain1.Equals(domain2, StringComparison.CurrentCultureIgnoreCase)) return true;

// partial match
if (account1.Equals(account2, StringComparison.CurrentCultureIgnoreCase) && (domain1 =="" || domain2 == "")) return true;
if (account1.Equals(account2, StringComparison.CurrentCultureIgnoreCase) && (domain1 == "" || domain2 == "")) return true;

// no match
return false;
Expand Down
4 changes: 2 additions & 2 deletions SQLCheck/SQLCheck/Properties/AssemblyInfo.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -32,5 +32,5 @@
// You can specify all the values or you can default the Build and Revision Numbers
// by using the '*' as shown below:
// [assembly: AssemblyVersion("1.0.*")]
[assembly: AssemblyVersion("1.0.1439.0")]
[assembly: AssemblyFileVersion("1.0.1439.0")]
[assembly: AssemblyVersion("1.0.1482.0")]
[assembly: AssemblyFileVersion("1.0.1482.0")]
5 changes: 3 additions & 2 deletions SQLCheck/SQLCheck/Storage.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -456,8 +456,9 @@ public static DataSet CreateDataSet(String ComputerName)
dt.AddColumn("ID", "Integer");
dt.Columns["ID"].AutoIncrement = true;
dt.AddColumn("ParentID", "Integer");
dt.AddColumn("Account", "String");
dt.AddColumn("Domain", "String");
dt.AddColumn("Account", "String"); // split from Service!DomainAccount - unique combo in this table - can have trailing $ if machine account or GMSA account
dt.AddColumn("Domain", "String"); // split from Service!DomainAccount - unique combo in this table
dt.AddColumn("DomainAccount", "String"); // full account name contoso\sqlprod01$
dt.AddColumn("DistinguishedName", "String");
dt.AddColumn("AccountType", "String");
dt.AddColumn("UserAccountControl", "String");
Expand Down
2 changes: 2 additions & 0 deletions SQLCheck/SQLCheck/TextReport.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -581,6 +581,8 @@ static void ReportService(DataSet ds, TextWriter s) // outputs computer and dom

s.WriteLine("Services of Interest:");
s.WriteLine();
s.WriteLine(@"Note: UPN account names (SQLSVC@Contoso.com) are converted to NT Account format (Contoso\SQLSVC) for ease of comparison and LDAP queries.");
s.WriteLine();

ReportFormatter rf = new ReportFormatter();
rf.SetColumnNames("Name:L", "Instance:L", "Description:L", "PID:R", "Service Account:L", "Domain Account:L", "Start Mode:L", "Started:L", "Status:L");
Expand Down
39 changes: 39 additions & 0 deletions SQLCheck/SQLCheck/Utility.cs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -8,6 +8,7 @@
using Microsoft.Win32;
using System.Diagnostics;
using System.IO;
using System.Data;

namespace SQLCheck
{
Expand Down Expand Up @@ -279,6 +280,44 @@ public static string GetExecutableSTDOUT(string exeName, string args)
return result;
}

//
// Service Account Name Normalization
//
// Used to make all account names, including UPN account names, into the NT Account format
// Convert local account names into domain account names ("domain\computer$")
//

public static string NormalizeNTAccount(string account, string domain = "") // unsplit names just go in account and leave domain blank
{
try
{
System.Security.Principal.NTAccount nta = new System.Security.Principal.NTAccount(domain, account);
System.Security.Principal.SecurityIdentifier si = (System.Security.Principal.SecurityIdentifier)nta.Translate(typeof(System.Security.Principal.SecurityIdentifier));
System.Security.Principal.NTAccount ntaFull = (System.Security.Principal.NTAccount)si.Translate(typeof(System.Security.Principal.NTAccount));
return $"{ntaFull.Value}";
}
catch (Exception ex)
{
if (domain == "") return account;
return $@"{domain}\{account}";
}
}

public static string ConvertLocalAccountToDomainAccount(string account, string ComputerName, string DomainName)
{
// string ComputerName = Computer.GetString("NETBIOSName");
// string DomainName = Domain.GetString("DomainShortName");

if (account.StartsWith(@"NT AUTHORITY\", StringComparison.InvariantCultureIgnoreCase) || // local built-in accounts NT AUTHORITY\NETWORK SERVICE
account.StartsWith(@"NT SERVICE\", StringComparison.InvariantCultureIgnoreCase) || // local virtual service accounts NT SerVICE\MSSQLSERVICE
account.StartsWith($@"{ComputerName}\", StringComparison.InvariantCultureIgnoreCase) || // local account SQLProd01\Administrator
account.Contains(@"\") == false)
{
return $@"{DomainName}\{ComputerName}$"; // output Contoso\SQLProd01$
}
return account; // return domain account names unchanged
}

//
// Reads a file into a string
//
Expand Down