Post reply

SQL Server 2014 Auditing advice

  • June 23, 2015 at 1:34 pm

    #299366

    We are going to have State auditors come to our campus. In the past, we have only been auditing "Failed Logins". I was asked in our last security meeting what do people in the SQL Server 2014 community commonly audit when they use SQL Auditing. I said I would pose this to the SQL Server Forum community. Thank you in advance.

    Charlie

  • June 23, 2015 at 1:50 pm

    #1807541

    i'd start with the basics: who has sysadmin, who has db_owner and in what database.

    grab a copy of Brent Ozar's sp_blitz, and run that, it gives a nice, quick overview of a lot of stuff related to security.

    I have this old snippet, which i use to see the last time a bad password was put in, and if someone is set to have non-expiring passwords, for example.

    i have to assume domain users follow the domain password policy, since they show up as null, but there's a bunch of neat stuff int he loginproperty functions you can review.

    SELECT

    name,

    type_desc,

    create_date,

    modify_date,

    default_database_name,

    IS_SRVROLEMEMBER ('sysadmin',name) AS [Is_ServerAdmin_Sysadmin],

    LOGINPROPERTY (name,'BadPasswordCount') AS BadPasswordCount,

    LOGINPROPERTY (name,'BadPasswordTime') AS BadPasswordTime,

    LOGINPROPERTY (name,'DaysUntilExpiration') AS DaysUntilExpiration,

    LOGINPROPERTY (name,'DefaultDatabase') AS DefaultDatabase,

    LOGINPROPERTY (name,'DefaultLanguage') AS DefaultLanguage,

    LOGINPROPERTY (name,'HistoryLength') AS HistoryLength,

    LOGINPROPERTY (name,'IsExpired') AS IsExpired,

    LOGINPROPERTY (name,'IsLocked') AS IsLocked,

    LOGINPROPERTY (name,'IsMustChange') AS IsMustChange,

    LOGINPROPERTY (name,'LockoutTime') AS LockoutTime,

    LOGINPROPERTY (name,'PasswordHash') AS PasswordHash,

    LOGINPROPERTY (name,'PasswordLastSetTime') AS PasswordLastSetTime,

    LOGINPROPERTY (name,'PasswordHashAlgorithm') AS PasswordHashAlgorithm

    from sys.server_principals

    ORDER BY type_desc,name

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 23, 2015 at 1:53 pm

    #1807544

    Thank you for the information Lowell.

    Charlie

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply