May 21
I will be teaching a half day course on SQL Server security and auditing for the Midlands Chapter of ISACA.This is a course intended for auditors and will be a technical deep dive on the SQL Server security features and how to audit activity properly. The purpose is to give IT auditors detailed information on what they need to look at within SQL Server so their audits are more efficient and less time consuming for both the auditors and us DBAs.
May 28
I will be speaking at the Augusta Developers Guild on Baked In SQL Server Security. I had been originally scheduled to speak in March, but was sick and had to cancel. I'm looking forward to getting out to the user group and meeting some folks in the Augusta area!
The Midlands PASS Chapter will be meeting next Thursday night, May 7, at Training Concepts. We have the privilege of welcoming the head of our sister group, the Columbia Enterprise Developer's Guild, Chris Eargle. Chris is a C# MVP and INETA Community Champion.
Bio:
Chris Eargle began coding shareware in his teens for the Legend of the Red Dragon by Seth Able Robinson. From there, he developed a career unintentionally following the products of Anders Hejlsberg: from Turbo Pascal to Delphi to C#. He is currently an enterprise architect at South Carolina Farm Bureau Insurance in Columbia, SC.
Chris is a Microsoft Most Valuable Professional – Visual C# and an INETA Community Champion. He is the president of the Columbia Enterprise Developers Guild and enjoys speaking at code camps, conferences, and user group meetings.
TOPIC: RESTful Data
REST is an architectural style that allows for a layered, scalable, and cacheable enterprise information system. With ADO.NET Data Services, a database can be surfaced to a service as a REST-style resource collection that is addressable with natural URIs and can be interacted with using the usual HTTP verbs: GET, POST, PUT, and DELETE. This session will describe RESTful Data, the benefits it conveys, and its uses. Then we will set up a data service using an existing database that developers would then access rather than accessing the database directly.
Self-Discipline
Yes, I've said it: self-discipline.
I'm reading a book called Disciplines of a Godly Man by R. Kent Hughes. Now as might be suggested by the title, it is a book that has at its basis, a focus on improving oneself spiritually. However, Hughes doesn't start out the book talking about spiritual exercises or anything of that sort. He starts out by talking about how he fell in love with the game of tennis as a kid. And how he worked hour upon hour to get better because he loved the game so much. He attributes his self-discipline in pursuing the game and practicing in and playing it constantly as a reason for his improvement. Of course, we all probably say, "You're just stating what is common sense." But the point the author makes is it's one thing to know it; it's a different thing to apply it.
It's not unusual to see posts by folks, or receive emails or tweets about how to get better at this or get better at that. Usually folks are asking for resources to help them get better. And collecting that information is important. You have to know what to use to get stronger in a particular skill set. But simply knowing what you need or possessing what you need doesn't actually help you get better. They are simply steps or tools to doing so. At some point you have to put in the work. Almost all of us are busy and have a plethorea of requests for our time. The thing we have to do is prioritize those requests. Again, a common sense thing. This includes building our skill set. If we want to improve our skill set, we have to invest the time and energy into it. It's not going to develop on its own.
Two Differing Examples:
The most recent conversation I had on this wasn't with respect to technical skills. It was on goalkeeping skills. My oldest son has this crazy idea he wants to be a football/soccer goalkeeper. It's probably a genetic thing, because I had that same crazy idea at about the same time in my childhood. What really scares me is my four year-old daughter clearly has that mindset, too. He asked me how to get better at handling the ball, at diving, and at ball delivery. I explained to him the techniques and showed him the drills he can use to get better. He basically said, "I know all that." To which my response was, "Great! Then you have to practice them, and practice them a lot." He knows I'll be there to help him as much as he wants. But he wasn't too enthusiastic about that answer. As a result, over these last two soccer seasons, his goalkeeping skills have progressed rather slowly. It's based on the fact that he hasn't been self-disciplined in his pursuit of one of his goals.
Now let's take my four year-old daughter. In her league they don't play with goalkeepers. They're just trying to get the kids to kick the ball in the correct goal and teach basic dribbling and shooting skills. But she will plant herself in the goal during practice or before games and dare all comers, adults included, to get a shot by her. She has watched like a hawk as her two older brothers did some of the drills. When I watch football/soccer on television, she's the one most likely to climb onto the couch with me and watch. And not just watch because daddy is watching. Her eyes are darting back and forth with every ball movement. And she tracks what the goalkeepers do. Frankly, it's a bit scary. (I said that before, didn't I?) But I've already watched her take in how professional goalkeepers handle fast-paced balls low to the ground which come directly at them and repeat the same motion to completion. I've not taught her that and I've not taught the boys, either. The first part of handling those types of shots is putting your body into position to stop the ball in case it gets through your hands. With her brothers, we're still working on that fundamental skill. She's moved on past that to where you will see the goalkeeper go prone or almost prone landing on the forearms to ensure his/her full body smothers the ball. The reason she's more advanced, at least in that aspect of the game, is she has shown more self-discipline with respect to learning the craft of goalkeeping.
Applying This to Your Skill Set:
So let's take this and apply it to improving your skill set. First, figure out what you want to learn. Then figure out the steps it will take to learn it. Third, figure out what your motivation is. This third step is extremely important! Know why you are doing what you are doing. You will need this as motivation later. And if you can't come up with very solid reasons, this is the time to ask yourself is this something you really need to learn. It may not be. If not, start back at the first step.
Those three steps tend to be the easy part. Assuming you have all the equipment and resources you need, the hard part will be allocating time to learn it and once that time comes, focusing your attention on that learning. That means putting aside all other distractions. That also means ensuring you don't choose something that's really a lower priority over working on your skill set because it's more "fun." For instance, when that Wii or XBOX 360 or PS3 is staring you in the face as you are plodding through the minutae of all the uses of the FROM clause, you've got to forego game time. If that means taking the power cord and locking it in the trunk of your car before you start, so be it.
This all may require some work on your part to be ready to focus. Part of self-discipline is your preparation. To go back to the football/soccer parallel, think about trying to stop a rocket shot with heavy spin without goalkeeper gloves or trying to make a diving save on a muddy field when you're in penny loafers. From personal experience, it doesn't work so well. You bang up your hands and wrists and you end up slipping and sliding more than you do saving the ball. Now what about your skill set?
You may have to consider your schedule. When will you be awake enough to work on your skill set? When are the distractions likely at a minimum? How well do those two periods coincide? What about your work area? You've dealt with the power cord on your game consoles, but what else can get you? Do you need to put the TV remote in the trunk, too? What about your DS or DSi or PSP? Do you have adequate lighting? Do you have the ability to work without having to get up for every little thing? In other words, do you have food and drink handy, if necessary? Do you need a small white noise generator or soft music (without words) playing on a portable sound system to help you zone-in? Think about your needs and possible wants and get yourself set up.
Finally, get to work. And if you start to find yourself drifting, remind yourself what you're doing and why you are doing it. Those long hours I was taking shots as a goalkeeper to get better, I reminded myself that being a better goalkeeper is something I wanted to become and that those shots were the road to get there. The more I trained, the more "instinctual" my movements became on making saves. And that means the better I got in practice, the better I was in the game. When it comes to your skill set, you've figured out why you're doing it. Use that as motivation to keep you going and see you through.
In my security presentations, another basic I talk about is defense-in-depth. The idea here is to produce multiple layers of protection against a particular attack. For instance, imagine malicious code against your home computer. This is a case where you likely already practice defense in depth, as this illustration shows.
Now if you're using a Mac or if you aren't using Vista or if you're using Firefox with NoScript there may be more or less or slightly different layers. But hopefully the picture conveys the idea. Some attacks will get stopped right away. But other attacks, might require multiple layers. For instance, a worm that tries to infect via RPC but can also be triggered by starting an executable would require all the layers, and in some cases even that may not be enough (for instance, if the user double clicks said executable before AV definitions become available and accepts the prompt by the Vista UAC to run as an administrator). Blaster and its variants come to mind right away, though they proceeded Vista. The idea is that the more different layers we have, the more ways an attack is going to have to work or the more security measures that attack is going to have to beat in order to be effective.
With respect to SQL Server we can put multiple layers in place. Here are some ideas:
Now these layers don't help us if a web server with access to the SQL Server is compromised, but hopefully defense in depth has been practiced there, as well. But what it does do is make it that much harder for a rogue system on the network to do any direct damage to the SQL Server. There is always the possibility of coming through shared components (in this case the domain controller is the weak link), but if the DC is up-to-date on patches and the IDS/IPS is up to snuff, it may be very difficult to exploit that system to get around to the SQL Server. That's why we should plan a strategy that involves defense in depth when considering the security architecture for a system. We want to make it as difficult as possible for an attack to succeed while still staying within the constraints of what business has provided for overall security of a system.
Another reason to consider defense in depth is when any one control isn't particularly strong or is flat-out missing. For instance, if a third party application has a hard-coded and weak password for a SQL Server login (I've seen it), then the valid credentials to logon just isn't there. After all, anyone who owns the application (or has supported the application) has that login/password combination. As a result, the IPSEC policy restricting what IPs can connect, along with the hardware firewall doing the same thing, and the NAC/NAP ensuring that a rogue system can't grab that IP may all be what we call compensating controls to counteract that login weakness.
I'm finally getting around to answering this one originated by Chris Shaw (@SQLShaw), as I was tagged by Jack Corbett (@unclebiguns) earlier. In addition, Chuck Boyce (@chuckboycejr) tagged me on Friday, so I figured I had better offer some response or fall off the "SQL hotness" chart, as defined by Kendall Van Dyke (@SQLDBA).
Who has been a great leader in your career and what made them a great leader?
I'm going to come at this from a completely different angle from most of the DBA crew. I'm going to pick my first tactical officer at The Citadel, (then) Capt. Stack, United States Army. Capt. Stack had the credentials. He was Airborne. Ranger. Combat Infantryman Badge (CIB). But there are a lot of folks walking around with these awards who aren't great leaders. So let me tell you about why Capt. Stack was to me.
He Was Always Around
This was what we first saw. Even as a fresh little knobbie (freshman at The Citadel), Capt. Stack was around, and around a lot. Subsequent "tac." officers were, too, but for whatever reason, they didn't carry the presence of Capt. Stack. And they were good men. But it seemed like every time we turned around, Capt. Stack was around. He made sure we knew he was going to be around. Not because he was trying to scare us or intimidate us, but because he had understood a very important principle about leadership: your troops need to see you. They need to be able to talk with you. They need to know you care. Capt. Stack knew each one of us. He remembered details. We knew we mattered to him. This was a combat veteran who could have looked at the job as babysitting a group of college, granted military college, kids, but he didn't treat us that way. Whether it was walking around the barracks or accompanying us on P.T. runs or being present when we received awards, Capt. Stack was there.
I've tried to do the same thing. When I was in the military, I made a point of stopping by and talking at least 5-10 minutes each day with my charges. When things were going on in their lives, they knew they could talk to me. If I could help, I would. If I couldn't, I would try and find the person who could. I can remember getting really ticked off at my leadership because my NCOIC (Non-Commissioned Officer in Charge) was graduating from a school down in Keelser and no one was going down to see him graduate. They "didn't have the time." So I demanded the day to drive down there, see his graduation, and then drive back. That drive is exactly 233 miles one way (or it was before before Katrina destroyed the roads and bridges). I know, because I drove it every weekend I was in tech school. It was a LONG day. I left out about 6 AM (meaning I got up about 5 AM) and got home about 9 PM that night. But he was one of mine. And I was going to be there for him. I had been taught that by Capt. Stack.
He Was Fair
The Citadel barracks have been rebuilt since my time there, but I lived in Padgett-Thomas barracks, the first one constructed when The Citadel moved to the banks of the Ashley River in 1922. To say it had problems is an understatement. That's what you get when you have a building that old trying to house five companies of insane college men in a military system. I remember standing my first Saturday Morning Inspection (SMI) at The Citadel. Something happened and the Citadel physical plant crew had to go into one of our walls just two days before. My roommate and I spent all Friday night and into Saturday morning trying to clean up the plaster dust, and whatever else had fallen out of the wall. It was still a gaping hole on Saturday morning and it seemed like any time someone slammed a door in the barracks (which happened a lot), a puff of dust came out of the hole. We figured that we were going to get dogged for our first SMI. Demerits, getting yelled out by our sergeants and corporals, doing push ups until our arms gave out, the whole nine yards. But doing SMI that morning was Capt. Stack. He walked into our barracks room, looked at the dust, glanced to his left and saw the hole, and said, "There's not a whole lot you can do with that," motioning over to the hole. With that said, he looked to my company commander, first sergeant, platoon sergeant, and platoon commander and said, "Let's go see the next room."
At The Citadel you often take the consequences even for circumstances beyond your control. That's part of dealing with life and the military system. Combat isn't fair. And The Citadel reinforces that simple fact by putting cadets in what others would describe as unfair situations. We understand (although maybe after we graduate) that it is all part of the training to prepare us for life and for the potential of going into combat. When the enemy outnumbers you and the bullets start flying, you can't beg out of the firefight by saying, "This isn't fair!" But I think Capt. Stack could see my roommate and I were exhausted. He probably could figure out that we had been up all night trying to get our room in shape. And he showed us understanding, as if it say, "You did everything you could have done in a bad situation. I'm not going to punish you any more than you've already suffered." I remember that day well. My roommate and I talked about it after SMI was over. We had respected Capt. Stack before. But after that day, he could have asked us to do anything and we would have, without hesitation.
When I've been assigned personnel, whether at The Citadel as a cadet NCO or officer, or in the military as the officer in charge, or as an infrastructure architect and team lead, I've tried to be fair. If someone has had a new baby, I try to reassign tasks to others to help that person out. If someone isn't pulling their weight, I pull them aside privately and have a talk with them. I try to understand the circumstances and apply that knowledge to the situation. I learned that from Capt. Stack.
He Was Calm
Capt. Stack was one of the calmest men I've ever met. Don't get me wrong. He could get fired up in order to motivate you to finish the last part of a PT run stronger than when you started. And he could get after you when you were in the wrong. But otherwise, he seemed unshakeable. When there was busyness and confusion and chaos, there was Capt. Stack as a bastion of calmness and order. I've since learned that as a leader you could be just as worried or confused as your troops. But you must exude a calmness. You must be able to think clearly and efficiently and then give orders and act accordingly. You must bring yourself under control so those around you will see your example and begin to follow it. This takes a great deal of effort. For some, this takes repeated practice and actual experience. This is an area I still need to work on. I'm nowhere close to how Capt. Stack was.
He Knew His Profession
Capt. Stack had the badges and awards. That says a lot, but you can run into those folks who have a bunch of awards but you can't figure out how they got them. Not so with Capt. Stack. You could tell after a brief conversation he knew his profession. He was a soldier. And he knew the fine details. As a tac. officer he knew how the cadet uniform was to be worn, the exact arrangement of the full and half presses, what was allowed in a room and what wasn't. He could have relied on the cadets that inspected alongside of him, but he didn't need to. To be an effective infantry officer he didn't need to know that information. But to be an effective tac. he did. And so I learned that whatever my job is, whether or not it's what I see my future to be, I need to learn it to the best of my ability.
C# MVP Chris Eargle (@kodefuguru on Twitter), who is also an INETA community champion and president of the Columbia Enterprise Developer's Guild, is starting up a patterns and practices special interest group (SIG) here in Columbia, SC. As with our Midlands PASS Chapter meetings, this group will meet at Training Concepts. The group will meet the 4th Monday of every month. The first meeting will be more of an organizational one and it'll meet this coming Monday, April 27th, at 6 PM. If you're interested in design patterns, please come out to the meeting. I'm hoping to make it out there myself.
The standard best practice answer when it comes to connecting to SQL Server is to use Windows authentication. However, SQL Server-based authentication exists, and there are some times when it's a best practice (or only practice) to use it. Wait a second... what do you mean by having two best practices? Well, as with most things in security and architecture, what is the best practice in a given situation depends on the situation. So let's look at two common reasons why you would use SQL Server authentication over Windows authentication.
Third Party Products
This one is a no-brainer. You've purchased a third party application and it only uses SQL Server-based authentication. If that third party application is important to your organization, you're now stuck between a rock and a hard place, right? After all, the recommendation is to use Windows authentication! Well, not really. The fact of the matter is you can't do it. The application doesn't support it. So you have to make do with what the application supports. Either that, or you've got to eject the application from your environment. That's not happening? Then SQL Server authentication it is. And that reminds me of a baseball analogy.
When I played Little League I was blessed with some really good coaches. They didn't just teach the skills, they taught how the game worked. Simple things which seem obvious to me as an adult I realize wasn't so obvious as a 10, 11, or 12 year-old kid. Like one time my team was down by more than 4 runs. The bases were loaded and I was going up to hit. Before I left the batting circle, my coach motioned to me so I could look at him. He said, "Make contact, get a hit. Don't overswing and end up striking out." I think I remember saying, "But if I get a homerun, we score 4 runs!" To which he said, "Even if you hit a homerun, we're still losing the game. It's more important to get a base hit. So keep your eye on the ball and get a good swing. Don't overswing!" And it clicked what he was saying. So I went up, put the ball in play and got a base hit, drove in a couple of runs, and didn't get an out. Unfortunately, we lost that game (we lost every game that season).
The same idea is true here. If it can't be done, it can't be done. The application doesn't support Windows authentication and you can't make it do so. You can request a future version support it, but until the vendor writes that into the application (if the vendor does so), you use SQL Server authentication. If it represents an exception to your environment, save the installation/configuration documentation. Do a short exception write-up about where in the documentation it says you have to set it up to use SQL Server authentication. If need be, get acknowledgment from the appropriate powers that be and save that evidence with those documents. And then move on.
Applications in Untrusted Domains or in the DMZ
Using Windows authentication assumes you have a connection coming from a trusted computer. The simplest example is when everything is in the same domain. There are cases where a computer may be coming from an untrusted domain or the computer isn't in a domain at all. In these cases, you can't use Windows authentication. The most common example is a computer in the DMZ, the area firewalled off from the Internet, but firewalled off from your internal network. Computers in the DMZ are usually accessible from the Internet.
For instance, a web server that is your company's presence on the Internet is likely in a DMZ. By rule, these computers are supposed to be off any internal domain. The reason is simple: if I compromise this DMZ computer from the Internet and it's on an internal domain, I can launch a direct attack against at least one of your internal domain controllers. And that's not good. By being on the domain it's got to be able to talk to domain controllers. And the way it'll talk to a domain controller is the same pathway I would use to attack that domain controller. Therefore, these computers are usually put in workgroups. Since they are in a workgroup, they can't use Windows authentication to connect to SQL Server. They must use SQL Server authentication.
What About Shared Services and Resource Pooling?
It's not unusual to have a case where multiple systems hit a particular resource and that resource makes a connection to the database server. A good example is a web service which multiple applications use. The web service may authenticate the individual incoming connections, but for the purposes of efficiency you want to make a connection back to the database server using a single account. This means the connection string can be the same every time and this means you can take advantage of resource pooling. A perfect candidate for SQL Server authentication, right? Well, not necessarily. If the web service runs on servers where you can establish that trusted connection to SQL Server, a better option is to use a domain user account, only make sure it doesn't belong to a particular user. A lot of folks call this a service account. A savvy domain administrator can even lock down what servers that account can run against, meaning if Joe Smith somehow got the username/password and tried to run it from his workstation, it would fail. In this case, Windows authentication is the best option. You still get resource pooling. You're not relying on a SQL Server-based login. And you can apply additional security options to the account to help make it harder to be abused.
Log File Sizes:
It's not unusual to see cases where database backups are taken from production and restored to a development or QA environment. Often times the database in production requires point-in-time recovery, meaning we set it up to use full recovery mode. However, when it goes to non-production, there is no need for that level of recovery. There are also cases when we create a new database and we don't check its recovery mode. By default, the database is going to be created with full recovery. In both of those cases we can see the transaction log grow very large. Sometimes, we can even see cases where the transaction log is larger than the size of the database files. We can see the size of the database files by the following simple query:
SELECT NAME, type_desc, (size / 128) AS [Size_in_MB] FROM sys.database_files;
The size column is the number of 8-KB pages allocated to the file. So in order to quickly determine the value in MB, we can multiply by 8 to get the KB and then divide by 1024 to get MB. That's the same as dividing by 128, hence the calculation on that column.
When running this against a particular sample database I created, here were the results.
With the data file size only being 20 MB and the log file size being 1024 MB (or 1 GB), we have an issue. Likely the database is in full recovery mode. Since it's non-production, it likely only needs to be in simple recovery mode. We can see what databases are not in simple recovery mode by executing:
SELECT [name], recovery_model_desc FROM sys.databases WHERE recovery_model_desc <> 'SIMPLE';
Once the databases are known, they can be changed by using the ALTER DATABASE statement with the RECOVERY option. For instance, for the sample database I cited above, I would issue the following statement:
ALTER DATABASE SampleDB SET RECOVERY SIMPLE;
Once that's done, the next step would be to shrink the files to an appropriate size. The command DBCC SHRINKFILE() can be used. For instance, to attempt to shrink the log file down to 5 MB, I would issue:
DBCC SHRINKFILE('SampleDB_log', 5);
The entire space may not be reclaimed. For instance, after running this command, the log file shrunk to 255 MB. That's not the 5MB I wanted, but it represents recovering about 3/4 of the space used,
Database Ownership:
When a database is created or restored, unless otherwise specified, the owning login for the database is whoever performed the operation. At my organization we prefer to ensure databases are not owned by a login that corresponds to an actual person. The reason being is that if that person is no longer a DBA but still has the ability to log on to the database, then the user will be able to implicitly do anything he or she wants within the database. That's because SQL Server automatically maps the login who owns the database to the dbo user within the database. That user bypasses all security checks within the database. To prevent against this, we periodically check database ownership and re-assign accordingly. To determine who owns each database, a query against sys.databases is all that is necessary:
SELECT NAME, SUSER_SNAME(owner_sid) [Owner] FROM sys.databases;
Once you determine which databases are owned by actual people, changing the ownership is simple. In SQL Server 2005/2008, you can use the ALTER AUTHORIZATION command. For instance, to change the ownership of SampleDB to be owned by sa, I'd execute the following:
ALTER AUTHORIZATION ON DATABASE::SampleDB TO sa;
And if I run the query again, I'll see that ownership has changed.
When it comes to logins to SQL Server, there are basically 3 types:
The latter two we have limited control. We can grant them the ability to connect to SQL Server and we can then make them users within the databases. But with respect to passwords and password expiration/strength, there's nothing we can do within SQL Server. Those accounts (or the accounts which are members of the group, in the case of a Windows group login) are controlled either by the operating system (an account local to the computer) or by Active Directory (domain account). So other than auditing for their existence on a SQL Server and their fixed server role memberships, there's not a whole lot more we do with respect to them at the server level. SQL Server-based logins are a different story.
With SQL Server-based logins, we can (as of SQL Server 2005) control whether or not they adhere to the password policies set at the operating system level (and which the computer may receive from the domain via a group policy). We can choose whether or not password complexity and lockout is enforced as well as whether or not password expiration is checked. In SQL Server 2005 and above, the key to audit this information is to use the catalog view sys.sql_logins. The reason for using this over sys.server_principals is that it only returns information on the SQL Server-based logins and it returns all the information we would want to check related to a SQL Server-based login. It also inherits all the columns from sys.server_principals, meaning we don't have to reference that catalog view as well.
Here are several simple queries that we can use to audit our SQL Server environment:
SQL Server Logins which are currently disabled:
SELECT name FROM [sys].[sql_logins] WHERE [is_disabled] = 1;
SQL Server Logins which do not adhere to the password policy:
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0;
SQL Server Logins which do not adhere to password expiration:
A note about this one. Password expiration is only checked if the password policy is enforced. If the password policy is not enforced, then SQL Server won't check for password expiration, meaning that SQL Server login effectively has a password that never expires. Therefore, we need to check both when there is no password policy enforcement as well as when there is password policy enforcement, but no password expiration enforcement.
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0 OR ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);
When I discuss SQL Server security, one of the basic concepts I concentrate on is the difference between logins and users with respect to SQL Server. As of SQL Server 2005, Microsoft introduced new terminology, that of server and database principals. So the mappings look like:
However, when you look at the T-SQL given to create these objects within SQL Server, and the T-SQL was also introduced in SQL Server 2005, you find that they are CREATE LOGIN and CREATE USER respectively. So logins and users will remain a part of the nomenclature for the foreseeable future. Because of this, and because we are often very ambiguous when we use the term USER, I looked for a way to better communicate what SQL Server means when it uses LOGIN and when it uses USER. I finally came up with the self-storage facility model, which seems to work well. For instance:
In order to get into most self-storage facilities, you usually have to enter a code. For instance, there's a keypad at the gate of one such self-storage facility.
You can see the individual self-storage units inside. If the facility itself is considered the SQL Server and the individual self-storage units are the databases, then the first thing you must do is enter the facility. You do so by typing in the passcode. This is the equivalent to having a login. A login allows you to enter the SQL Server. However, that doesn't mean you have any access to any of the individual self-storage units, aka databases. That's because when we do a close-up on the storage units, we see:
Along the right side there's the place for a lock (or multiple locks) to go. We'll assume just one. In order to get into the unit, you have to be able to open the lock. In the real world that means either having the key or having the combination. Once you open the lock, you can enter the facility. This corresponds to having a user account in the database. For each database where you have the "keys" (you are a user), you can enter.
So what about master, msdb, and tempdb? Well, in those cases everyone comes in as guest (with the exception of members of the sysadmin role or anyone who has been granted CONTROL SERVER rights, in which case you come in as dbo). That's easy to fit into this model, too. Those are storage units where there is no lock. Everyone who can enter the server can get into them. And that's by design.
Now once you're in the database, there are permissions at the schema level, on the individual objects, etc. Once you get into a storage unit, there's nothing stopping someone from putting a safe, a locked box, or some other secured storage container in place. If you think of tables, stored procedures, schemas, and the like as those individual components that can fit inside the storage unit, that completes the model.
In the course of giving my security presentations over the past year, I've learned that quite a few folks have never seen the C-I-A triad before. The C-I-A triad stands for:
It's often illustrated using a triangle like so:
The C-I-A triad forms the basis of how to start thinking about information/data security. The three words mean the following when applied to security:
In security we often focus on the first two items and forget about the last, availability. To do this, however, is a mistake. Typically I find business users and development staff focused on the last, availability, and not so worried about the first two. again, this is a mistake. Good information security balances all three to ensure a reasonably secure system. What do I mean by reasonably secure? It depends on the data. If we're talking about who brought what to the bake sale, there's probably not a lot of security required there. But then again, if we're talking about intellectual property, such as something for which a patent, copyright, or registered trademark hasn't been filed on yet, we may want very good measures in place with respect to confidentiality and integrity.
With all that said, when looking at the initial architecture for a system or application, a good starting point from a security perspective is the C-I-A triad. It's high level enough to start asking the right questions to bake security into the system. And if security is baked in from the beginning, it's a lot cheaper than trying to retrofit a system later to fix security holes. Therefore, when talking with folks about initial development, I make sure they understand the C-I-A triad.
I was talking with a gentleman last night after the Greater Charleston .NET User Group about career development. He's not currently a DBA, but that's what he wants to go in his career. Since DBA work isn't his day-to-day job, he asked for ideas about how to keep his skills current and advance his learning. Both I and Lou Vega (a Device Application Development MVP), aka BrainThump (@BrainThump on Twitter), suggested working on and delivering presentations as one of the avenues to do so. But getting started with presentations can be a bit frightening. Here's how to make it easier.
Pick Something You Are Passionate About
It's significantly easier to present on something you are passionate about than it is to present on something you are not. If you really love some aspect of the technology you're working with, that's a great candidate. Or if you are learning about something and want to go deeper, this is something else that might make for a good presentation. In the first case, think about what that technology does and why you love it. Build your presentation around those things. In the latter case, take notes as you hit difficulties and as you have those "Aha!" moments. Personal experience goes a long way in presentations. It shows that you care about what you're talking about, that you've actually used it, and your experiences may help others avoid issues you encountered and improve the overall learning curve for those who hear your talk.
Start Small
I've seen a growing trend in usergroups to allow for a short presentation before the main one. If you're not used to giving presentations, that's a good place to start. 10-15 minutes goes by really fast and you don't have to build what may seem like a monolithic presentation to get some practice in. If you're in a user group that doesn't do this, suggest it the leadership of that group.
Build an Outline
Before you start building slides and writing code, build an outline. The outline will help you organize your thoughts, will show you if there is a logical flow from one subject to another, and will likely give you a roadmap on how to build your demos, if you have any. Even if it's only a small presentation, you still want an outline.
Research Your Topic
Even if you know your stuff on the topic, still go and do some research. Is there anything new or noteworthy being discussed? Is a major player using the technology? If you can work these types of facts into your presentation, you'll likely be providing information others didn't know, and that's valuable. Also, it will give you a new appreciation for the topic, and that will help you give the presentation.
Get the Right Equipment
If you're going to become an active presenter, at the very least you're going to need a laptop to present with. If you're just looking to do one or two speaking engagements, and it's a slide talk (no code), then it may be okay to send the slidedeck on to the organizer(s) that'll run off someone else's gear. But having your own laptop, even in these situations, is really the way you want to go. It'll be important for the next tip.
If you are going to do a lot of presentations, make sure your laptop can handle the applications you're going to use. Quite a few regular presenters build virtual machines which they use for their presentations. That's typically a good idea, but it's not absolutely required (for instance, I don't, but I do have one available if all else fails). If you're going that route, make sure your laptop can handle doing the presentation and giving the hardware needed to the VM for a smooth running presentation.
Another thing to consider is either a wireless mouse built for presenting or a presenting device. Being tied to a mouse wired to your laptop limits your ability to move around and be a bit more natural in your delivery. I have a nice presenting device which allows me to cycle quickly between the slides and even have mouse functionality if I need it. But a basic wireless mouse will do if you that's all you have.
Practice By Yourself
You've got your topic, your outline, you've done your research, and you've built your slides and code samples. The next thing to do is to practice. The first step is to practice on your own. If you've got a video camera where you can record yourself, even better. The point here is to work out the kinks in the presentation that often get forgotten about. Things like switching between PowerPoint and your application, making sure the right project/files are loaded, that you've done the steps to prepare your environment, and that you know what to do to clean up afterwards so you can be "reset" for the next presentation. Run through your presentation a few times. If you've been able to record yourself, watch over your presentation carefully and take notes. Look for space filler words ("Ah," "Um", etc.). Look for areas where the presentation didn't go smoothly because of a technical issue (like switching between the slides and the application). And look for repeated bad habits. As you practice, seek to eliminate those issues. Check your timing. If you're going long, see if it's because of a technical issue. If it's not, consider how to pare down your talk. If you're running short, do you have enough material? Or is it because you're getting nervous and speaking faster?
Consider Groups That Are All About Speaking
If you find yourself struggling, consider joining a group or organization like Toastmasters. Those groups are all about developing the ability to speak better in front of people. These groups tend to be supportive in nature and you should feel that both as you speak and as you hear others speak. The types of speeches you'll give won't likely be technical in nature, but they will develop you as a speaker. For instance, the first speech you give in Toastmasters is an ice breaker, where you talk briefly about yourself. It's there to get you started. The next speech works making sure you have structure to your talk: a definite beginning, middle, and ending. You get the idea. Even if you feel comfortable speaking, still consider these types of groups. There's always more to learn with regards to the craft of public speaking.
Get Feedback from People You Trust
Once you've practiced a few times on your own, gather a few folks you trust to give you honest feedback. Run through the presentation once, let them critique while you take notes. Try to make improvements and gather them again to see if you did better. Because you're presenting to an audience, you may have a tendency either to speed up or to slow way down, so watch your time. Ask them to note if you are doing either of those things.
Find the Right Audience
The perfect place to start is with your local user group. If there's not one near you, look to see if there's one in a nearby town. Attend at least a few times before presenting if you're not doing regular presentations. This will mean you get to know the folks there and they will get to know you. It'll also give you an idea of the types of questions they might ask or what you may need to do to tweak your presentation to hit a chord with your audience. As you get better at presentations, look for other opportunities in other groups and possibly at conferences.
Take a Deep Breath and...
Go for it! You may make mistakes. You may get nervous. You may have some difficulties with the slides and the demos. That's all part of the learning process. Don't let the potential for an issue dissuade you from giving your presentation. And certainly don't let issues during a presentation stop you from giving more. You'll get better. You'll work out the issues. And you'll feel more comfortable each and every time you get up to speak. Don't get me wrong. You'll likely always feel nervous. I do. And everyone else I know who presents a lot do as well. Nervousness is a natural reaction, but you can overcome it!
Next week I'll be giving presentations in Charleston and in Florence here in South Carolina.
Tuesday, April 14, 2009 - Pee Dee Area .NET User Group - Getting the Most Out of Your SQL Server Queries
You can register and find directions at the PDANUG web site (http://www.pdanug.net/). I spoke there at the end of last year and Chris Reeder, Chris Craft, Page Brooks, and crew have graciously invited me back. I'm looking forward to visiting with everyone there again.
In this presentation we'll look at standard tips and techniques to write efficient queries for SQL Server. The use of stored procedures, the basics of indexing and the impact that has on your queries, how to avoid cursors and RBAR code (a Jeff Moden-ism), as well as typical no-nos to avoid will be covered. We'll touch briefly on how to measure query performance through the use of execution plans and SQL Server traces using tools like Profiler and Microsoft Excel to provide for a quantitative assessment of which queries to look at tuning.
Thursday, April 16, 2009 - Greater Charleston .NET User Group - Baked In SQL Server Security
Again, you can register and find directions at the GCNUG web site (http://www.gcnug.org/). I'm looking to returning to my old stomping grounds, having spent four years in Charleston as a Citadel cadet. I'm also looking forward to hopefully meeting with Lou Vega.
We'll look at best practices for data contained within SQL Server from primarily a developer's perspective. We'll start with the basics of how to relate security to business personnel and take into account security measures from the beginning of the development effort. After that we'll dive into the technical aspects of using the security features of SQL Server to help us protect the data contained within. SQL Server 2000 through 2008 will be covered.
Apologies to the Augusta Developers Guild
I was scheduled to speak there (http://www.augustadevelopers.org/) on March 26, but came down with a stomach bug that took me out for several days. I'm working with Pete Mourfield to try and make it out there soon.
Next Meeting - April 2, 2009
SPEAKER: Paul S. Waters
During Paul’s 16 years of working in IT, he has held a variety of positions including Director of IT, application developer, network administrator and help desk manager. He is a business intelligence architect for AMECO in Greenville, SC working with SharePoint and the Microsoft Business Intelligence technology stack. He enjoys problem-solving and especially welcomes the ever-changing variety of challenges that comes with working in the field of information technology.
His leadership in the Upstate South Carolina IT community includes Founder and President of the SQL Server Innovators Guild (www.ssig.org), Founding Board Member of the GSA Technology Council (www.gsatc.org), member of Greenville Technical College’s Program Advisory Council, member of ECPI College of Technology’s Advisory Board, former Co-Chairman of the South Carolina Code Camp 2.0 (www.sccodecamp.com) and organizer of SQL Saturday #9. Additionally, he is a frequent presenter at user groups and business lunch-and-learns.
A Dimensional Data Model is at the core of most Business Intelligence and Data Analysis systems. Unlike normalized, transactional database designs built for efficient writes and ease of application development, dimensional data models are build for efficient reads and ease of reporting and analytics. Designing dimensions around subject areas such as customers, products, locations, and employees as they change over time can be the most challenging part of a well-designed Dimensional Model.
After an overview of Dimensional Modeling, the presentation will cover the fundamentals of type 1, 2 and 3 Slowly Changing Dimensions (SCD), how and when to use them, and how to combine them.
