Category: Uncategorized

I wrote about setting up a basic server audit recently. That showed about how a server level audit is set up, but that’s a just a shell. The audit itself is just a container in which you can store various audited items. Like a SQL Server Agent job, it doesn’t do anything until you add some details.
One of the details that I think is worth adding is the failed login audit at the server level. Finding a large number of failed logins can clue you in to some hacking going on, so it can be good to log these.
To add these, you first need to add a server audit specification, which is like a job step. It’s a detail at the server level. Using the SSMS GUI, you can do this by right clicking the Server Audit Specification under Security. Select “New Audit Specification”
 
That gives you a dialog where you can add a name:

And then select an audit to which you assign this particular detail.

For my purposes, I need to audit failed logins, so I select that change group. You can get all change groups in BOL.

Once this is done, your audit appears in the folder in SSMS.

Now when someone tries to log in and can’t, you can view this in the logs:

I got new drives for my Windows Home Server the other day and decided to add some space to the box. I don’t need the space so much as I’d like to be sure that I’m protected with some extra drives.

The first step, obviously, is to shut down the server and add the physical hardware. That was easy enough. Then you connect to the Home Server console and click the Server Storage link and verify that the drive is there. It will be listed as non-WHS storage.

You select it, click “Add” at the top, and a few minutes later the space is available.

I had 3.3TB on my server and added another 2TB to it. It took about 2 minutes after I installed the drive, and I was more protected. I am waiting on an RMA of another 2TB drive, and once I get that, I’ll add it in as well to give myself a little more protection and storage space.

One of my goals this year was to spend some time learning Reporting Services. Not necessarily do the 10,000 hours to become an expert, but perhaps the 100 hours or so Andy Warren mentioned in his Professional Development presentation.

I’ve done a little getting SSRS installed and running on a few machines, and decided to document a basic report with the BIDS SSRS wizard. It’s basic, but it shows the easiest way to build a basic report.

First start up BIDS and create a new project.

The wizard starts with an opening screen, which is a waste. So be sure you check the “Don’t show this page again” box.

The first thing you get is a data source box, which is needed for any report.

 

I also checked the “Shared data source” box so that this source can be shared on other reports.

You have to enter credentials, and I picked a new login/user that I set up with minimal SELECT rights to a couple databases.

Next is the query

I picked the Query Builder, though you can just enter the query. The first screen shows the basic designer.

If you right click in the top pane, you can select the “Add table”

and you’ll get a list of tables.

You can double click a table to add it, or select it and click “Add”. Click close when you are done.

You get your table(s) in the top pane, and as you select columns, they get added in the bottom pane as part of the query. If you close the Query Builder, you see your query back in the wizard.

The next step in the wizard is to pick a type of report; I left mine as a tabular report.

The next screen shows some grouping option. For basic reports, you add things to the details. If you want to group up by data that is repeated, like by Customer, add those in other boxes.

Choose a style.

And then pick a report name. 

I checked Preview Report,

and then I’d see the report in BIDS. 

Next, need to deploy this to the Report Server and make it work.

I saw someone post a question recently about tracking down who deleted a stored procedure. I suggested the default trace, and linked to a beginner article on it.

However there was another post that had a better way to track things. In SSMS there’s a report that will help you:

Right click a database, and drill down as shown above through reports to the “Schema Change History” report and select it. Previously I had issued this:

ALTER TABLE aa ADD bb varchar(20)

And when I ran my report, I found this in the list:

It doesn’t give the code, but it says that I altered the proc today.

I then tested for stored procedure. I created, and then deleted a quick proc, so it only existed on my system for a few minutes. When I ran the schema report again, despite this proc no longer being around, it was there:

This comes off the default trace, so there’s only a limited amount of data kept. If you catch something quick enough, however, you can easily track down some information about it.