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.

SQL Saturday Columbus

Wohoo!  I will be giving 3 sessions at SQL Saturday in Columbus and participating on the WIT panel.  I am sure I will be tired by the end of the day.  My sessions are:

• Build Your Own SQL Server Cloud
• Oh, no Disaster Strikes
• The 4-Hour DBA

Please come to see me

Somedays it is easier to blame the users

I believe that part of a DBA's job is to educate their users on proper SQL Server practices.  Today, I had a situation occur where a developer rolled out a database that nearly crashed its SQL Server, because every query had a where clause that contained a wild card at the beginning of the search string. 


WHERE product like '%widget%'


This meant that every call that used this pattern had to perform a table scan and caused all sorts of locking and crappy performance.  I was not happy.  


I started to write a "You are an idiot" email to the developer screaming about how stupid they were and why did they not use a full-text index.  I started to look through my SQL Server training materials for the section where I covered full-text indexes and realized that I had never written about this.   Now, should an experienced developer know that using a wildcard at the beginning of a search string is a bad idea? I think so, but I also had not done my job fully and trained the developer.  This is what I believe is called a teachable moment.  


Understanding database performance is not easy or everyone's databases would be super fast.  Before blaming someone else make sure that you have communicated what you expect of them first.  Then the next time they do something stupid, you are justified in flying off the handle.

Upcoming Speaking Events

I have submitted sessions for the upcoming SQL Saturday events in Columbus, OH and Indianapolis. I am looking forward to attending both events and hopefully will have the opportunity get back into speaking about my favorite topic SQL Server again.

Public Not Granted Server Permission

I have been setting up policies on my 2008 servers. I thought a good place to start would be with the suggested policies Microsoft provides with their samples. You can download the sample databases from codeplex.

I can came across a sample policy named “Public Not Granted Server Permission”, now this sounds like something a person would want to implement. I imported the policy and ran it on my test system and discovered my system failed, but I was not really sure why. The policy explains that the server role public has been granted server level permissions. OK, but what does that mean? What server level permissions? This policy does not provide an option to force compliance, so the dba must correct the issue manually, but how and if you correct what permissions will be revoked?

I started by trying to investigate the @PublicServerRoleIsGrantedPermissions facet, but that provided no greater information. Then I found this great blog post by Randy Dyess who delves into the guts of the public role. He provides a script to identify the permissions that the public role has been granted. Using this script I determined that a fresh install of SQL Server by default grants the public role the following permissions:

Name	Permission	Class	Securable	State
public	CONNECT	ENDPOINT	NAMED_PIPES	GRANT
public	CONNECT	ENDPOINT	SHARED_MEMORY	GRANT
public	CONNECT	ENDPOINT	TCP	GRANT
public	CONNECT	ENDPOINT	VIA	GRANT
public	VIEW ANY DATABASE	SERVER	Server	GRANT

Of these permissions only VIEW ANY DATABASE is the only server lever permission. I am familiar with this permission. If you DENY VIEW ANY DATABASE to PUBLIC a user will only be able to see the master, msdb and databases in which they own. This would prevent a user from seeing the other databases on the server listed in management studio. The problem with this is that I don’t make any user dbo. If a developer wanted to connect via Studio Manager they would not see database which they have been granted permission within. They can still connect to the database, but can’t see it in Management Studio which kind of defeats the point.

For my testing I went ahead and issued the following:

DENY VIEW ANY DATABASE to PUBLIC.

I re-evaluated the policy and it still failed. So on the advice of Brian Kelley, I issued the denied all connect permissions to the Endpoints.

DENY CONNECT ON ENDPOINT::[TSQL Named Pipes] to public

DENY CONNECT ON ENDPOINT::[TSQL Local Machine] to public

DENY CONNECT ON ENDPOINT::[TSQL Default TCP] to public

DENY CONNECT ON ENDPOINT::[TSQL Default VIA] to public

This worked and the policy passed. However, this is not a policy that I will be implementing.

Speaking at Louisville PASS Chapter

On Thursday June 11th from noon to 1pm I will be speaking at the Louisville PASS chapter meeting. The meeting is being held at Kindred Healthcare, 680 S. Fourth Street. The topic is "Defensive SQL Server Security." I believe that Idera will be providing Pizza.