Upcoming Events

I am excited to announce that I will be speaking this Thursday at the Louisville SQL Server User Group meeting on the New Features of SQL Server 2012.   The User Group meets at 5:30pm at the Duthie Center for Engineering at the University of Louisville. 

I also have 2 upcoming SSWUG events

The 4-Hour DBA: Automate, Audit and Relax on Feb 15.  

I will also be a part of a Security Expo on Feb 17th.  I don't have a link yet, but when I do I will let you know

 

Basic Troubleshooting

I have been a DBA for a long time and I guess that there are just some things that have become second nature for me and I forget that when people are new to SQL Server they often are overwhelmed, when there is an outage and waste time troubleshooting the wrong issue.  


Recently, I was called about a SQL Server outage.  Here were the facts:

• 3 servers were impacted
• The servers could not be pinged
• The servers would not allow users to login with domain credentials directly via the console.
• All the servers were on the same subnet in the same rack behind a firewall
• Other servers in the same rack were up and responding to requests
• No changes have been made for months to any of these servers

An hour of time was was spent trying to troubleshoot this as a SQL Server issue, when it was clearly not.   Do you know what the issue was?  The answer is in the comments.

Build Your Own SQL Server Cloud

On Wednesday, October 26 at 11am PST, SSWUG will running my webcast Build Your Own SQL Server Cloud presentation.  


This presentation focuses on building a private cloud using Windows 2008 R2 and SQL Server 2008, but the concepts that I discuss can be applied to any virtualization platform. This presentation covers what is a private cloud (as well as what is a cloud) and how to achieve the benefits of cloud technologies using the builtin-features of SQL Server.   I will also discuss some of the challenges I have encountered building my own cloud.  


This week I have started designing the next generation SQL Server private cloud for SQL Server 2012 and this is a topic I will be blogging about more over the upcoming year.  


I really excited about this webcast and look forward to talking to attendees about their ideas about the private cloud.

SQL Saturday Atanta

I had a great time in Atlanta at SQL Saturday and think that the audience enjoyed it as well.   I have received some emails that people are having trouble downloading my presentation.  I tried to down the PowerPoint from the SQL Saturday site as well and had some difficulty.  Here is a link to my presentation for those that have trouble.

Audit for New Login without using SQL Audit

Because SQL Audit is only an Enterprise Edition feature, I have been thinking about the best to audit for the creation of a login without it.  I do not need to know real time when a login is created, a daily report will suffice.  This is not as simple as you may think.   I am not going to give you the whole script, but you should be able to work it out for yourself from here.

First lets create a login

USE [master]

GO

CREATE LOGIN [Test_Login] WITH PASSWORD=N'TestLoginPASSWORD', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

A query of syslogins will tell you that a new user has been created since the last time you checked

SELECT createdate, name, dbname, isntuser

FROM sys.syslogins

WHERE createdate > @LastDate

ORDER BY createdate DESC

However,  this information does not tell me what I really want to know.   Who created this login?  Since I do not want to write an elaborate thing to capture this information, I want to query an exisiting source: The defeault trace.  

This comes with a problem of its own.  The default trace rolls over and only 5 are kept.  The limit of 5 is not so much of a problem in my world, but not knowing when the rollover happens is.   You can query the ::fn_trace_getinfo to get metadata about these traces, but we need to know which trace contains the information we need. 

SELECT * from ::fn_trace_getinfo(0) Where traceid = 1

This returns the following

traceid	property	value
1	1	2
1	2	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc
1	3	20
1	4	NULL
1	5	1

The property values are

1= Trace options.
2 = File name
3 = Max size
4 = Stop time
5 = Current trace status.  0 = stopped. 1 = running.

The stop time would help us, but I have yet to run this query on a server or a log and not get a null result.  In order to find out if the trace contains the data we are looking for we must query the file.  You can use the meta data collected in the last query to get the file path.

SELECT MAX(StartTime) as EndTIme , MIN(startTime) as StartTime

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc', default)

This will let us know if our create date from syslogins in within this trace file.  If it is we can then query the trace to get infromation on the person who created the login.  Event class 104 is the event for the creation of a new login. 

SELECT StartTime, LoginName, Hostname, TargetLoginName

FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\log_5.trc', default)

WHERE EventClass = 104

Another approach is to query the default trace only, you will still have to do some date management to make sure you don’t miss any new logins between trace files.

One of the Boys, Maybe Not

At the WIT Luncheon at SQL Saturday in Indianapolis, I was asked about challenges I have to overcome as a woman in technology.   I have been fortunate that I have not had external obstacles, like a sexist boss or woman hating co-worker.  My obstacles have been of my own making, a belief that I have to be someone I am not in order to fit in with my male co-workers.   I am not one of the guys.  

I started my career during the dot com boom in Austin.   It was a time and place that encouraged creativity and companies were desperate for anyone who wanted to contribute.  This was a great time for women in technology.  All of my friends were developers, admins, and developers.  When the dot com boom went bust, many of us looked for work in other places.  Side note: Ten years later, I am the only one of my friends that is still in IT.

It was when I moved to Louisville that I realized what it really was like to be a woman in technology.  It is lonely.  I worked in a large IT organization, 3000 or more people and there were a few other women but in different departments or buildings, but I was the only women DBA.  Because I was new in town, it was important to me to be part of the team and fit in with group.  I wanted to go to lunch with the rest of the team and enjoy the water cooler banter.    These men shared common interests that I did not even have the slightest interest, but I wanted to be included.  I have no desire to go fishing, know nothing about youth athletics, nor is Hooters my favorite restaurant.  

I tried so hard to be part of that boys club, but I was miserable.  My male coworkers respected my work and I did enjoy friendships on an individual basis with many of these co-workers.  One day I realized that my coworkers were sneaking out to have lunch without me.  Of course, my feelings were hurt and no one likes to think that people are sneaking around rather than having to hang out with you.   However, after that incident I stop trying to be a part of the group and eventually realized that wanting to be a part of group that I have nothing in common is ridiculous.  If anything not being part of the group improved my work and career and I found my individual friendships with coworkers strengthened.   

I have since moved on from this company and while I have more in common with my coworker now, I will never be one of the boys.  I am girl who has girly interests and hobbies.  I will still occasionally still feel a twinge when my coworkers engage in some masculine bonding over sports or cars or some other thing that I don’t care about, but it is more important to me now to be myself.   

Querying SQL Audit Files

I love SQL Audit, mainly because I can use it avoid login auditing going to the SQL Server error log, but that is besides the point.  However, an audit is only useful if you can use the information within the audit easily.  This is one negative on SQL Audit, I do not find it easy to query, but here is how you can do it. 


I created an audit to log failed logins

USE [master]
GO


CREATE SERVER AUDIT [Login_Audit]
TO FILE 
( FILEPATH = N'C:\Audit\'
,MAXSIZE = 1024 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'd60fca2d-14a6-4cc5-bb6c-78a9249acae6'
)
GO


CREATE SERVER AUDIT SPECIFICATION [Login_Audit]
FOR SERVER AUDIT [Login_Audit]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO


What I want to know is how many failed logins happen in a 24 hour period and what logins are failing.

SELECT server_principal_name, COUNT(*) as FailedLoginCount
FROM sys.fn_get_audit_file ('C:\Audit\*',default,default)
WHERE event_time > GETDATE()-1  AND action_id = 'LGIF'
GROUP BY server_principal_name
ORDER BY FailedLoginCount Desc


Not the most elegant method for getting the information I want, but it is a starting place.