Government or corporate regulations may factor into the security of your database. Learn how to audit the database for logins and other interactions to track security on the database.
- [Narrator] Partly performance, and partly security related, auditing a database provides insight into how the database is performing, but also how users are interacting with the database. Oftentimes there may be government or other regulatory requirements for audits on a database, which makes this an important topic. Auditing an instance of SQL Server, the database engine, or an individual database involves tracking and logging events that occur on the database engine. SQL Server lets you create audits at the server level, and at the database level.
And they can contain server audit specifications for server-level events and database audit specifications for database-level events. They track and log events either to files, or to the security or application logs on the Windows operating system. So they can be written to either event logs or to files. There are several types of auditing or several levels of auditing for SQL Server depending on the government, or regulatory standard requirements for your installation.
SQL Server audit provides the tools and processes that you must have to enable, store, and view audits included with SQL Server and on Windows. All editions of SQL Server support server-level audits, however, for database-level audits, these are limited to Enterprise, Developer, and Evaluation editions. They're not supported on SQL Compact edition. So, let's go ahead and take a look at how we create audits on SQL Server. Remember, the auditing is available at database-level, and at the server-level.
In order to create a server-level audit specification or database-level audit specification, we need to expand the security for the database or the server. We can see a database audit specification folder here, and a server audit specification folder here. We cannot create any server specifications until we have an audit specification created. So, in other words, we can't continue until we actually create an audit first. That's done from the server level, under the security folder for audits.
So let's create a new audit. You'll notice that the audit name is filled in automatically for you, and it's based on a date and time stamp. We can also specify a queue delay in milliseconds. Looking at the queue delay, the time is specified in milliseconds, and it indicates the amount of time that can elapse before audit actions are forced to be processed. With a value of zero, it means that it's synchronous delivery, so everything is processed instantaneously, and as it occurs. We can also chose to take an action based on an audit log failure.
We can continue the auditing of the system. We can chose to shut down the server, which potentially is a severe form of mitigating a problem. And we can also chose to fail that operation that was being audited at the time. Deselections will be dictated based on the specific regulatory requirements for auditing on your database. And those are typically driven based on the type of data that you're actually storing. Our audit destination can be written to a file.
It can be written to a security log, or the application log on Windows. And you'll note that if I chose the security log, the remaining options from this dialog are grayed out. So, only when we're dealing with a file storage, do we actually have to specify a file path, and in this case, we'll put it on our DB backup folder on the D drive. We can also specify a maximum limit for the files, and a maximum rollover files. If we have the check box for unlimited selected, then basically what that means is, our audit files will continue to be stored on that drive, until it fills up.
We can also specify a maximum number of files that we can store. Once we've reached the maximum, the audit will stop. If we do decide to go with the maximum rollover files, and clear the unlimited check box, now we can specify as specific number of files that we want to store, and if we reach that maximum number of files, the audit will start deleting the older files to make room for the newer files. So, a lot of flexibility here in how we deal with the files that we have available. Also, if we specify the maximum files, and clear the unlimited maximum file check box, we can specify a maximum file size.
So again, unlimited means that the file size can grow unlimited, again realizing that it will chew up potentially all of the hard drive space, or you can decide in a megabyte, gigabyte, or terabyte limit, how large those files should be. And we can also choose to reserve disk space. So if we have set a specific amount of files, you'll notice that when I selected this option it says, cannot reserve disk space when maximum file size is unlimited? It's telling us that we can't deal with unlimited file size.
So what we have to do is, come back up and specify a maximum file size, so instead of being unlimited, we actually have to put a value in this box. Once we choose a file size, our error message goes away, and we have now reserved six megabytes of file size on the disk. Now this is rather small, so be considerate when you're specifying your maximum file size in reserving disk space for that. Let's change it to six gigabytes just to be a little more realistic. So I'm going to select okay, and we're going to have SQL Server create that audit for us.
Now that we have that audit created, we can now go in and create a new server audit specification. Again, I'm just going to leave it as the default name that is selected. Now we can choose that audit that we've created, and let me expand this window a little bit and see how we can now go in and select an audit type. In this case, let's simply audit the log in change password group. Depending on what you selected here, you may or may not have an object class that you can choose, and you may or may not have object scheme, his name and principals.
All of these are grayed out when you cannot select them. So we're just concerned about a login change password group for now, and we select okay, and now we have our server audit specification created. Also, we can create a new database audit specification, now that the audit is in place. Selecting that audit, we now have the option to go in and change a specific audit action type. And this is at the database level, so we can go in here, and we can audit some specific things that take place within our database.
Perhaps we want to audit a schema object ownership change. And again, like we did with the server audit, some of these boxes are unable, or disabled based on the audit action type that you have chosen. Clicking okay, we now have a database audit specification. So this is a quick overview of creating audits in your SQL Server database, creating an audit first, and then going in and looking at the various events that you can create at the server or at the database level.
This course is also ideal for anybody preparing for the Administering a SQL Database Infrastructure (70-764) exam, one of two exams necessary to earn an MCSA: SQL 2016 Database Administration certification.
- Installation and configuration considerations
- Data storage strategies
- Configuring database instances
- Performance considerations
- Security considerations
- Managing roles and users
- Managing data
- High-availability options