public SQLServices(string TargetComputer, string TargetInstance, ServerVersion TargetVersion)
{
m_TargetComputerName = TargetComputer;
m_TargetInstance = TargetInstance;
// NOTE: Must match order in enum ServiceType
string[] serviceArray = new string[]
{
"MSSQLSERVER", // SQL Server
"SQLSERVERAGENT", // SQL Server Agent
"msftesql", // SQL Server Full Text Search Service for 2005
"MSSQLServerOLAPService", // SQL Server Analysis Services
"SQLBrowser", // SQL Server Browser
"MsDtsServer", // SQL Server Integration Services
"ReportServer", // SQL Server Reporting Services
"SQLWriter", // SQL Server VSS Writer
"MSSQLServerADHelper", // SQL Server Active Directory Helper
"NSService", // SQL Server Notification Service
"MSSEARCH", // SQL Server Full Text Search Service for 2000
"NS$%", // SQL Server Notification for 2005
"MsDtsServer100", // SQL Server Integration Services 10.0 (SQL2008)
"MSSQLServerADHelper100", // SQL Active Directory Helper Service 10.0 (SQL2008)
"MSSQLFDLauncher", // SQL Full-text Filter Daemon Launcher (SQL2008)
"MsDtsServer110", // SQL Server Integration Services 11.0 (SQL2012)
"MsDtsServer120", // SQL Server Integration Services 12.0 (SQL2014)
"MsDtsServer130", // SQL Server Integration Services 13.0 (SQL2016)
};
string[] serviceArrayInstance = new string[]
{
"MSSQL$", // SQL Server
"SQLAGENT$", // SQL Server Agent
"msftesql$", // SQL Server Full Text Search
"MSOLAP$", // SQL Server Analysis Services
"ReportServer$", // SQL Server Reporting Services
"NS$", // SQL Server Notification Service
"MSSQLFDLauncher$" // SQL Full-text Filter Daemon Launcher (SQL2008)
};
if (!string.IsNullOrEmpty(m_TargetInstance))
{
serviceArray[0] = serviceArrayInstance[0] + m_TargetInstance;
serviceArray[1] = serviceArrayInstance[1] + m_TargetInstance;
serviceArray[2] = serviceArrayInstance[2] + m_TargetInstance;
serviceArray[3] = serviceArrayInstance[3] + m_TargetInstance;
serviceArray[6] = serviceArrayInstance[4] + m_TargetInstance;
serviceArray[9] = serviceArrayInstance[5] + m_TargetInstance;
serviceArray[14] = serviceArrayInstance[6] + m_TargetInstance;
}
Type serviceType = typeof(SQLService.ServiceType);
//exclude services not belong to target version
foreach (SQLService.ServiceType type in Enum.GetValues(serviceType))
{
// Eliminate potential duplicate services when multiple versions on same machine.
// Ideally, I would construct this differently, but I'm just filtering out now to have minimal impact on existing code.
switch (TargetVersion)
{
case ServerVersion.SQL2000:
// The SQL Browser and VSS Writer do not ship with 2000, but can be used by it, I think
if (type == SQLService.ServiceType.msftesql ||
type == SQLService.ServiceType.MSSQLFDLauncher ||
type == SQLService.ServiceType.MSSQLServerADHelper100 ||
type == SQLService.ServiceType.NSService ||
type == SQLService.ServiceType.NSWildcard ||
type == SQLService.ServiceType.MsDtsServer100 ||
type == SQLService.ServiceType.MsDtsServer110 ||
type == SQLService.ServiceType.MsDtsServer120 ||
type == SQLService.ServiceType.MsDtsServer130)
{
continue;
}
break;
case ServerVersion.SQL2005:
if (type == SQLService.ServiceType.MSSearch ||
type == SQLService.ServiceType.MSSQLFDLauncher ||
type == SQLService.ServiceType.MSSQLServerADHelper100 ||
type == SQLService.ServiceType.MsDtsServer100 ||
type == SQLService.ServiceType.MsDtsServer110 ||
type == SQLService.ServiceType.MsDtsServer120 ||
type == SQLService.ServiceType.MsDtsServer130)
{
continue;
}
break;
case ServerVersion.SQL2008:
case ServerVersion.SQL2008R2:
// Notification services was removed in 2008, but appears to still be able to work with it, so I'm treating it as a shared service
if (type == SQLService.ServiceType.MSSearch ||
type == SQLService.ServiceType.msftesql ||
type == SQLService.ServiceType.MSSQLServerADHelper ||
type == SQLService.ServiceType.MsDtsServer ||
type == SQLService.ServiceType.MsDtsServer110 ||
type == SQLService.ServiceType.MsDtsServer120 ||
type == SQLService.ServiceType.MsDtsServer130)
{
continue;
}
break;
case ServerVersion.SQL2012:
if (type == SQLService.ServiceType.MSSearch ||
type == SQLService.ServiceType.msftesql ||
type == SQLService.ServiceType.MSSQLServerADHelper ||
type == SQLService.ServiceType.MsDtsServer ||
type == SQLService.ServiceType.MsDtsServer100 ||
type == SQLService.ServiceType.MsDtsServer120 ||
type == SQLService.ServiceType.MsDtsServer130)
{
continue;
}
break;
case ServerVersion.SQL2014:
if (type == SQLService.ServiceType.MSSearch ||
type == SQLService.ServiceType.msftesql ||
type == SQLService.ServiceType.MSSQLServerADHelper ||
type == SQLService.ServiceType.MsDtsServer ||
type == SQLService.ServiceType.MsDtsServer100 ||
type == SQLService.ServiceType.MsDtsServer110 ||
type == SQLService.ServiceType.MsDtsServer130)
{
continue;
}
break;
case ServerVersion.SQL2016:
if (type == SQLService.ServiceType.MSSearch ||
type == SQLService.ServiceType.msftesql ||
type == SQLService.ServiceType.MSSQLServerADHelper ||
type == SQLService.ServiceType.MsDtsServer ||
type == SQLService.ServiceType.MsDtsServer100 ||
type == SQLService.ServiceType.MsDtsServer110 ||
type == SQLService.ServiceType.MsDtsServer120)
{
continue;
}
break;
default:
// should not occur, but collect them all, I guess
logX.loggerX.Error(string.Format("Unknown SQL Server version {0} gathering Target Services. Gathering services for all versions. ", TargetVersion));
break;
}
SQLService s = new SQLService();
s.Name = serviceArray[(int)type];
s.Type = type;
m_possibleServices.Add(s);
}
}
public int GetSQLServices(string repositoryConnectionString, int snapshotid)
{
int numWarnings = 0;
try
{
// Get Service Information
// -----------------------
StringBuilder scopeStr = null;
scopeStr = new StringBuilder();
scopeStr.Append(m_TargetComputerName);
scopeStr.Append(Idera.SQLsecure.Core.Accounts.Constants.Cimv2Root);
// Create management scope and connect.
ConnectionOptions options = new ConnectionOptions();
if (Path.NonWhackPrefixComputer(m_TargetComputerName) != Environment.MachineName)
{
if ((Program.TargetServer.ForceLocalStatus == Server.ForceLocalStatusEnum.Unknown) || // if we have already forced a local
(Program.TargetServer.ForceLocalStatus == Server.ForceLocalStatusEnum.Failed)) // connection, then don't even try to
{ // give a username/password
if (!string.IsNullOrEmpty(Program.TargetUserName))
{
options.Username = Program.TargetUserName;
options.Password = Program.targetUserPassword;
}
}
}
ManagementScope scope = new ManagementScope(scopeStr.ToString(), options);
try
{
scope.Connect(); //let's see if we can connect
}
catch (Exception ConnectionAttempException1)
{
if (Program.TargetServer.ForceLocalStatus == Server.ForceLocalStatusEnum.Unknown) // if we haven't tried making a local connection
{
logX.loggerX.Error("First connection attempt failed - retrying as a local connection.");
ManagementScope ForcedLocalScope = new ManagementScope(scopeStr.ToString());
try
{
ForcedLocalScope.Connect();
Program.TargetServer.ForceLocalStatus = Server.ForceLocalStatusEnum.Succeeded;
scope = ForcedLocalScope;
logX.loggerX.Info("Local connection attempt succeeded.");
}
catch (Exception ConnectionAttempException2)
{
Program.TargetServer.ForceLocalStatus = Server.ForceLocalStatusEnum.Failed;
logX.loggerX.Error("Local connection attempt failed.");
}
}
}
// Create the datatable to write to the repository.
using (DataTable dataTable = SQLServicesDataTable.Create())
{
foreach (SQLService s in m_possibleServices)
{
string queryString = string.Format(
"select name, displayname, pathname, state, startmode, startname from Win32_Service where name = '{0}'",
s.Name);
if (s.Name.Contains("%"))
{
// Since the LIKE operator is not available in Windows 2000, find all services
// with name <= s.Name and name > s.Name + 0xFF.
s.Name = s.Name.Substring(0, s.Name.Length - 1);
string endName = s.Name + (char)0xFF;
//queryString = string.Format(
//"select name, displayname, pathname, state, startmode, startname from Win32_Service where name like '{0}'",
//s.Name);
queryString = string.Format(
"select name, displayname, pathname, state, startmode, startname from Win32_Service where name >= '{0}' and name < '{1}'",
s.Name, endName);
}
logX.m_logX.Info(string.Format("Running WMI Query: {0}", queryString));
SelectQuery query = new SelectQuery(queryString);
try
{
using (
ManagementObjectSearcher searcher =
new ManagementObjectSearcher(scope, query))
{
foreach (ManagementObject service in searcher.Get())
{
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[SQLServicesDataTable.ParamSnapshotid] = snapshotid;
dr[SQLServicesDataTable.ParamServiceType] = s.Type;
dr[SQLServicesDataTable.ParamState] = service["State"];
dr[SQLServicesDataTable.ParamName] = service["Name"];
dr[SQLServicesDataTable.ParamDisplayName] = service["DisplayName"];
dr[SQLServicesDataTable.ParamServicePath] = service["PathName"];
dr[SQLServicesDataTable.ParamStartupType] = service["StartMode"];
dr[SQLServicesDataTable.ParamLogonName] = service["startname"];
dataTable.Rows.Add(dr);
// Add to internal found SQL Services list
SQLService foundService = new SQLService();
foundService.Name = (string)service["name"];
foundService.Type = s.Type;
foundService.DisplayName = (string)service["DisplayName"];
foundService.FullFilePath = (string)service["PathName"];
if (foundService.FullFilePath[0] == '"')
{
foundService.FullFilePath =
foundService.FullFilePath.Substring(1,
foundService.FullFilePath.IndexOf("\"",
3) -
1);
}
m_foundServices.Add(foundService);
logX.m_logX.Info(string.Format("Found Service: {0}", foundService.DisplayName));
}
}
}
catch (Exception ex1)
{
numWarnings++;
logX.loggerX.Error(string.Format("Error Getting Target Service {0}: ", s.Name), ex1.Message);
}
}
logX.m_logX.Info("Writing SQL Service results to Repository");
using (SqlConnection repository = new SqlConnection(repositoryConnectionString))
{
// Open repository connection.
Program.ImpersonationContext wi2 = Program.SetLocalImpersonationContext();
try
{
repository.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = SQLServicesDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
bcp.WriteToServer(dataTable);
}
}
catch (Exception ex)
{
numWarnings++;
logX.loggerX.Error("Error Writing Target Services to Repository: ", ex.Message);
}
finally
{
Program.RestoreImpersonationContext(wi2);
}
}
}
}
catch (Exception ex)
{
numWarnings++;
logX.loggerX.Error("Error Getting Target Services: ", ex.Message);
}
finally
{
}
return(numWarnings);
}
