public bool GetCollectionFilters(
string targetInstance,
out List <Sql.Filter> filterList
)
{
// Init returns.
bool isOk = true;
filterList = null;
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Validate input.
if (string.IsNullOrEmpty(targetInstance))
{
logX.loggerX.Error("ERROR - invalid target instance specified to collect filter rules");
isOk = false;
}
// Collect filter rules.
if (isOk)
{
if (!Sql.CollectionFilter.GetFilterRules(m_ConnectionStringBuilder.ConnectionString, targetInstance, out filterList))
{
logX.loggerX.Error("ERROR - failed to retrieve data collection filter rules");
isOk = false;
}
}
Program.RestoreImpersonationContext(wi);
return(isOk);
}
/// <summary>
/// SQLSecure 3.1 (Anshul Aggarwal) - Stores firewall rules in repository.
/// </summary>
private void WriteFirewallRulesToRepository(string repositoryConnectionString)
{
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
try
{
using (logX.loggerX.DebugCall())
{
using (SqlConnection repository = new SqlConnection(repositoryConnectionString))
{
// Open repository connection.
repository.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcpFirewallRules = new SqlBulkCopy(repository))
{
// Set the destination table.
bcpFirewallRules.DestinationTableName = AzureSqlDBFirewallRulesObjectTable.RepositoryTable;
bcpFirewallRules.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTableObject = AzureSqlDBFirewallRulesObjectTable.Create())
{
foreach (AzureSqlDBFirewallRule fp in firewallRules)
{
// Update the datatable.
DataRow dr = dataTableObject.NewRow();
dr[AzureSqlDBFirewallRulesObjectTable.ParamSnapshotid] = m_snapshotId;
dr[AzureSqlDBFirewallRulesObjectTable.ParamIsServerLevel] = fp.IsServerLevel;
dr[AzureSqlDBFirewallRulesObjectTable.ParamName] = fp.Name;
dr[AzureSqlDBFirewallRulesObjectTable.ParamDBId] = fp.DBId;
dr[AzureSqlDBFirewallRulesObjectTable.ParamStartIPAddress] = fp.StartIPAddress;
dr[AzureSqlDBFirewallRulesObjectTable.ParamEndIPAddress] = fp.EndIPAddress;
dataTableObject.Rows.Add(dr);
if (dataTableObject.Rows.Count >= Constants.RowBatchSize)
{
bcpFirewallRules.WriteToServer(dataTableObject);
dataTableObject.Rows.Clear();
}
}
if (dataTableObject.Rows.Count > 0)
{
bcpFirewallRules.WriteToServer(dataTableObject);
}
}
}
}
}
}
catch (Exception ex)
{
logX.loggerX.Error("WriteFirewallRulesToRepository failed: ", ex.Message);
throw;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
public string[] GetAuditFolders(string targetInstance)
{
using (logX.loggerX.DebugCall())
{
Debug.Assert(IsValid);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
using (SqlConnection connection = new SqlConnection(m_ConnectionStringBuilder.ConnectionString))
{
// Open connection to the repository SQL Server.
try
{
// Open the connection.
connection.Open();
// Retrieve audit folders string.
logX.loggerX.Info("Retrieve audit folders...");
SqlParameter param = new SqlParameter(QueryGetTargetServerInfoParam, targetInstance);
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text,
QueryGetAuditFoldersString, new SqlParameter[] { param }))
{
// Read only 1 row.
if (rdr.Read())
{
string auditFoldersString = rdr[0] == DBNull.Value ? null : (string)rdr[0];
if (!string.IsNullOrEmpty(auditFoldersString))
{
string[] folders = auditFoldersString.Split(new string[] { Constants.AUDIT_FOLDER_DELIMITER }, StringSplitOptions.RemoveEmptyEntries);
return(folders);
}
}
}
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - exception raised when retrieving audit folders", ex);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlValidationCat,
"Retrieve audit folders", ex.Message);
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(new string[0]);
}
}
/// <summary>
/// SQLSecure 3.1 (Anshul Aggarwal) - Need to find last snapshot time for given server for new metric "Backup Encryption"
/// </summary>
public DateTime?GetLastCollectionEndTime(string targetInstance)
{
using (logX.loggerX.DebugCall())
{
Debug.Assert(IsValid);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
using (SqlConnection connection = new SqlConnection(m_ConnectionStringBuilder.ConnectionString))
{
// Open connection to the repository SQL Server.
try
{
// Open the connection.
connection.Open();
logX.loggerX.Info(string.Format("Retrieve last collection endtime for instance - {0}.", targetInstance));
SqlParameter param = new SqlParameter(QueryGetTargetServerInfoParam, targetInstance);
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text,
QueryGetLastCollectionEndTime, new SqlParameter[] { param }))
{
// Read only 1 row.
if (rdr.Read())
{
return(rdr[0] == DBNull.Value ? (DateTime?)null : rdr.GetDateTime(0));
}
}
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - exception raised when retrieving last collection endtime.", ex);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlValidationCat,
"Retrieve last collection endtime", ex.Message);
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(null);
}
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
string serverName,
int serverId,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Stopwatch sw = new Stopwatch();
sw.Start();
uint numProcessedLinkedServers = 0;
// Init return.
bool isOk = true;
Program.ImpersonationContext wi = Program.SetTargetImpersonationContext();
// Process linked server principals.
List <int> serverList = new List <int>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = LinkedServerPrincipalDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = LinkedServerPrincipalDataTable.Create())
{
var sqlServerVersion = Sql.SqlHelper.ParseVersion(target.ServerVersion);
// Create the query.
string query = CreateQuery(serverName, sqlServerVersion);
Debug.Assert(!string.IsNullOrEmpty(query));
var linkedServerUsersCount = 0;
// Query to get the table objects.
using (SqlDataReader rdr = SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve information.
SqlString principalName = rdr.GetSqlString(rdr.GetOrdinal(FieldPrincipalName));
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[LinkedServerPrincipalDataTable.ParamSnapshotId] = snapshotid;
dr[LinkedServerPrincipalDataTable.ParamServerId] = serverId;
dr[LinkedServerPrincipalDataTable.ParamPrincipalName] = principalName;
dataTable.Rows.Add(dr);
linkedServerUsersCount++;
numProcessedLinkedServers++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
if (linkedServerUsersCount == 0)
{
logX.loggerX.InfoFormat("No admin users found for linked server {0}", serverName);
}
}
}
}
catch (Exception ex)
{
string strMessage = "Processing linked server principals";
logX.loggerX.Error("ERROR - " + strMessage, ex);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
uint oldMetricCount = 0;
uint oldMetricTime = 0;
// See if User is already in Endpoint Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.LinkedServerPrincipals, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = numProcessedLinkedServers + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.LinkedServerPrincipals] = de;
return(isOk);
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Stopwatch sw = new Stopwatch();
sw.Start();
uint numProcessedLinkedServers = 0;
// Init return.
bool isOk = true;
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Process endpoints.
Dictionary <int, string> linkedServers = new Dictionary <int, string>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = LinkedServersDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = LinkedServersDataTable.Create())
{
var sqlServerVersion = Sql.SqlHelper.ParseVersion(target.ServerVersion);
// Create the query.
string query = CreateQuery(sqlServerVersion);
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve information.
SqlInt32 serverId = rdr.GetInt32(FieldServerId);
SqlString serverName = rdr.GetSqlString(FieldName);
// Add linked server to the list for logins roles processing.
linkedServers.Add(serverId.Value, serverName.Value);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[LinkedServersDataTable.ParamSnapshotId] = snapshotid;
dr[LinkedServersDataTable.ParamServerId] = serverId;
dr[LinkedServersDataTable.ParamServerName] = serverName;
dataTable.Rows.Add(dr);
numProcessedLinkedServers++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
}
}
}
catch (Exception ex)
{
string strMessage = "Processing linked servers";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
if (isOk)
{
foreach (var server in linkedServers)
{
// Load linked servers users.
if (!LinkedServerPrincipal.Process(targetConnection, repositoryConnection, snapshotid, server.Value, server.Key, ref metricsData))
{
logX.loggerX.Error("ERROR - error encountered in processing linked servers");
}
}
}
uint oldMetricCount = 0;
uint oldMetricTime = 0;
// See if User is already in Endpoint Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.LinkedServer, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = numProcessedLinkedServers + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.LinkedServer] = de;
return(isOk);
}
public static bool ProcessProxies(ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
string server)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
bool isOk = true;
targetConnection = Sql.SqlHelper.AppendDatabaseToConnectionString(targetConnection, "msdb");
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = SQLJobProxy.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = SQLJobProxy.Create())
{
string query = SqlJob.GetProxiesQuery;
if (version == ServerVersion.SQL2000)
{
query = SqlJob.GetProxiesQuerySQL2000;
}
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null, CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the object information.
DataRow dr = dataTable.NewRow();
dr[SQLJobProxy.ParamSnapshotId] = snapshotid;
dr[SQLJobProxy.ParamName] = rdr.GetSqlString(SQLJobProxy.ColName);
dr[SQLJobProxy.ParamEnabled] = rdr.GetByte(SQLJobProxy.ColEnabled);
dr[SQLJobProxy.ParamUserSid] = rdr.GetSqlBinary(SQLJobProxy.ColUserSid);
dr[SQLJobProxy.ParamSubSystemId] = rdr.GetInt32(SQLJobProxy.ColSubSystemId);
dr[SQLJobProxy.ParamSubSystem] = rdr.GetSqlString(SQLJobProxy.ColSubSystem);
dr[SQLJobProxy.ParamCredentialId] = rdr.GetInt32(SQLJobProxy.ColCredentialId);
dr[SQLJobProxy.ParamCredentialName] = rdr.GetSqlString(SQLJobProxy.ColCredentialName);
dr[SQLJobProxy.ParamCredentialIdentity] = rdr.GetSqlString(SQLJobProxy.ColCredentialIdentity);
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql job proxies failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql job proxies failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing sql job proxies failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool Process(ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
string server)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
bool isOk = true;
targetConnection = Sql.SqlHelper.AppendDatabaseToConnectionString(targetConnection, "msdb");
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = SqlJobDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = SqlJobDataTable.Create())
{
// Process each rule to collect the table objects.
string query = string.Format(SqlJob.GetJobsQuery, server);
if (version == ServerVersion.SQL2000)
{
query = SqlJob.GetJobsQuerySQL2000;
}
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null, CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the object information.
SqlString name = rdr.GetSqlString(ColName);
SqlBinary owner = rdr.GetSqlBinary(ColOwnerSid);
SqlInt16 enabled = rdr.GetByte(ColEnabled);
SqlString desc = rdr.GetSqlString(ColDescription);
SqlInt32 lastRunInt = rdr.GetInt32(ColLastRunDate);
SqlDateTime lastRun = SqlDateTime.MinValue;
if (lastRunInt != 0)
{
lastRun = DateTime.ParseExact(lastRunInt.ToString(), "yyyyMMdd", null);
}
SqlString command = rdr.GetSqlString(ColCommand);
SqlString stepname = rdr.GetSqlString(ColStepName);
SqlString subsystem = rdr.GetSqlString(ColSubSystem);
SqlInt32 proxyId = rdr.IsDBNull(ColProxyId)? SqlInt32.Null : rdr.GetInt32(ColProxyId);
DataRow dr = dataTable.NewRow();
dr[SqlJobDataTable.ParamSnapshotId] = snapshotid;
dr[SqlJobDataTable.ParamName] = name;
dr[SqlJobDataTable.ParamDescription] = desc;
dr[SqlJobDataTable.ParamStepName] = stepname;
dr[SqlJobDataTable.ParamLastRunDate] = lastRun;
dr[SqlJobDataTable.ParamCommand] = command;
dr[SqlJobDataTable.ParamSubSystem] = subsystem;
dr[SqlJobDataTable.ParamOwnerSid] = owner;
dr[SqlJobDataTable.ParamEnabled] = enabled;
dr[SqlJobDataTable.Paramproxyid] = proxyId;
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server jobs faild";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server jobs failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing sql server jobs failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool Process(
ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
Database database,
ObjIdCollection objIdCollection
)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
Debug.Assert(objIdCollection != null);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
bool isOk = true;
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = DatabaseObjectPermissionDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = DatabaseObjectPermissionDataTable.Create())
{
// Process each object id in the obj id collection.
int idcntr = objIdCollection.ObjIdSet.Count;
List <ObjId> objidbatch = new List <ObjId>();
foreach (ObjId objId in objIdCollection.ObjIdSet)
{
// Add obj id to the batch.
objidbatch.Add(objId);
// If batch count is at threshold query & process.
--idcntr;
if (objidbatch.Count == Constants.PermissionBatchSize || idcntr == 0)
{
// Create the query based on the object.
string query = createDbPermissionQuery(version, database, objidbatch);
Debug.Assert(!string.IsNullOrEmpty(query));
// Clear the batch.
objidbatch.Clear();
// Query to get the column objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the values.
SqlInt32 classid = rdr.GetSqlInt32(FieldDatabasePermissionClassid);
SqlInt32 objectid = rdr.GetSqlInt32(FieldDatabasePermissionObjectid);
SqlInt32 parentobjectid = rdr.GetSqlInt32(FieldDatabasePermissionParentobjectid);
SqlInt32 grantor = rdr.GetSqlInt32(FieldDatabasePermissionGrantor);
SqlString permission = rdr.GetSqlString(FieldDatabasePermissionPermission);
SqlInt32 grantee = rdr.GetSqlInt32(FieldDatabasePermissionGrantee);
SqlString isgrant = rdr.GetSqlString(FieldDatabasePermissionIsgrant);
SqlString isgrantwith = rdr.GetSqlString(FieldDatabasePermissionIsgrantwith);
SqlString isrevoke = rdr.GetSqlString(FieldDatabasePermissionIsrevoke);
SqlString isdeny = rdr.GetSqlString(FieldDatabasePermissionIsdeny);
SqlBinary columns = rdr.GetSqlBinary(FieldDatabasePermissionColumns);
// Update the datatable.
ColumnPermissions columnPermissions = new ColumnPermissions(columns);
if (version != ServerVersion.SQL2000 ||
(version == ServerVersion.SQL2000 && columnPermissions.IsNoColumns))
{
DataRow dr = dataTable.NewRow();
dr[DatabaseObjectPermissionDataTable.ParamClassid] = classid;
dr[DatabaseObjectPermissionDataTable.ParamObjectid] = objectid;
dr[DatabaseObjectPermissionDataTable.ParamParentobjectid] = parentobjectid;
dr[DatabaseObjectPermissionDataTable.ParamGrantor] = grantor;
dr[DatabaseObjectPermissionDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseObjectPermissionDataTable.ParamDbid] = database.DbId;
dr[DatabaseObjectPermissionDataTable.ParamPermission] = permission;
dr[DatabaseObjectPermissionDataTable.ParamGrantee] = grantee;
dr[DatabaseObjectPermissionDataTable.ParamIsgrant] = isgrant;
dr[DatabaseObjectPermissionDataTable.ParamIsgrantwith] = isgrantwith;
dr[DatabaseObjectPermissionDataTable.ParamIsrevoke] = isrevoke;
dr[DatabaseObjectPermissionDataTable.ParamIsdeny] = isdeny;
dr[DatabaseObjectPermissionDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
// Keep counter of number of PermissionsCollected
// ----------------------------------------------
Target.numPermissionsCollected++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database object permissions to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
else
{
// Get indices to which permissions apply and create
// seperate data row for each of them.
List <int> colIndices = columnPermissions.Columns;
foreach (int colid in colIndices)
{
DataRow dr = dataTable.NewRow();
dr[DatabaseObjectPermissionDataTable.ParamClassid] = classid;
dr[DatabaseObjectPermissionDataTable.ParamObjectid] = colid;
dr[DatabaseObjectPermissionDataTable.ParamParentobjectid] = objectid;
dr[DatabaseObjectPermissionDataTable.ParamGrantor] = grantor;
dr[DatabaseObjectPermissionDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseObjectPermissionDataTable.ParamDbid] = database.DbId;
dr[DatabaseObjectPermissionDataTable.ParamPermission] = permission;
dr[DatabaseObjectPermissionDataTable.ParamGrantee] = grantee;
dr[DatabaseObjectPermissionDataTable.ParamIsgrant] = isgrant;
dr[DatabaseObjectPermissionDataTable.ParamIsgrantwith] = isgrantwith;
dr[DatabaseObjectPermissionDataTable.ParamIsrevoke] = isrevoke;
dr[DatabaseObjectPermissionDataTable.ParamIsdeny] = isdeny;
dr[DatabaseObjectPermissionDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
// Keep counter of number of PermissionsCollected
// ----------------------------------------------
Target.numPermissionsCollected++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database object permissions to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database object permissions to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing database object permissions";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
catch (Exception ex)
{
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool ProcessGroups(ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
string server,
ServerType serverType)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(version >= ServerVersion.SQL2012);
Debug.Assert(!String.IsNullOrEmpty(targetConnection));
Debug.Assert(!String.IsNullOrEmpty(repositoryConnection));
List <int> ids = new List <int>();
bool isOk = true;
targetConnection = SqlHelper.AppendDatabaseToConnectionString(targetConnection, MasterDatabaseName);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = AvailabilityGroupTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = AvailabilityGroupTable.Create())
{
// Process each rule to collect the table objects.
// Query to get the table objects.
using (SqlDataReader rdr = SqlHelper.ExecuteReader(target, null, CommandType.Text, GetAvailabilityGroups, null))
{
while (rdr.Read())
{
// Retrieve the object information.
DataRow dr = dataTable.NewRow();
dr[AvailabilityGroupTable.ParamGroupId] = rdr.GetSqlGuid(ColGroupId);
dr[AvailabilityGroupTable.ParamName] = rdr.GetSqlString(ColName);
dr[AvailabilityGroupTable.ParamResourceId] = rdr.GetSqlString(ColResourceId);
dr[AvailabilityGroupTable.ParamResourceGroupId] = rdr.GetSqlString(ColResourceGroupId);
dr[AvailabilityGroupTable.ParamFailureConditionLevel] = rdr.GetSqlInt32(ColFailureConditionLevel);
dr[AvailabilityGroupTable.ParamHealthCheckTimeout] = rdr.GetSqlInt32(ColHealthCheckTimeout);
dr[AvailabilityGroupTable.ParamAutomatedBackuppReference] = rdr.GetSqlByte(ColAutomatedBackuppReference);
dr[AvailabilityGroupTable.ParamAutomatedBackuppReferenceDesc] = rdr.GetSqlString(ColAutomatedBackuppReferenceDesc);
dr[AvailabilityGroupTable.ParamSnapshotid] = snapshotid;
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server availability groups failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server availability groups failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing sql server availability groups failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
if (isOk)
{
return(ProcessReplicas(version, targetConnection, repositoryConnection, snapshotid, server, serverType));
}
return(isOk);
}
public static bool Process(string targetConnection,
string repositoryConnection, int snapshotid,
Database database,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData)
{
bool isOk = true;
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
try
{
//Do the main job
uint processedCertificatesCnt = 0;
Stopwatch sw = new Stopwatch();
sw.Start();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
var sqlServerVersion = Sql.SqlHelper.ParseVersion(target.ServerVersion);
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
bcp.DestinationTableName = CertificateTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
using (DataTable dataTable = CertificateTable.Create())
{
var query = CreateQuery(database, sqlServerVersion);
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the object information.
SqlString certName = rdr.GetSqlString((int)DatabaseCertificateColumns.CertificateName);
SqlInt32 certificateId = rdr.IsDBNull((int)DatabaseCertificateColumns.CertificateId) ? SqlInt32.Null : rdr.GetSqlInt32((int)DatabaseCertificateColumns.CertificateId);
SqlInt32 principalId = rdr.IsDBNull((int)DatabaseCertificateColumns.PrincipalId) ? SqlInt32.Null : rdr.GetSqlInt32((int)DatabaseCertificateColumns.PrincipalId);
SqlString keyEncryptionType = rdr.GetSqlString((int)DatabaseCertificateColumns.KeyEncryptionType);
SqlString keyEncryptionTypeDesc = rdr.GetSqlString((int)DatabaseCertificateColumns.KeyEncryptionTypeDesc);
SqlBoolean isActiveForBeginDialog = rdr.GetSqlBoolean((int)DatabaseCertificateColumns.IsActiveForBeginDialog);
SqlString issuerName = rdr.GetSqlString((int)DatabaseCertificateColumns.IssuerName);
SqlString certSerialNumber = rdr.GetSqlString((int)DatabaseCertificateColumns.CertSerialNumber);
var sid = rdr.GetSqlBinary((int)DatabaseCertificateColumns.Sid);
SqlString stringSid = rdr.GetSqlString((int)DatabaseCertificateColumns.StringSid);
SqlString subject = rdr.GetSqlString((int)DatabaseCertificateColumns.Subject);
SqlDateTime expiryDate = rdr.GetSqlDateTime((int)DatabaseCertificateColumns.ExpiryDate);
SqlDateTime startDate = rdr.GetSqlDateTime((int)DatabaseCertificateColumns.StartDate);
var thumbprint = rdr.GetSqlBinary((int)DatabaseCertificateColumns.Thumbprint);
SqlString attestedBy = rdr.GetSqlString((int)DatabaseCertificateColumns.AttestedBy);
SqlDateTime keyLastBackupDate = rdr.IsDBNull((int)DatabaseCertificateColumns.KeyLastBackupDate) ? SqlDateTime.Null : rdr.GetSqlDateTime((int)DatabaseCertificateColumns.KeyLastBackupDate);
// Update the datatable.
var dataRow = dataTable.NewRow();
dataRow[CertificateTable.SnapshotId] = snapshotid;
dataRow[CertificateTable.DbId] = database.DbId;
dataRow[CertificateTable.CertificateName] = certName;
dataRow[CertificateTable.CertificateId] = certificateId;
dataRow[CertificateTable.PrincipalId] = principalId;
dataRow[CertificateTable.KeyEncryptionType] = keyEncryptionType;
dataRow[CertificateTable.KeyEncryptionTypeDesc] = keyEncryptionTypeDesc;
dataRow[CertificateTable.IsActiveForBeginDialog] = isActiveForBeginDialog;
dataRow[CertificateTable.IssuerName] = issuerName;
dataRow[CertificateTable.CertSerialNumber] = certSerialNumber;
dataRow[CertificateTable.Sid] = sid;
dataRow[CertificateTable.StringSid] = stringSid;
dataRow[CertificateTable.Subject] = subject;
dataRow[CertificateTable.ExpiryDate] = expiryDate;
dataRow[CertificateTable.StartDate] = startDate;
dataRow[CertificateTable.Thumbprint] = thumbprint;
dataRow[CertificateTable.AttestedBy] = attestedBy;
dataRow[CertificateTable.KeyLastBackupDate] = keyLastBackupDate;
processedCertificatesCnt++;
dataTable.Rows.Add(dataRow);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
}
}
}
sw.Stop();
//ToDo: Check if we need this
uint oldMetricCount = 0;
uint oldMetricTime = 0;
// See if Schema is already in Metrics Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.Schema, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = processedCertificatesCnt + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.Schema] = de;
}
catch (Exception ex)
{
_logX.loggerX.Error(ex.Message);
string strMessage = "Processing certificates";
_logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
return(isOk);
}
public static bool Process(ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
int databaseId, string databaseName, ServerType serverType)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
bool isOk = true;
if (serverType != ServerType.AzureSQLDatabase)
{
targetConnection = Sql.SqlHelper.AppendDatabaseToConnectionString(targetConnection, "master");
}
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
if (version == ServerVersion.SQL2000)
{
return(true);
}
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = EncryptionKeyDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = EncryptionKeyDataTable.Create())
{
// Process each rule to collect the table objects.
string query = string.Format(GetQuery(version), databaseName);
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null, CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the object information.
SqlString name = rdr.GetSqlString(ColName);
SqlString algorithm = rdr.GetSqlString(ColAlgorithm);
SqlString algorithmDesc = rdr.GetSqlString(ColAlgorithmDesc);
SqlString provType = rdr.GetSqlString(ColProviderType);
SqlString type = rdr.GetSqlString(ColType);
SqlInt32 principalId = rdr.IsDBNull(ColPrincipalId) ? SqlInt32.Null : rdr.GetInt32(ColPrincipalId);
SqlInt32 dbId = rdr.IsDBNull(ColDbKeyId) ? SqlInt32.Null : rdr.GetInt32(ColDbKeyId);
SqlInt32 keyLength = rdr.IsDBNull(ColKeyLength) ? SqlInt32.Null : rdr.GetInt32(ColKeyLength);
//todo add database id
DataRow dr = dataTable.NewRow();
dr[EncryptionKeyDataTable.ParamSnapshotId] = snapshotid;
dr[EncryptionKeyDataTable.ParamName] = name;
dr[EncryptionKeyDataTable.ParamAlgorithm] = algorithm;
dr[EncryptionKeyDataTable.ParamAlgorithmDesc] = algorithmDesc;
dr[EncryptionKeyDataTable.ParamProviderType] = provType;
dr[EncryptionKeyDataTable.ParamPrincipalId] = principalId;
dr[EncryptionKeyDataTable.ParamDbKeyId] = dbId;
dr[EncryptionKeyDataTable.ParamKeyLength] = keyLength;
dr[EncryptionKeyDataTable.ParamDatabaseId] = databaseId;
dr[EncryptionKeyDataTable.ParamType] = type;
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server keys faild";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server keys faild";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing sql server keys failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
SqlObjectType oType,
List <int> oidList,
ServerType serverType
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(oidList != null && oidList.Count > 0);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
bool isOk = true;
using (SqlConnection repository = new SqlConnection(repositoryConnection))
{
using (SqlConnection target = new SqlConnection(targetConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = ServerPermissionDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = ServerPermissionDataTable.Create())
{
// Process each uid in the uid list.
int oidcntr = oidList.Count;
List <int> oidbatch = new List <int>();
foreach (int oid in oidList)
{
// Add oid to the batch.
oidbatch.Add(oid);
// If batch count is at threshold query & process.
--oidcntr;
if (oidbatch.Count == Constants.PermissionBatchSize || oidcntr == 0)
{
// Create the query based on the object.
string query;
if (serverType == ServerType.AzureSQLDatabase)
{
query = createPermissionQueryAzureDB(oType, oidbatch);
}
else
{
query = createPermissionQuery(oType, oidbatch);
}
Debug.Assert(!string.IsNullOrEmpty(query));
// Clear the batch.
oidbatch.Clear();
// Query to get the column objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query,
null))
{
while (rdr.Read())
{
// Retrieve the values.
SqlInt32 classid = rdr.GetSqlInt32(FieldClass);
SqlInt32 majorid = rdr.GetSqlInt32(FieldMajorId);
SqlInt32 minorid = rdr.GetSqlInt32(FieldMinorId);
SqlInt32 grantee = rdr.GetSqlInt32(FieldGrantee);
SqlInt32 grantor = rdr.GetSqlInt32(FieldGrantor);
SqlString type = rdr.GetSqlString(FieldType);
SqlString permission = rdr.GetSqlString(FieldPermission);
SqlString isgrant = rdr.GetSqlString(FieldIsGrant);
SqlString iswithgrant = rdr.GetSqlString(FieldIsWithGrant);
SqlString isrevoke = rdr.GetSqlString(FieldIsRevoke);
SqlString isdeny = rdr.GetSqlString(FieldIsDeny);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[ServerPermissionDataTable.ParamSnapshotid] = snapshotid;
dr[ServerPermissionDataTable.ParamClassId] = classid;
dr[ServerPermissionDataTable.ParamMajorId] = majorid;
dr[ServerPermissionDataTable.ParamMinorId] = minorid;
dr[ServerPermissionDataTable.ParamGrantee] = grantee;
dr[ServerPermissionDataTable.ParamGrantor] = grantor;
dr[ServerPermissionDataTable.ParamPermission] = permission;
dr[ServerPermissionDataTable.ParamIsgrant] = isgrant;
dr[ServerPermissionDataTable.ParamIsgrantwith] = iswithgrant;
dr[ServerPermissionDataTable.ParamIsrevoke] = isrevoke;
dr[ServerPermissionDataTable.ParamIsdeny] = isdeny;
dr[ServerPermissionDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
// Keep counter of number of PermissionsCollected
// ----------------------------------------------
Target.numPermissionsCollected++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing server level permissions for " + oType;
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " +
new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
}
}
Program.RestoreImpersonationContext(wi);
return(isOk);
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
Database database,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
uint processedSchemaCnt = 0;
Stopwatch sw = new Stopwatch();
sw.Start();
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
bool isOk = true;
List <int> schemaidList = new List <int>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = DatabaseSchemaDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = DatabaseSchemaDataTable.Create())
{
// Create the query based on the rule.
string query = createSchemaQuery(database);
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the object information.
SqlInt32 schemaid = rdr.GetSqlInt32(FieldSchemaid);
SqlInt32 uid = rdr.GetSqlInt32(FieldUid);
SqlString schemaname = rdr.GetSqlString(FieldSchemaname);
// Add schema id to the list.
schemaidList.Add(schemaid.Value);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[DatabaseSchemaDataTable.ParamDbid] = database.DbId;
dr[DatabaseSchemaDataTable.ParamUid] = uid;
dr[DatabaseSchemaDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseSchemaDataTable.ParamSchemaid] = schemaid;
dr[DatabaseSchemaDataTable.ParamSchemaname] = schemaname;
dr[DatabaseSchemaDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
processedSchemaCnt++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing schema";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
// Load object permissions.
if (isOk)
{
if (!DatabaseSchemaPermission.Process(targetConnection, repositoryConnection, snapshotid, database, schemaidList))
{
logX.loggerX.Error("ERROR - error encountered in processing schema permissions");
isOk = false;
}
}
sw.Stop();
uint oldMetricCount = 0;
uint oldMetricTime = 0;
// See if Schema is already in Metrics Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.Schema, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = processedSchemaCnt + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.Schema] = de;
return(isOk);
}
public static bool SaveSnapshotFilterRules(
string repositoryConnectionString,
int snapshotid,
List <Filter> filterList
)
{
Debug.Assert(!string.IsNullOrEmpty(repositoryConnectionString));
Debug.Assert(filterList != null && filterList.Count > 0);
// Validate inputs.
if (string.IsNullOrEmpty(repositoryConnectionString))
{
logX.loggerX.Error("ERROR - invalid connection string specified for saving filter rules to snapshot");
return(false);
}
// Empty filter list, return.
if (filterList == null || filterList.Count == 0)
{
logX.loggerX.Info("INFO - no filters to save");
return(true);
}
// Save each filter header and rule to repository.
bool isOk = true;
Program.ImpersonationContext ic = Program.SetLocalImpersonationContext();
using (SqlConnection connection = new SqlConnection(repositoryConnectionString))
{
try
{
// Open the connection.
connection.Open();
// Use bulk copy to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(connection))
{
// Write all the filter headers to the repository.
bcp.DestinationTableName = ServerFilterRuleHeaderDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
using (DataTable dataTable = ServerFilterRuleHeaderDataTable.Create())
{
foreach (Filter f in filterList)
{
//if (f.Name == Collector.Constants.SpecialMasterDatabase)
//{
// continue;
//}
DataRow dr = dataTable.NewRow();
dr[ServerFilterRuleHeaderDataTable.ParamSnapshotid] = snapshotid;
dr[ServerFilterRuleHeaderDataTable.ParamFilterruleheaderid] = f.HeaderId;
dr[ServerFilterRuleHeaderDataTable.ParamRulename] = f.Name;
dr[ServerFilterRuleHeaderDataTable.ParamDescription] = "";
dr[ServerFilterRuleHeaderDataTable.ParamCreatedby] = f.CreatedBy;
dr[ServerFilterRuleHeaderDataTable.ParamCreatedtm] = f.CreatedOn;
dr[ServerFilterRuleHeaderDataTable.ParamLastmodifiedby] = f.LastModifiedBy;
dr[ServerFilterRuleHeaderDataTable.ParamLastmodifiedtm] = f.LastModifiedOn;
dr[ServerFilterRuleHeaderDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
}
// Write to repository.
bcp.WriteToServer(dataTable);
}
// Write all the filter rules to the repository.
bcp.DestinationTableName = ServerFilterRuleDataTable.RepositoryTable;
using (DataTable dataTable = ServerFilterRuleDataTable.Create())
{
foreach (Filter f in filterList)
{
//if (f.Name == Collector.Constants.SpecialMasterDatabase)
//{
// continue;
//}
// Write server rules to the data table.
if (f.ServerLevelRules != null)
{
foreach (Filter.Rule sr in f.ServerLevelRules)
{
DataRow dr = dataTable.NewRow();
addRuleToDataRow(snapshotid, f.HeaderId.Value, sr, dr);
dataTable.Rows.Add(dr);
}
}
// Write db rules to the data table.
if (f.DatabaseRules != null)
{
foreach (Filter.Rule dbr in f.DatabaseRules)
{
DataRow dr = dataTable.NewRow();
addRuleToDataRow(snapshotid, f.HeaderId.Value, dbr, dr);
dataTable.Rows.Add(dr);
}
}
// Write db obj rules to the data table.
if (f.DatabaseLevelRules != null)
{
foreach (Filter.Rule dblr in f.DatabaseLevelRules)
{
DataRow dr = dataTable.NewRow();
addRuleToDataRow(snapshotid, f.HeaderId.Value, dblr, dr);
dataTable.Rows.Add(dr);
}
}
}
// Write to repository.
bcp.WriteToServer(dataTable);
}
}
}
catch (SqlException ex)
{
string strMessage = "Update snapshot filter tables";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnectionString,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
strMessage, ex.Message);
isOk = false;
}
}
Program.RestoreImpersonationContext(ic);
return(isOk);
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
Database database,
List <int> uidList
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
Debug.Assert(uidList != null);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
bool isOk = true;
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = DatabasePrincipalPermissionDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = DatabasePrincipalPermissionDataTable.Create())
{
// Process each uid in the uid list.
int uidcntr = uidList.Count;
List <int> uidbatch = new List <int>();
foreach (int uid in uidList)
{
// Add uid to the batch.
uidbatch.Add(uid);
// If batch count is at threshold query & process.
--uidcntr;
if (uidbatch.Count == Constants.PermissionBatchSize || uidcntr == 0)
{
// Create the query based on the object.
string query = createPrincipalPermissionQuery(database, uidbatch);
Debug.Assert(!string.IsNullOrEmpty(query));
// Clear the batch.
uidbatch.Clear();
// Query to get the column objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the values.
SqlInt32 schemaid = rdr.GetSqlInt32(FieldUid);
SqlInt32 grantor = rdr.GetSqlInt32(FieldGrantor);
SqlInt32 grantee = rdr.GetSqlInt32(FieldGrantee);
SqlInt32 classid = rdr.GetSqlInt32(FieldClassid);
SqlString permission = rdr.GetSqlString(FieldPermission);
SqlString isgrant = rdr.GetSqlString(FieldIsgrant);
SqlString isgrantwith = rdr.GetSqlString(FieldIsgrantwith);
SqlString isrevoke = rdr.GetSqlString(FieldIsrevoke);
SqlString isdeny = rdr.GetSqlString(FieldIsdeny);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[DatabasePrincipalPermissionDataTable.ParamSnapshotid] = snapshotid;
dr[DatabasePrincipalPermissionDataTable.ParamUid] = schemaid;
dr[DatabasePrincipalPermissionDataTable.ParamGrantor] = grantor;
dr[DatabasePrincipalPermissionDataTable.ParamGrantee] = grantee;
dr[DatabasePrincipalPermissionDataTable.ParamDbid] = database.DbId;
dr[DatabasePrincipalPermissionDataTable.ParamClassid] = classid;
dr[DatabasePrincipalPermissionDataTable.ParamPermission] = permission;
dr[DatabasePrincipalPermissionDataTable.ParamIsgrant] = isgrant;
dr[DatabasePrincipalPermissionDataTable.ParamIsgrantwith] = isgrantwith;
dr[DatabasePrincipalPermissionDataTable.ParamIsrevoke] = isrevoke;
dr[DatabasePrincipalPermissionDataTable.ParamIsdeny] = isdeny;
dr[DatabasePrincipalPermissionDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
// Keep counter of number of PermissionsCollected
// ----------------------------------------------
Target.numPermissionsCollected++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - writing database principal permissions to Repository, ", ex);
isOk = false;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - writing database principal permissions to Repository, ", ex);
isOk = false;
}
}
}
}
}
}
}
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - exception encountered when processing database principal permissions, ", ex);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
Database database,
List <int> schemaidList
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
Debug.Assert(schemaidList != null);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
bool isOk = true;
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = DatabaseSchemaPermissionDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = DatabaseSchemaPermissionDataTable.Create())
{
// Process each schema id in the list.
int schemaidcntr = schemaidList.Count;
List <int> schemaidbatch = new List <int>();
foreach (int schemaidv in schemaidList)
{
// Add schema id to the batch.
schemaidbatch.Add(schemaidv);
// If batch count is at threshold or at the end of the list query & process.
--schemaidcntr;
if (schemaidbatch.Count == Constants.PermissionBatchSize || schemaidcntr == 0)
{
// Create the query based on the object.
string query = createSchemaPermissionQuery(database, schemaidbatch);
Debug.Assert(!string.IsNullOrEmpty(query));
// Clear the batch.
schemaidbatch.Clear();
// Query to get the column objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve the values.
SqlInt32 schemaid = rdr.GetSqlInt32(FieldSchemaid);
SqlInt32 grantor = rdr.GetSqlInt32(FieldGrantor);
SqlInt32 grantee = rdr.GetSqlInt32(FieldGrantee);
SqlInt32 classid = rdr.GetSqlInt32(FieldClassid);
SqlString permission = rdr.GetSqlString(FieldPermission);
SqlString isgrant = rdr.GetSqlString(FieldIsgrant);
SqlString isgrantwith = rdr.GetSqlString(FieldIsgrantwith);
SqlString isrevoke = rdr.GetSqlString(FieldIsrevoke);
SqlString isdeny = rdr.GetSqlString(FieldIsdeny);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[DatabaseSchemaPermissionDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseSchemaPermissionDataTable.ParamSchemaid] = schemaid;
dr[DatabaseSchemaPermissionDataTable.ParamGrantor] = grantor;
dr[DatabaseSchemaPermissionDataTable.ParamGrantee] = grantee;
dr[DatabaseSchemaPermissionDataTable.ParamDbid] = database.DbId;
dr[DatabaseSchemaPermissionDataTable.ParamClassid] = classid;
dr[DatabaseSchemaPermissionDataTable.ParamPermission] = permission;
dr[DatabaseSchemaPermissionDataTable.ParamIsgrant] = isgrant;
dr[DatabaseSchemaPermissionDataTable.ParamIsgrantwith] = isgrantwith;
dr[DatabaseSchemaPermissionDataTable.ParamIsrevoke] = isrevoke;
dr[DatabaseSchemaPermissionDataTable.ParamIsdeny] = isdeny;
dr[DatabaseSchemaPermissionDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
// Keep counter of number of PermissionsCollected
// ----------------------------------------------
Target.numPermissionsCollected++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database schema permissions to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database schema permissions to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing database schema permissions";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
public static bool ProcessOSObjects(
string repositoryConnection,
int snapshotid,
List <Account> users,
Dictionary <Account, List <Account> > groupMemberhips
)
{
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(groupMemberhips != null);
// If no group membership or users info, return.
if (groupMemberhips.Count == 0 && users.Count == 0)
{
return(true);
}
// Create a set to keep track of accounts already processed.
List <string> accountSID = new List <string>();
// Open the connections.
bool isOk = true;
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
try
{
using (SqlConnection repository = new SqlConnection(repositoryConnection))
{
// Open repository connection.
repository.Open();
if (groupMemberhips.Count > 0)
{
// Use bulk copy object to write group members to repository.
using (SqlBulkCopy
bcpAccount = new SqlBulkCopy(repository),
bcpMembership = new SqlBulkCopy(repository)
)
{
// Set the destination tables.
bcpAccount.DestinationTableName = WindowsOSAccountDataTable.RepositoryTable;
bcpAccount.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
bcpMembership.DestinationTableName = WindowsOSGroupMemberDataTable.RepositoryTable;
bcpMembership.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable
dtAccount = WindowsOSAccountDataTable.Create(),
dtMembership = WindowsOSGroupMemberDataTable.Create()
)
{
// Store group members to the repository.
foreach (KeyValuePair <Account, List <Account> > group in groupMemberhips)
{
// If group is not in the account set, then update.
if (!accountSID.Contains(group.Key.SID.SidString))
{
// Add to the set for subsequent checks.
accountSID.Add(group.Key.SID.SidString);
// Add the group to the account data table.
addToAccountDataTable(dtAccount, snapshotid, group.Key, true);
}
// Process group members.
foreach (Account member in group.Value)
{
// If member is not in the account set, then update.
if (!accountSID.Contains(member.SID.SidString))
{
// Add to the set for subsequent checks.
accountSID.Add(member.SID.SidString);
// Add the member to the account data table.
addToAccountDataTable(dtAccount, snapshotid, member, true);
}
// Add the member to the member data table.
addToMembershipDataTable(dtMembership, snapshotid, group.Key, member);
// Write to the repository if reached the batch size.
if (atBatchSize(dtAccount, dtMembership))
{
writeDataTables(bcpAccount, dtAccount, bcpMembership, dtMembership);
}
}
}
// Write any remaining data to the repository.
writeDataTables(bcpAccount, dtAccount, bcpMembership, dtMembership);
}
}
}
if (users.Count > 0)
{
// Use bulk copy to write users to the repository.
using (SqlBulkCopy bcpAccount = new SqlBulkCopy(repository))
{
// Set the destination tables.
bcpAccount.DestinationTableName = WindowsOSAccountDataTable.RepositoryTable;
bcpAccount.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dtAccount = WindowsOSAccountDataTable.Create())
{
// Process each user.
foreach (Account user in users)
{
// If member is not in the account set, then update.
if (!accountSID.Contains(user.SID.SidString))
{
// Add to the set for subsequent checks.
accountSID.Add(user.SID.SidString);
// Add the member to the account data table.
addToAccountDataTable(dtAccount, snapshotid, user, true);
}
// Write to the repository if reached the batch size.
if (atBatchSize(dtAccount))
{
writeDataTable(bcpAccount, dtAccount);
}
}
// Write any remaining data to the repository.
writeDataTable(bcpAccount, dtAccount);
}
}
}
}
}
catch (Exception ex /*SqlException ex*/)
{
string strMessage = "Saving windows accounts";
logX.loggerX.Error("WARNING -" + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Warning,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventWarning(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
return(isOk);
}
public static bool Process(
ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
Database database,
ServerType serverType,
out bool isGuestEnabled,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData
)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
Stopwatch sw = new Stopwatch();
sw.Start();
uint numProcessedUsers = 0;
// Init return.
bool isOk = true;
isGuestEnabled = false;
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Process database users.
List <int> uidList = new List <int>();
Dictionary <string, KeyValuePair <int, string> > nameDictionary = new Dictionary <string, KeyValuePair <int, string> >();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = DatabasePrincipalDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = DatabasePrincipalDataTable.Create())
{
// Create the query.
string query = createPrincipalQuery(version, database, serverType);
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve information.
SqlString name = rdr.GetSqlString(FieldPrincipalName);
SqlInt32 uid = rdr.GetSqlInt32(FieldPrincipalUid);
SqlString type = rdr.GetSqlString(FieldPrincipalType);
SqlBinary usersid = rdr.GetSqlBinary(FieldPrincipalUsersid);
SqlString isalias = rdr.GetSqlString(FieldPrincipalIsalias);
SqlInt32 altuid = rdr.GetSqlInt32(FieldPrincipalAltuid);
SqlString hasaccess = rdr.GetSqlString(FieldPrincipalHasaccess);
SqlInt32 owner = rdr.GetSqlInt32(FieldPrincipalOwner);
SqlString defaultSchemaName = rdr.GetSqlString(FieldPrincipalDefaultschemaname);
// Azure SQL DB does not support contained databases.
SqlBoolean isContained = (serverType == ServerType.AzureSQLDatabase ? true : database.IsContained) &&
rdr.GetBoolean(FieldIsContainedUser);
SqlString authenticationType = rdr.GetString(FieldAuthenticationType);
// Add to uid collection for later permission processing.
Debug.Assert(!uid.IsNull);
uidList.Add(uid.Value);
// Add to name dictionary for SQL 2000 role member processing & public role processing.
Debug.Assert(!name.IsNull);
Debug.Assert(!type.IsNull);
nameDictionary.Add(name.Value, new KeyValuePair <int, string>(uid.Value, type.Value));
// If guest account set guest enabled flag.
if (uid.Value == Constants.GuestUser && string.Compare(hasaccess.Value, "Y", true) == 0)
{
isGuestEnabled = true;
}
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[DatabasePrincipalDataTable.ParamSnapshotid] = snapshotid;
dr[DatabasePrincipalDataTable.ParamOwner] = owner;
dr[DatabasePrincipalDataTable.ParamDbid] = database.DbId;
dr[DatabasePrincipalDataTable.ParamUid] = uid;
dr[DatabasePrincipalDataTable.ParamName] = name;
dr[DatabasePrincipalDataTable.ParamUsersid] = usersid;
dr[DatabasePrincipalDataTable.ParamType] = type;
dr[DatabasePrincipalDataTable.ParamIsalias] = isalias;
dr[DatabasePrincipalDataTable.ParamAltuid] = altuid;
dr[DatabasePrincipalDataTable.ParamHasaccess] = hasaccess;
dr[DatabasePrincipalDataTable.ParamDefaultschemaname] = defaultSchemaName;
dr[DatabasePrincipalDataTable.ParamHashkey] = "";
dr[DatabasePrincipalDataTable.ParamIsContained] = isContained;
dr[DatabasePrincipalDataTable.ParamAuthenticationType] = authenticationType;
dataTable.Rows.Add(dr);
numProcessedUsers++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database principals to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing database principals to Repository ";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw ex;
}
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing database principals";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
// Process role memberships.
if (isOk)
{
if (!processMembers(version, targetConnection, repositoryConnection, snapshotid, database, nameDictionary))
{
logX.loggerX.Error("ERROR - error encountered in processing database role members");
isOk = false;
}
}
// Load principal permissions, if its 2005.
if (isOk)
{
if (version != ServerVersion.SQL2000)
{
if (!DatabasePrincipalPermission.Process(targetConnection, repositoryConnection, snapshotid, database, uidList))
{
logX.loggerX.Error("ERROR - error encountered in processing database principal permissions");
isOk = false;
}
}
}
uint oldMetricCount = 0;
uint oldMetricTime = 0;
sw.Stop();
// See if User is already in Metrics Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.User, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = numProcessedUsers + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.User] = de;
return(isOk);
}
public bool GetTargetCredentials(
string targetInstance,
out string server,
out int?port,
out string sqlLogin,
out string sqlPassword,
out string sqlAuthType,
out string srvrLogin,
out string srvrPassword,
out string serverType
)
{
using (logX.loggerX.DebugCall())
{
Debug.Assert(IsValid);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Init returns.
server = null;
port = null;
sqlLogin = null;
sqlPassword = null;
sqlAuthType = null;
srvrLogin = null;
srvrPassword = null;
serverType = null;
using (SqlConnection connection = new SqlConnection(m_ConnectionStringBuilder.ConnectionString))
{
// Open connection to the repository SQL Server.
try
{
// Open the connection.
connection.Open();
// Retrieve target credential information.
logX.loggerX.Info("Retrieve target credentials");
SqlParameter param = new SqlParameter(QueryGetTargetServerInfoParam, targetInstance);
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text,
QueryGetTargetServerInfo, new SqlParameter[] { param }))
{
// Read only 1 row.
if (rdr.Read())
{
m_RegisteredServerId = (int)rdr[0];
server = (string)rdr[1];
sqlLogin = (string)rdr[2];
sqlPassword = Encryptor.Decrypt((string)rdr[3]);
sqlAuthType = (string)rdr[4];
srvrLogin = (string)rdr[5];
srvrPassword = Encryptor.Decrypt((string)rdr[6]);
port = rdr[7] == DBNull.Value ? null : (int?)rdr[7];
serverType = (string)rdr[9];
}
else
{
logX.loggerX.Error("ERROR - ", targetInstance, " is not registered in SQLsecure");
AppLog.WriteAppEventError(SQLsecureEvent.DlErrTargetNotRegistered, SQLsecureCat.DlValidationCat,
targetInstance);
return(false);
}
}
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - exception raised when retrieving target credentials", ex);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlValidationCat,
"Retrieve target credentials", ex.Message);
return(false);
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(true);
}
}
public static bool ProcessReplicas(ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
string server,
ServerType serverType)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(version >= ServerVersion.SQL2012);
Debug.Assert(!String.IsNullOrEmpty(targetConnection));
Debug.Assert(!String.IsNullOrEmpty(repositoryConnection));
bool isOk = true;
targetConnection = SqlHelper.AppendDatabaseToConnectionString(targetConnection, MasterDatabaseName);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
List <int> epList = new List <int>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = AvailabilityReplicas.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = AvailabilityReplicas.Create())
{
// Process each rule to collect the table objects.
// Query to get the objects.
using (SqlDataReader rdr = SqlHelper.ExecuteReader(target, null, CommandType.Text, GetAvailabiltyReplicas, null))
{
while (rdr.Read())
{
// Retrieve the object information.
DataRow dr = dataTable.NewRow();
dr[AvailabilityReplicas.ParamReplicaId] = rdr.GetGuid(AvailabilityReplicas.ColReplicaid);
dr[AvailabilityReplicas.ParamSnapshotid] = snapshotid;
dr[AvailabilityReplicas.ParamGroupid] = rdr.GetGuid(AvailabilityReplicas.ColGroupId);
dr[AvailabilityReplicas.ParamReplicaServerName] = rdr.GetSqlString(AvailabilityReplicas.ColReplicaServerName);
dr[AvailabilityReplicas.ParamOwnersid] = rdr.GetSqlBinary(AvailabilityReplicas.ColOwnersid);
dr[AvailabilityReplicas.ParamEndpointUrl] = rdr.GetSqlString(AvailabilityReplicas.ColEndpointUrl);
dr[AvailabilityReplicas.ParamAvailabilityMode] = rdr.GetSqlByte(AvailabilityReplicas.ColAvailabilityMode);
dr[AvailabilityReplicas.ParamAvailabilityModeDesc] = rdr.GetSqlString(AvailabilityReplicas.ColAvailabilityModeDesc);
dr[AvailabilityReplicas.ParamFailoverMode] = rdr.GetSqlByte(AvailabilityReplicas.ColFailoverMode);
dr[AvailabilityReplicas.ParamFailoverModeDesc] = rdr.GetSqlString(AvailabilityReplicas.ColFailoverModeDesc);
dr[AvailabilityReplicas.ParamCreateDate] = rdr.GetSqlDateTime(AvailabilityReplicas.ColCreateDate);
dr[AvailabilityReplicas.ParamModifyDate] = rdr.GetSqlDateTime(AvailabilityReplicas.ColModifyDate);
SqlInt32 replicaMetaDataId = rdr.GetSqlInt32(AvailabilityReplicas.ColReplicaMetadataId);
dr[AvailabilityReplicas.ParamReplicaMetadataId] = replicaMetaDataId;
if (!replicaMetaDataId.IsNull)
{
epList.Add(replicaMetaDataId.Value);
}
dataTable.Rows.Add(dr);
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server availability replicas failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
string strMessage = "Writing to Repository sql server availability replicas failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
throw;
}
}
}
}
}
if (epList.Count != 0)
{
if (!ServerPermission.Process(targetConnection, repositoryConnection, snapshotid, SqlObjectType.AvailabilityGroup, epList, serverType))
{
logX.loggerX.Error("ERROR - error encountered in processing availability group permissions");
isOk = false;
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing sql server availability replicas failed";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
/// <summary>
/// SQLSecure 3.1 (Anshul Aggarwal) - Fetches firewall rules for target server and stores then in repository.
/// </summary>
public bool ProcessFirewallRules(string repositoryConnectionString, SqlConnectionStringBuilder targetConnection, List <Database> databases)
{
using (logX.loggerX.DebugCall())
{
Debug.Assert(targetConnection != null);
bool isOk = true;
// Check inputs.
if (targetConnection == null)
{
string strMessage = "Invalid connection string";
logX.loggerX.Error("ERROR - " + strMessage);
isOk = false;
}
if (isOk)
{
Program.ImpersonationContext wi = Program.SetTargetImpersonationContext();
try
{
foreach (Database db in databases)
{
targetConnection.InitialCatalog = db.Name;
using (SqlConnection target = new SqlConnection(targetConnection.ConnectionString))
{
target.Open();
if (db.DbId == int.MinValue)
{
logX.loggerX.Warn(
string.Format("Collection firewall rules. Incorrect dbid:{0} for db: {1}", db.DbId, db.Name));
continue;
}
// Fetch server level firewall rules
if (db.Name == Constants.MASTER_DB_NAME)
{
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, SERVER_FIREWALL_QUERY, null))
{
while (rdr.Read())
{
SqlString name = rdr.GetSqlString(FieldName);
SqlString startIPAddress = rdr.GetSqlString(FieldStartIPAddress);
SqlString endIPAddress = rdr.GetSqlString(FieldEndIPAddress);
// SQLsecure 3.1 (Anshul Aggarwal) - Skip if rule contains invalid values.
if (name == null || string.IsNullOrWhiteSpace(name.Value) ||
startIPAddress == null || string.IsNullOrWhiteSpace(startIPAddress.Value) ||
endIPAddress == null || string.IsNullOrWhiteSpace(endIPAddress.Value))
{
logX.loggerX.Warn(
string.Format("Incorrect server firewall rules for db: {0} name: {1} startip: {2} endip: {3}", db, name, startIPAddress, endIPAddress));
continue;
}
AzureSqlDBFirewallRule rule = new AzureSqlDBFirewallRule(
name.Value,
db.DbId, true,
startIPAddress.Value,
endIPAddress.Value);
firewallRules.Add(rule);
}
}
}
// SQLSECU-1772 - Unable to snapshot Azure DB databases with a dash in the name.
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, string.Format(DB_FIREWALL_QUERY, Sql.SqlHelper.CreateSafeDatabaseName(db.Name)), null))
{
while (rdr.Read())
{
SqlString name = rdr.GetSqlString(FieldName);
SqlString startIPAddress = rdr.GetSqlString(FieldStartIPAddress);
SqlString endIPAddress = rdr.GetSqlString(FieldEndIPAddress);
// SQLsecure 3.1 (Anshul Aggarwal) - Skip if rule contains invalid values.
if (name == null || string.IsNullOrWhiteSpace(name.Value) ||
startIPAddress == null || string.IsNullOrWhiteSpace(startIPAddress.Value) ||
endIPAddress == null || string.IsNullOrWhiteSpace(endIPAddress.Value))
{
logX.loggerX.Warn(
string.Format("Incorrect database firewall rules for db: {0} name: {1} startip: {2} endip: {3}", db, name, startIPAddress, endIPAddress));
continue;
}
AzureSqlDBFirewallRule rule = new AzureSqlDBFirewallRule(
name.Value,
db.DbId, false,
startIPAddress.Value,
endIPAddress.Value);
firewallRules.Add(rule);
}
}
}
}
// Wriye firewall rules to db.
WriteFirewallRulesToRepository(repositoryConnectionString);
}
catch (Exception ex)
{
isOk = false;
logX.loggerX.Error(string.Format("Error Getting Firewall Rules: {0}", ex.Message));
}
finally
{
targetConnection.InitialCatalog = string.Empty;
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
}
public static bool Process(
string targetConnection,
string repositoryConnection,
int snapshotid,
ref Dictionary <Sql.SqlObjectType, Dictionary <MetricMeasureType, uint> > metricsData,
ServerType serverType
)
{
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Stopwatch sw = new Stopwatch();
sw.Start();
uint numProcessedEndpoints = 0;
// Init return.
bool isOk = true;
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Process endpoints.
List <int> epList = new List <int>();
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = EndPointDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = EndPointDataTable.Create())
{
// Create the query.
string query = createQuery();
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the table objects.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve information.
SqlString name = rdr.GetSqlString(FieldName);
SqlInt32 id = rdr.GetInt32(FieldId);
SqlInt32 principalid = rdr.GetInt32(FieldPrincipalId);
SqlString state = rdr.GetSqlString(FieldState);
SqlString protocol = rdr.GetSqlString(FieldProtocol);
SqlString type = rdr.GetSqlString(FieldType);
SqlString isadminendpoint = rdr.GetSqlString(FieldIsAdminEndpoint);
// Add endpoint id to the list for permission procesing.
Debug.Assert(!id.IsNull);
epList.Add(id.Value);
// Update the datatable.
DataRow dr = dataTable.NewRow();
dr[EndPointDataTable.ParamSnapshotid] = snapshotid;
dr[EndPointDataTable.ParamPrincipalid] = principalid;
dr[EndPointDataTable.ParamEndpointid] = id;
dr[EndPointDataTable.ParamName] = name;
dr[EndPointDataTable.ParamType] = type;
dr[EndPointDataTable.ParamProtocol] = protocol;
dr[EndPointDataTable.ParamState] = state;
dr[EndPointDataTable.ParamIsadminendpoint] = isadminendpoint;
dr[EndPointDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
numProcessedEndpoints++;
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
}
}
}
}
catch (SqlException ex)
{
string strMessage = "Processing endpoints";
logX.loggerX.Error("ERROR - " + strMessage, ex);
Sql.Database.CreateApplicationActivityEventInRepository(repositoryConnection,
snapshotid,
Collector.Constants.ActivityType_Error,
Collector.Constants.ActivityEvent_Error,
strMessage + ex.Message);
AppLog.WriteAppEventError(SQLsecureEvent.ExErrExceptionRaised, SQLsecureCat.DlDataLoadCat,
" SQL Server = " + new SqlConnectionStringBuilder(targetConnection).DataSource +
strMessage, ex.Message);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
// Load endpoint permissions.
if (isOk)
{
if (!ServerPermission.Process(targetConnection, repositoryConnection, snapshotid, SqlObjectType.Endpoint, epList, serverType))
{
logX.loggerX.Error("ERROR - error encountered in processing end point permissions");
isOk = false;
}
}
uint oldMetricCount = 0;
uint oldMetricTime = 0;
// See if User is already in Endpoint Dictionary
// ----------------------------------------------
Dictionary <MetricMeasureType, uint> de;
if (metricsData.TryGetValue(SqlObjectType.Endpoint, out de))
{
de.TryGetValue(MetricMeasureType.Count, out oldMetricCount);
de.TryGetValue(MetricMeasureType.Time, out oldMetricTime);
}
else
{
de = new Dictionary <MetricMeasureType, uint>();
}
de[MetricMeasureType.Count] = numProcessedEndpoints + oldMetricCount;
de[MetricMeasureType.Time] = (uint)sw.ElapsedMilliseconds + oldMetricTime;
metricsData[SqlObjectType.Endpoint] = de;
return(isOk);
}
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);
}
private static bool processMembers(
ServerVersion version,
string targetConnection,
string repositoryConnection,
int snapshotid,
Database database,
Dictionary <string, KeyValuePair <int, string> > nameDictionary
)
{
Debug.Assert(version != ServerVersion.Unsupported);
Debug.Assert(!string.IsNullOrEmpty(targetConnection));
Debug.Assert(!string.IsNullOrEmpty(repositoryConnection));
Debug.Assert(database != null);
Debug.Assert(nameDictionary != null);
Program.ImpersonationContext wi = Program.SetLocalImpersonationContext();
// Process database role members.
bool isOk = true;
using (SqlConnection target = new SqlConnection(targetConnection),
repository = new SqlConnection(repositoryConnection))
{
try
{
// Open repository and target connections.
repository.Open();
Program.SetTargetSQLServerImpersonationContext();
target.Open();
// Use bulk copy object to write to repository.
using (SqlBulkCopy bcp = new SqlBulkCopy(repository))
{
// Set the destination table.
bcp.DestinationTableName = DatabaseRoleMemberDataTable.RepositoryTable;
bcp.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
// Create the datatable to write to the repository.
using (DataTable dataTable = DatabaseRoleMemberDataTable.Create())
{
// Create the query.
string query = createRoleMemberQuery(version, database);
Debug.Assert(!string.IsNullOrEmpty(query));
// Query to get the role member objects.
// Note : this query does not return public role members, so we
// have to do special processing for public role members. See below.
using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(target, null,
CommandType.Text, query, null))
{
while (rdr.Read())
{
// Retrieve and setup the table row.
if (version == ServerVersion.SQL2000)
{
KeyValuePair <int, string> role, member;
if (nameDictionary.TryGetValue((string)rdr[0], out role) &&
nameDictionary.TryGetValue((string)rdr[1], out member))
{
DataRow dr = dataTable.NewRow();
dr[DatabaseRoleMemberDataTable.ParamDbid] = database.DbId;
dr[DatabaseRoleMemberDataTable.ParamGroupuid] = role.Key;
dr[DatabaseRoleMemberDataTable.ParamRolememberuid] = member.Key;
dr[DatabaseRoleMemberDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseRoleMemberDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
}
else
{
logX.loggerX.Warn("WARN - uid not found for db role member", (string)rdr[0], ", or ", (string)rdr[1]);
}
}
else
{
DataRow dr = dataTable.NewRow();
dr[DatabaseRoleMemberDataTable.ParamDbid] = database.DbId;
dr[DatabaseRoleMemberDataTable.ParamGroupuid] = rdr.GetSqlInt32(0);
dr[DatabaseRoleMemberDataTable.ParamRolememberuid] = rdr.GetSqlInt32(1);
dr[DatabaseRoleMemberDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseRoleMemberDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
}
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - writing database role members to Repository, ", ex);
isOk = false;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - writing database role members to Repository, ", ex);
isOk = false;
}
}
}
// Now write all SQL user, windows user and windows group as a
// member of the public role. Note: the queries used above
// do not return any public role members. So we have to do
// special processing here.
const int PublicRoleUid = 0;
dataTable.Clear();
foreach (KeyValuePair <int, string> dbprincipal in nameDictionary.Values)
{
// Add row to the data table, if a user.
if (isUserPrincipal(dbprincipal.Value))
{
DataRow dr = dataTable.NewRow();
dr[DatabaseRoleMemberDataTable.ParamDbid] = database.DbId;
dr[DatabaseRoleMemberDataTable.ParamGroupuid] = PublicRoleUid;
dr[DatabaseRoleMemberDataTable.ParamRolememberuid] = dbprincipal.Key;
dr[DatabaseRoleMemberDataTable.ParamSnapshotid] = snapshotid;
dr[DatabaseRoleMemberDataTable.ParamHashkey] = "";
dataTable.Rows.Add(dr);
}
// Write to repository if exceeds threshold.
if (dataTable.Rows.Count > Constants.RowBatchSize)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - writing database role members to Repository, ", ex);
isOk = false;
}
}
}
// Write any items still in the data table.
if (dataTable.Rows.Count > 0)
{
try
{
bcp.WriteToServer(dataTable);
dataTable.Clear();
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - writing database role members to Repository, ", ex);
isOk = false;
}
}
}
}
}
catch (SqlException ex)
{
logX.loggerX.Error("ERROR - exception encountered when processing database role members, ", ex);
isOk = false;
}
finally
{
Program.RestoreImpersonationContext(wi);
}
}
return(isOk);
}
