Audit Webinar Tomorrow (4 September 2014)

I will be given a webinar on how to audit SQL Server through MSSQLTips.com.

Webinar Registration Link

The abstract:

Don’t become a statistic. With the numerous data breaches and internal data theft, securing your SQL Server environment can help keep your company out of the news. Unfortunately, a single SQL Server configuration, coding technique or operational practice in your environment can put you at risk. Now is the time to be proactive for your own peace of mind or prepare for your organization’s next formal SQL Server audit. This session will provide key scripts and reports to build your SQL Server auditing checklist.

In this session you will learn about the following topics:

  • Permissions – Elevated permissions for logins at the instance and database level
  • Logins – Creation and modification of logins as well as failed login attempts
  • Operations – Out of cycle backups, phantom SQL Server Agent Jobs and changes to standard operating procedures that should raise the red flag
  • Configurations – Whether it is xp_cmdshell, Linked Servers or password policy changes, these need to be recorded
  • Code Changes – Code changes sneaking into production would never happen, so be able to prove it
  • Data Auditing – With awareness for confidential data rising, report on who accessed and when

Understanding the Three A’s of Security for SQL Server

Do you know what the “Three A’s of Security” are and how they apply to Microsoft SQL Server? Let’s look at them as they are important for managing security properly on a given Microsoft SQL Server.

Authentication

Authentication is determining who a person/process is.

When a connection is made, it’s important to know what account is making that connection. Authentication is the process of validating who that connection is. There are two types of authentication with respect to users connecting to SQL Server:

  • SQL Server’s build-in logins
  • Windows logins

In the first case, SQL Server has stored logins with passwords and if SQL Server is set to mixed mode authentication, these logins can be used to make a connection to a given SQL Server. More on mixed mode authentication in another post. In the second case, a Windows user can connect to SQL Server. This access can either be granted by to the Windows user directly or via a Windows security group.

Obviously, there are times when SQL Server is able to authenticate a connection but that connection isn’t allowed access to any resources, to include simply logging in to the SQL Server itself. That leads us to the next A.

Authorization

Authorization is determining what a person/process is allowed to do.

Once SQL Server knows who the connection is, the next question is what should that connection have access to? This is authorization. The initial check is to see if the account in question should even be able to log in to the SQL Server. If it does have that permission (you’ll see it as CONNECT SQL if you look at the permissions), the next question is what other permissions should it have. This can be at the server level and it can be within the individual databases.

Auditing

Auditing is keeping track of what happens for a person/process.

Auditing is the record keeping involved with tracking various security events. For instance, SQL Server can track both successful and failed logins. Typically, it’s recommended that at least failed logins be tracked. SQL Server can, depending on the edition, audit just about anything you want to track. We’ll cover auditing in more detail in later posts.

 

Auditing VMware vCenter Actions (on SQL Server)

When you’ve got a SIEM appliance or application, you want actions and events going into it as a central repository. That allows you to see patterns and hopefully track incidents across systems. As a result, if you want to track actions in VMware’s vCenter and you’ve got the database hosted on SQL Server, you need to give access to the following two tables:

  • VPX_EVENT
  • VPX_EVENT_ARG

A standard tendency is to give the account that the SIEM product is using membership in the db_datareader role. Don’t do this. It’s a violation of the Principle of Least Privilege. It’s far better to create a user-defined role and granting it the appropriate permissions. Then make the account a member of the role. For instance:

USE VirtualCenterDB;
GO

CREATE ROLE [Auditor];
GO

GRANT SELECT ON OBJECT::dbo.VPX_EVENT TO [Auditor];
GRANT SELECT ON OBJECT::dbo.VPX_EVENT_ARG TO [Auditor];
GO

And then you make the user account being used by the SIEM product a member of the Auditor role.