Right now it looks like I'll be putting a training session on for my local ISACA chapter on SQL Server security and auditing SQL Server. I know there is usually a lot of griping by DBAs when auditors show up and ask about their databases. Typically it's around how the auditors are going from a checklist and aren't really well-versed in the technology itself. I'm volunteering to teach the training so that they better know what needs to be looked at from a SQL Server perspective because I've been there, too.
So what are your pain points when dealing with auditors? If you had the chance to teach them a subject or area before they walked over and started their checklist of questions, what would it be? What do you think they should be looking at? I'll try to work a lot of that into the class. The better we educate them as to what really matters, the less they'll be in our hair.
This question comes up a lot: how do I give read-only access to job status to a group of people? The answer, at least in SQL Server 2005 and 2008, seems simple: add them to the SQLAgentReaderRole role. But this doesn't actually work, as this role also has the ability to create jobs. That's not what the name implies, but that's the rights it has. And you're rather limited in the roles that can be used. Why? If you do an sp_helptext on sysjobs_view (which is what the Job Status Monitor makes a call to - you can see this if you do a Profiler or server-side trace), you'll see this nice little where clause:
WHERE (owner_sid = SUSER_SID()) OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) OR (ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1) OR ( (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1) AND (EXISTS(SELECT * FROM msdb.dbo.sysjobservers js WHERE js.server_id <> 0 AND js.job_id = jobs.job_id))) -- filter out local jobs
So how do you get around giving users the ability to create jobs? This was a question Colin Stasiuk asked yesterday on Twitter (his Twitter handle). After a bit of research he came up with this:
Creating a REAL SQLAgentReader role
It's not pretty, but without modifying sysjobs_view or giving more access than what a user needs, there's probably no way around it. Colin is still working to find a more elegant solution, so if you have one, pop over to his blog or hit him up on Twitter with your suggestions.
The video I did for Jumpstart TV on xp_cmdshell execution has hit the front page. Registration on the site is required but viewing the video (~ 5 minutes) is free.
This is a little old (five days based on the last update), but TrendMicro put on their blog about fake LinkedIn profiles which have links leading to malware.
The basics:
With respect to the last point, it's still disturbing. Again it's a case of attackers going after a legitimate and trusted site to get users to be directed to malware. In this case they aren't hacking the site, rather using the functionality provided to end users.
Hopefully by now everyone has seen this, but if not, here's a reminder to continue to spread the details. Denis Gobo made a post earlier today and Security MVP Randy Franklin Smith sent out a newsletter after being prompted by his MVP lead.
The worm is known as Conficker or Downadup and here are the details from Microsoft. It attacks a vulnerability that was patched in MS08-067 (released in October) by attacking the Windows Server service, which, by default, all Windows machines have running (even if you're running a workstation... that's how you can have file shares and share out your printer). One of the nasty sets of things it does is disable automatic updates as well as AV / Malware protection that would try and stop it. It will also potentially block certain security sites (like where AV downloads from) based on a string search (more information in the details link).
In addition, it will flood the network and launch password hack attempts against user accounts, resulting in locked user accounts if there is any account lockout policy in place (and there should be). I've already had one friend whose network got hit by this and it was a hard clean-up of their environment as a result. Therefore the best way to stop this thing is to patch. After all, that patch was released in October. That was over 3 months ago now.
One last attack vector, and that's via removable drives. That's why I blogged about disabling autoplay. Not only does it stop those annoying splash screens when you insert a CD or DVD or the dialog window asking what to do when you insert the USB drive, it also ensures that by inserting a removable drive you don't get a social engineering based attack in that dialog window.
I was browsing through the new titles that are on Safari and saw some planning guides around Windows Server 2008 (Active Directory Services, File Services, etc.). Of course, all of these are published as solution accelerators, because they are designed to assist IT professionals understand, plan, and implement solutions quicker (hence the term, solution accelerator). Some of these documents have been around for a while, but the Solution Accelerators section groups them all into one place. If you've not run across them before, you can find them here:
Microsoft TechNet : Microsoft Solution Accelerators
Among them are the Infrastructure Planning and Design Guides. There's guidance on Windows Server 2008, virtualization, and even IIS. But unfortunately, none out there yet for SQL Server. If you're looking on the security side, there are the OS security guides from Windows XP on up, including for Vista and 2008. Sorry, no Windows 7 yet.
If not, you should. Because autoplay being on means any time a removable data device, such as a USB stick, is inserted into the computer, autoplay fires up. Typically this is usually taken advantage of by CDs/DVDs to get us to a start menu. However, attackers are now writing worms which spread to removable devices and take advantage of autoplay and a social engineering trick to try and infect the system. More details here:
SANS Internet Storm Center: Conficker's autorun and social engineering
Me personally, I have autorun disabled because I don't want to see some pop-up window every time I stick a USB drive onto my computer, as I switch out between several on a daily basis. I also don't care for CDs/DVDs autostarting on me when I may be simply looking for a couple of files in a directory on the CD (such as documentation). If you want to know how to disable autorun, here's a Microsoft KB article which explains the steps (you'll need admin rights on your system):
KB953252: How to correct "disable Autorun registry key" enforcement in Windows
Don't be thrown off by the name. It was written to correct another KB article. And in the article it has the steps on how to disable autorun. You'll want to do it for all drives if you go the GPO route (which is the simplest).
I've mentioned before that I was on Twitter and I know that for some the question is still out on whether or not it is of value. For me, it's like any other tool, it depends on how you use it. If you're trying to use a hammer to dig up a garden, you can probably get it done, but it's not going to be very efficient. Twitter is the same way, except it's more along the lines of a Swiss army knife: it can be used multiple ways for multiple things. For instance, Steve Jones blogged about how it gave some levity to an otherwise forgettable day.
I use it mostly to stay in contact with others who are using SQL Server and it does a good job of that. For instance, I was able to learn about the changes in Windows Server 2008 Core with r2 from Jason Massie. It looks like it'll now support SQL Server since it has enough of the .NET Framework to permit it. This is good news. It also lets me help others out. For instance, someone was struggling with using PHPMyAdmin to upload an SQL script to a back-end MySQL database. Because of the searches I have in place, I saw that tweet go out and I was able to send a link back to the GUI tools Sun/MySQL provides. Later, Brent Ozar commented on my blog post detailing the incident indicating Quest's Toad product was free. Speaking of Brent Ozar, he posted a tweet this morning with a link indicating some press on GM's plans with respect to its various car lines. I drive a Saturn, so while it's not SQL Server related news, it was still news I was interested in.
Twitter isn't without its headaches. It can be a constant source of interruption and it has to be managed like any other tool (such as email). Also, there were a couple of times last week where we saw a failure in the Twitter API which caused everything to lag by at least 30 minutes. But all in all, Twitter has become a valuable tool for me to get more information on the tools and products I use and how other people use those same tools and products. And that's good information to have, indeed.
If you haven't gotten on Twitter and are wondering who to follow, you might check out the following links:
This came up on Twitter today. I have a search set for "SQL Server" and it pulled up someone who was struggling with PHPMyAdmin because of a very large SQL text file. He was unaware that there were free GUI tools for MySQL from Sun. Those tools can be found here:
MySQL GUI Tools Downloads
First saw this because Jason Massie twittered about it. But apparently there are enough bits of the .NET Framework in R2 Core where SQL Server installations will be possible:
Andrew Fryer's Blog: Windows Server 2008 r2
It'll be a command-line install, since core has no GUI, but that's fine and dandy for me. One of my biggest gripes with Windows Server 2008 Core when it came out was that although the stripped down OS was ideal for a SQL Server install, because you didn't have .NET (and couldn't install it), you didn't have the ability to install SQL Server. The same was true of ASP.NET based web sites. I don't know if the ASP.NET web sites status has changed, but I'm glad to see that SQL Server has.
I have a lot of blogs I subscribe to. Well over 500 at last count. And the truth of the matter is there is no possible way to read them all. I tried once. It took about 3-4 hours a day. After about 3 days of that, I gave up. So earlier this week I decided to start pruning my blogs and made a Twitter post about it. Jason Hammer (Twitter) responded back and said why don't I check out:
Sink or Swim: Managing RSS Feeds with Better Groups
I took a look and it made a lot of sense because I use Google Reader, too. Even in those blogs I don't read as often, there are times when I'm looking for something in particular and those certain blogs are the source to contain it. I certainly don't want to get rid of them and rely on a web search to try and find them again. We tend to like to do topical classification, but it doesn't make a whole lot of sense for collecting information. For instance, not all SQL Server blogs are created equal. Folks will likely find the blogs of the SQL Server product teams a whole lot more valuable than mine. So I've modified mine to do a classification scheme based similar to what's in that blog post. I'm basically assigning numbers and categories. If it's something I want to read immediately, it's in a category that's a 1. If it's something I'll read usually, it's in a category that's a 2. And if it's something that I'll read when I have the time, it'll get categorized as a 3. So basically, it looks something like this:
You get the idea. And yes, Comics are #1 because they are short, and they are how I usually start my day. It goes back to the days when I was a kid in Japan and read Stars and Stripes there. The comics were one thing I could always look forward to.
Received my notification via email this morning that I've been recognized as a Microsoft MVP for SQL Server. It's been a goal of mine for a long time and I'm glad it is finally accomplished. Like Andy Warren, mine wasn't so much posting in forums but probably more so related to the authoring and presenting activities that I was involved in last year and will continue in this year. My family and I did a lot of traveling (for us) last year hitting Jacksonville and Orlando, Florida for SQL Saturdays and Miramar, Florida and Florence, SC for user group presentations. We were on our way to a SQL Saturday in Greenville when Kim got sick. Later in the day the bug hit me, too. So I guess for those attending, it was good we missed, but I still wanted to get there and present and meet up with some folks.
If at all possible, I look to get out more this year and my new job position at AgFirst may accomodate that better. Saw some history, had some great family vacations around those trips, and anything I can do with the family, I'll look into doing. Ones in SC, NC, GA, and FL I will definitely look at trying to get to. AL and VA and TN after that, especially the SQL Saturday in Birmingham. Need to swing by Montgomery and see some folks I haven't seen since 2009. Folks from my Air Force days who I've stayed in touch with.
