IBM Security Guardium

 View Only
Expand all | Collapse all

Huge Traffic from Source Prog=MICROSOFT SQL SERVER MANAGEMENT STUDIO - TRANSACT-SQL INTELLISENSE

  • 1.  Huge Traffic from Source Prog=MICROSOFT SQL SERVER MANAGEMENT STUDIO - TRANSACT-SQL INTELLISENSE

    Posted 8 days ago

    Hello Seniors,

    I have one concern. I have enabled Selective Audit  Policy to only capture Traffic from single  DB Server(1.1.1.1) , Database Name (Test) & Database User (GuardTest). The database server is MS SQL Server.  I am login and performing some manual DB Operation using "MICROSOFT SQL SERVER MANAGEMENT STUDIO". and it is getting captured by Guardium very well. The Source Program showing in the Report is "MICROSOFT SQL SERVER MANAGEMENT STUDIO - QUERY". That is correct & fine.

    However, The concern Is ,  I am seeing lot of other DB Traffic(Transactions) which is from Source Program "MICROSOFT SQL SERVER MANAGEMENT STUDIO - TRANSACT-SQL INTELLISENSE" AND "MICROSOFT SQL SERVER MANAGEMENT STUDIO". 

    AND the FULL SQL are totally different those are even not executed by me. These FULL SQLs are like seems like system generated. I am not executing them... So, I am not sure about these traffic. Can anyone guide - throw some light on this scenario. 

    For example :-

    sp_executesql  SELECT
    clmns.name AS [Name],
    clmns.column_id AS [ID],
    clmns.is_nullable AS [Nullable],
    clmns.is_computed AS [Computed],
    CAST(ISNULL(cik.index_column_id, ?) AS bit) AS [InPrimaryKey],
    clmns.is_ansi_padded AS [AnsiPaddingStatus],
    CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
    CAST(ISNULL(cc.is_persisted, ?) AS bit) AS [IsPersisted],
    ISNULL(clmns.collation_name, ?) AS [Collation],
    CAST(ISNULL((select TOP ? ? from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), ?) AS bit) AS [IsForeignKey],
    clmns.is_identity AS [Identity],
    CAST(ISNULL(ic.seed_value,?) AS numeric(?)) AS [IdentitySeedAsDecimal],
    CAST(ISNULL(ic.increment_value,?) AS numeric(?)) AS [IdentityIncrementAsDecimal],
    (case when clmns.default_object_id = ? then ? when d.parent_object_id > ? then ? else d.name end) AS [Default],
    (case when clmns.default_object_id = ? then ? when d.parent_object_id > ? then ? else schema_name(d.schema_id) end) AS [DefaultSchema],
    ISNULL(dc.Name, ?) AS [DefaultConstraintName],
    (case when clmns.rule_object_id = ? then ? else r.name end) AS [Rule],
    (case when clmns.rule_object_id = ? then ? else schema_name(r.schema_id) end) AS [RuleSchema],
    CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, ?),?) AS bit) AS [IsDeterministic],
    CAST(ISNULL(COLUMNPROPERTY(clmns.object_id, clmns.name, ?),?) AS bit) AS [IsPrecise],
    ISNULL(ic.is_not_for_replication, ?) AS [NotForReplication],
    CAST(COLUMNPROPERTY(clmns.object_id, clmns.name, ?) AS bit) AS [IsFullTextIndexed],
    CAST(COLUMNPROPERTY(clmns.object_id, clmns.name, ?) AS int) AS [StatisticalSemantics],
    CAST(clmns.is_filestream AS bit) AS [IsFileStream],
    CAST(clmns.is_sparse AS bit) AS [IsSparse],
    CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
    usrt.name AS [DataType],
    s1clmns.name AS [DataTypeSchema],
    ISNULL(baset.name, ?) AS [SystemType],
    CAST(CASE WHEN baset.name IN (?, ?) AND clmns.max_length <> -? THEN clmns.max_length/? ELSE clmns.max_length END AS int) AS [Length],
    CAST(clmns.precision AS int) AS [NumericPrecision],
    CAST(clmns.scale AS int) AS [NumericScale],
    ISNULL(xscclmns.name, ?) AS [XmlSchemaNamespace],
    ISNULL(s2clmns.name, ?) AS [XmlSchemaNamespaceSchema],
    ISNULL( (case clmns.is_xml_document when ? then ? else ? end), ?) AS [XmlDocumentConstraint],
    CASE WHEN usrt.is_table_type = ? THEN ? ELSE ? END AS [UserType],
    CAST(? AS bit) AS [IsDroppedLedgerColumn],
    CAST(

            CASE

            WHEN ep1.value IS NOT NULL or ep2.value IS NOT NULL or ep3.value IS NOT NULL or ep4.value IS NOT NULL THEN ?

            ELSE ?

            END

           AS bit) AS [IsClassified]
    FROM
    sys.all_views AS v
    INNER JOIN sys.all_columns AS clmns ON clmns.object_id=v.object_id
    LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and ?=ik.is_primary_key
    LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and ? = cik.is_included_column
    LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
    LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
    LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
    LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
    LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
    LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
    LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
    LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = ?) and (baset.is_assembly_type = ?))
    LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
    LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
    LEFT OUTER JOIN sys.tables t ON t.object_id = clmns.object_id
    LEFT OUTER JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    LEFT OUTER JOIN sys.extended_properties AS ep1 ON clmns.object_id = ep1.major_id AND clmns.column_id = ep1.minor_id and ep1.name = ?
    LEFT OUTER JOIN sys.extended_properties AS ep2 ON clmns.object_id = ep2.major_id AND clmns.column_id = ep2.minor_id and ep2.name = ?
    LEFT OUTER JOIN sys.extended_properties AS ep3 ON clmns.object_id = ep3.major_id AND clmns.column_id = ep3.minor_id and ep3.name = ?
    LEFT OUTER JOIN sys.extended_properties AS ep4 ON clmns.object_id = ep4.major_id AND clmns.column_id = ep4.minor_id and ep4.name = ?
    WHERE
    (clmns.name=?)and((v.type = ?)and(v.name=? and SCHEMA_NAME(v.schema_id)=?))

    DECLARE @edition sysname;
    if @edition = ?

      select ? as ConnectionProtocol

    else

      exec ('select CONVERT(nvarchar(40),CONNECTIONPROPERTY(''net_transport'')) as ConnectionProtocol')


    ------------------------------
    Sincerely,
    Akash Parmar
    ------------------------------


  • 2.  RE: Huge Traffic from Source Prog=MICROSOFT SQL SERVER MANAGEMENT STUDIO - TRANSACT-SQL INTELLISENSE

    Posted 7 days ago

    Hi Akash,

    This is what I have with me for your query:

    IntelliSense:-
    The editors in SQL Server Management Studio support Microsoft IntelliSense options that reduce typing, provide quick access to syntax information, or make it easier to view the delimiters of complex expressions.

    sp_executesql (Transact-SQL):-
    Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

    Logging of SELECT Statements:
    This is because a selective audit policy should not prevent logging of certain SQLs that may be needed for other functions, like application user translation.



    ------------------------------
    GIRISH RAMESH BABU
    ------------------------------

    Original Message