Monday, April 30, 2018

Security: Create and view Audit Logs

It is important to have a secure environment, enforcing the least privilege principle in your servers and databases, but this can be difficult to achieve on large environments, where a lot of people have elevated privileges or several people have permissions to perform tasks over your data or schema.

As an additional measure, an audit log should be implemented in those scenarios and review these often.

SQL Server provides the functionality to implement audit logs at Server and Database level. Starting SQL Server 2008, all SQL Server editions support Server level audits. Database audits are supported beginning with SQL Server 2016 (13.x) SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

On this post I will show you how to configure and view audit logs:

Implement Audit Log

Under security, go to Audits and select New audit...

Specify a name for the audit and select an audit destination (I personally prefer a file location).
Select the desired parameters and click OK.

The Audit is now created, for it to work just enable audit.
NOTE: before making any changes to the audit configuration, you will need to disable it first.

Implement Server Level Audit

We have created our audit location, now we will proceed to create the actions we want to audit at server level.

Under Security folder, locate  Server Audit Specifications, right click and select New Server Audit Specification...

In the next window, Specify a name for the audit (you can have more than one) and select the audit created in the previous step.

Choose the action or actions to be audited. To check what is included on each action, please read here from Microsoft documentation.

Once you are done click OK to create the audit specification.
For this example I just have included the DBCC action to be audited (as we will see later on this post)

The Audit specification is also created disabled, to make it work, enable it as shown in the image.

NOTE: before making any changes to the audit specification, you will need to disable it first.

Implement Database Level Audit

The process is relatively the same to create a database level audit specification, the main difference is that the option is located under the Security folder on each database.

Right click on the Database Audit Specification folder and then New Database Audit Specification...

Select the name of the Database Audit and the audit destination (the same created in first step for this example).
Another of the key differences for Database Audits is that we have to select the object or schema we want to check.
Select the actions to audit (you can see description for each one here), and then click Ok.

For this example I will audit the SELECT command over the a table named Course in a sample School database.

After the creation of the audit specification, you must enable it to run.

NOTE: before making any changes to the audit configuration, you will need to disable it first.

Review audit logs

Having the audits configured and running is just half of the work, it will worth nothing if you don't proactively review the logs often.
There are 2 options to review the logs, using SSMS and T-SQL

For this example I have executed some sentences to trigger the server and database audits configured above so we can see some data.


To check event logs using Management Studio, just select the Audit created, right click on it and select the View Audit Logs option

The audit log will display in a SQL Server log kind type, and you can see some of the examples I have triggered


For using this option, you use the DMF sys.fn_get_audit_file, specifying the file location, and for file name use *.sqlaudit to include all the files.

This is the query I use for this example:

SELECT event_time,action_id,statement,database_name,server_principal_name 
FROM sys.fn_get_audit_file ('C:\temp\TestAudit_*.sqlaudit',default,default);  

The output of the query looks like this:

I have marked in yellow the actions that have been captured in the database specification.

With this info you are good to go configuring your own audits for your Mission critical servers and review them often, also you could implement an alert or automatic reporting logic to be notified on suspicious activity.


Wednesday, April 25, 2018

Quick tip: format source code in html

I run a technical blog, so it is necessary to put code in almost every post. Most of the GUIs we use to do our work (let's say SSMS or Visual Studio) already provide this functionality, but when you copy and paste the code to the blog, you need to format it first, to pass from this:

DELETE FROM database1.schema1.table1 WHERE column1=valuex
/* generic comment */
SELECT * FROM database2.schema2.table2 WHERE column2=456

To this:

DELETE FROM database1.schema1.table1 WHERE column1=valuex
/* generic comment */
SELECT * FROM database2.schema2.table2 WHERE column2=456

These are some sites I use: 

My site of choice, it supports several languages and you can select predefined styles and customize them on-site.

Only for SQL, it provides a more robust keyword detection, but the format and customization is more limited, it also offers some obfuscation options.

Also a good option, supports various languages, but a little bit more complex to use than

Monday, April 23, 2018

Configure Min and Max memory in Reporting Services

As any of the available SQL Server services, Reporting Services will use all of the server memory if needed, in some cases when you have multiple products configured for the same server, you need to limit these values, we use WorkingSetMaximum and WorkingSetMinimum configuration values to achieve this task.

Where to set these values?

Find the .config file

We need to localize the RsReportServer.config configuration file in our server, this file is in the SQL Server installation folder under MSRS00.XXXX\Reporting Services\ReportServer where the 00 is your reporting services version and the XXXXX are your instance name.
NOTE: Before changing any parameter in this file, also take a backup of the file contents first.

Find the Service tag

Once you have located the file, proceed to find the <Service></Service> tags, and add the <WorkingSetMinimum> and <WorkingSetMaximum> as desired, values are specified in kilobytes, so for example, to set a value of 4 GB you must put 4000000.
You can see a sample code of a min memory of 2GB and a max memory of 4 GB



When the value for WorkingSetMaximum is reached, the report server does not accept new request, so please have this in mind before configuring this value.
Also note that a service restart is required for the values to take effect.