コード例 #1
0
        /// <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);
            }
        }
コード例 #2
0
        public static int ExecuteNonQuery(
            SqlConnection connection,
            CommandType commandType,
            string commandText,
            params SqlParameter[] commandParameters
            )
        {
//            using (logX.loggerX.DebugCall())
            {
                Debug.Assert(connection != null);

                // Create a command and prepare it for execution
                int retval = 0;
                using (SqlCommand cmd = new SqlCommand())
                {
                    // Prepare and execute the command.
                    try
                    {
                        // Create the command object.
                        prepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);

                        cmd.CommandTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();

                        // Execute the command
                        retval = cmd.ExecuteNonQuery();

                        // Detach the SqlParameters from the command object, so they can be used again
                        // Detach the SqlParameters from the command object, so they can be used again.
                        // HACK: There is a problem here, the output parameter values are fletched
                        // when the reader is closed, so if the parameters are detached from the command
                        // then the SqlReader can´t set its values.
                        // When this happen, the parameters can´t be used again in other command.
                        bool canClear = true;
                        foreach (SqlParameter commandParameter in cmd.Parameters)
                        {
                            if (commandParameter.Direction != ParameterDirection.Input)
                            {
                                canClear = false;
                            }
                        }

                        if (canClear)
                        {
                            cmd.Parameters.Clear();
                        }
                    }
                    catch (SqlException ex)
                    {
                        logX.loggerX.Error("ERROR: ExecuteNonQuery encounterd an exception", ex);
                        throw;
                    }
                }

                return(retval);
            }
        }
コード例 #3
0
        private static void writeDataTables(
            SqlBulkCopy bcpAccount,
            DataTable dtAccount,
            SqlBulkCopy bcpMembership,
            DataTable dtMembership
            )
        {
            // Write the account table first, because there are FK constraints.
            if (dtAccount.Rows.Count != 0)
            {
                bcpAccount.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
                bcpAccount.WriteToServer(dtAccount);
                dtAccount.Clear();
            }

            // Write the membership table.
            if (dtMembership.Rows.Count != 0)
            {
                bcpMembership.BulkCopyTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();
                bcpMembership.WriteToServer(dtMembership);
                dtMembership.Clear();
            }
        }
コード例 #4
0
        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);
        }
コード例 #5
0
ファイル: Schema.cs プロジェクト: roshnimehata/SQLSecure
        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);
        }
コード例 #6
0
        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);
        }
コード例 #7
0
        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);
        }
コード例 #8
0
        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);
        }
コード例 #9
0
        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);
        }
コード例 #10
0
        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);
        }
コード例 #11
0
        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);
        }
コード例 #12
0
ファイル: Endpoint.cs プロジェクト: roshnimehata/SQLSecure
        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);
        }
コード例 #13
0
        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);
        }
コード例 #14
0
        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);
        }
コード例 #15
0
        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);
        }
コード例 #16
0
        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);
        }
コード例 #17
0
        /// <summary>
        /// Construcs a command object and calls the ExecuteReader
        /// method.
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(
            SqlConnection connection,
            SqlTransaction transaction,
            CommandType commandType,
            string commandText,
            SqlParameter[] commandParameters
            )
        {
//            using (logX.loggerX.DebugCall())
            {
                Debug.Assert(connection != null);

                // Create/initialize a command and execute reader on it.
                SqlDataReader dataReader = null;
                using (SqlCommand cmd = new SqlCommand())
                {
                    //****************************************************************************
                    // NOTE: The command timeout is hard coded at this point, but should be made
                    // configurable in the future.
                    //****************************************************************************
                    cmd.CommandTimeout = SQLCommandTimeout.GetSQLCommandTimeoutFromRegistry();

                    // Prepare and execute the command.
                    try
                    {
                        // Create the command object.
                        prepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);

                        // Execute command.
                        dataReader = cmd.ExecuteReader();

                        // Detach the SqlParameters from the command object, so they can be used again.
                        // HACK: There is a problem here, the output parameter values are fletched
                        // when the reader is closed, so if the parameters are detached from the command
                        // then the SqlReader can´t set its values.
                        // When this happen, the parameters can´t be used again in other command.
                        bool canClear = true;
                        foreach (SqlParameter commandParameter in cmd.Parameters)
                        {
                            if (commandParameter.Direction != ParameterDirection.Input)
                            {
                                canClear = false;
                            }
                        }

                        if (canClear)
                        {
                            cmd.Parameters.Clear();
                        }
                    }
                    catch (SqlException ex)
                    {
                        logX.loggerX.Error("ERROR: ExecuteReader encounterd an exception", ex);
                        if (dataReader != null)
                        {
                            dataReader.Dispose();
                            dataReader = null;
                        }
                        throw;
                    }
                }

                return(dataReader);
            }
        }
コード例 #18
0
        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);
        }
コード例 #19
0
ファイル: FilterRule.cs プロジェクト: roshnimehata/SQLSecure
        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);
        }
コード例 #20
0
        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);
        }
コード例 #21
0
        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);
        }
コード例 #22
0
        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);
        }
コード例 #23
0
        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);
        }