Blog

Null Values in SQL Server

Null Values in SQL Server

Walk & Talk #001 – Unknown or Null Values

Ride with us in this video as we head to Richmond, Virginia to present to their user group.

This is the first official Walk & Talk, but you’ll notice we’re not exactly walking along.

Through this video we talk about and show you how NULL values in SQL Server work.

There are even demos on how NULL values affect your result sets, how to identify a NULL value, and how to deal with these unknown values.

You can download the scripts here: Download it!

 

Whitelist a Set of IP Addresses in SQL Server

Whitelist a Set of IP Addresses in SQL Server

Blacklist vs Whitelist

bouncer_EnviusDesignz

Are you on the Whitelist?

I think just about everyone is familiar to some degree with the concepts of a blacklist and whitelist.

If not, the easiest way I can summarize them is as follows:

A blacklist is a list of what can not access something you are trying to protect.

If you travel, this can be thought of as the TSA no fly list. If you are on the list, you are not getting anywhere fast.

A whitelist, on the other hand,  is just the opposite. It is a list of what is permitted to access something. If whatever is attempting access is not on the list, it isn’t getting in.

This is like your typical bouncer at that hip club with the clipboard that won’t let you in because you are not on “the list”.

There is a lot of debate of which is a better practice, to blacklist or whitelist, but I feel it is a matter of preference and how much maintenance you want to do.

 

What does this have to do with SQL Server?

Well, the other night I once again heard the question of: “Does SQL Server allow you to Blacklist/Whitelist certain IP addresses?”.

It is a good, and valid, question and I had to think a bit before answering that I was not aware of an easy way to do that.

Others in the room concurred that you pretty much would have to configure your DNS service to create the blacklist and/or whitelist.

Now, to me it makes sense that there are times you want to lock down access to your server to specific IPs, and other times you just want to stop other IPs from even getting near your server.

When I got home I started doing some research and was able to find a couple of articles written online about using a feature that came with SQL Server starting in SQL Server 2005: Logon triggers

One script that caught my eye was created in 2009 by

 

Welcome to the Whitelist!

Step this way please

Step this way please

Because of the question of creating a whitelist was what was asked, I am going to go through that process. In a different post I will detail the process for creating a blacklist of IP addresses.

Before we are able to put a logon trigger in place for a whitelist though, we are going to need to create a place to store our list. This will be our bouncer’s clipboard.

There isn’t much we really need, just a simple table to store the list of IPs allows to access our system (and 1 non-IP address, but we’ll get to that in a moment).

I am going to use the master database for this and just create a table with a varchar(15) field to hold the IP addresses.

Once we have the table created I will want to populate it with a few IP addresses I know will need access to the SQL Server. You will need to get the proper list together, but I include some commonly found ranges for test environments in this script.

Take note of the last value I am putting in the table ‘<local machine>’. If you are doing this from the machine on which you are going to place the logon trigger, it will not use the network to loop around to itself, but rather uses internal networking. If you leave off ‘<local machine>’ you may get locked out. Trust me on this. I found out the hard way on one of my test systems.

*Note: While you create this trigger and test it I strongly advise you do not disconnect your session. We are creating a very restrictive access logon trigger that will prevent anyone, even the DBA / admin from accessing the server from the wrong IP address. If you leave your session connected the logon trigger is not fired and you can undo the trigger. Once you know it is all working, you can safely close the connection. If you mess up and get locked out, you can get back in, bypassing the trigger, as a member of the sysadmin role and using the dedicated admin access or restart the server in single user mode.

Now that I have some IPs added to my list, I need to make sure that anyone is able to read from the list. To do this I grant select access to the Public role. This may seem counter-intuitive, but if the connection is not from one of these IP addresses, it won’t be able to really do anything with the data found since the connection will be refused.

Now for some extra function in the trigger, I want to record the IP address from failed attempts along with the number of times that IP address tries to access my system before I fully enable the blocking capability. Be aware that this number may be inflated as SSMS does connect multiple times to the server. This step is purely optional, but worth having in my opinion, to help you test and be a little more responsive with any addresses you may have missed or forgotten, but also to identify any IP addresses making high access attempts. You could then have the network admin update  the DNS server blacklist with the information you provide. It is a very simple table, just like the whitelist table, but includes a counter to be incremented each time a denied IP address attempts to connect. Permissions are granted to allow the trigger to use the table correctly to read, insert and update the values.

Once all our tables are created and ready to go, we can implement the logon trigger.

 

But Triggers are bad!

I know, I know. “But it’s a trigger! Triggers are bad!”.  Just like most things in life, there is a potential for triggers to be bad. It is like having hidden code in your system that you have to remember about. But like all those things that are “bad”, there are times that it may be the correct solution for an issue you have. There may be a blacklist and whitelist, but in general things are kind of grey. In this case, the trigger it lets us create a way to validate IP addresses without the immediate need to involve our network admins and gives us some extra insight to our own systems activity.

Anyhow, here is the basic code for the trigger.

Notice the ROLLBACK is commented out for the denied connections. This is so the IP_Attempts table can be populated for test / development purposes. When you add the ROLLBACK statement back in, the trigger will function as intended, but will not be able to record the denied IP addresses since the connection and trigger transaction are all rolled back. Once you have enough of a list of the attempts that are not on your whitelist in the IP_Attempts table you can make adjustments to your whitelist and add the ROLLBACK functionality back in by un-commenting the line.

You can change it up however you feel works better for you, and please share in the comments if you find a better way to do this.

Let’s step through this trigger in a little more detail:

First we state we are going to create a logon trigger called IPAddress_Check.

We declare a variable to hold the client IP address that is connecting, and we collect that IP address from the EVENT_INSTANCE/ClientHost data using the EVENT_DATA().value function.

Next we perform a simple comparison to see if the IP address is in our whitelist table. If it is we go ahead and “print ‘IP Address Allowed’” since we have to perform some action, and this is not visible to the end user. The logon then continues like normal.

If we don’t find an exact match for the incoming IP address, we perform a simple range check. We remove the last part of the IP address and replace it with a * and compare that to the values in our table (I’ve kept things simple in the table, but you could easily adapt it for more detailed ranges). Again, if there is a match, we print the “IP Address Allowed” message and continue with a regular logon.

Things start to get a little interesting if there is not match. I have opted to use a merge statement (SQL 2008 and later only, sorry, but you can change the code freely) to add or increment the IP_Attempts table accordingly, then rollback the connection attempt. The rollback action is what prevents any further action being taken by the connection from the wrong IP address.

That’s it. Nice, clean and simple.

I do advise working with your network admin to ensure your DNS is configured properly with its whitelist and blacklist options, but now you have an additional tool in your DBA belt to help keep things locked down a little tighter.

Try it out and let us know how it all works out in the comments below!

Protected: stock photos gallery

This content is password protected. To view it please enter your password below:

How Critical is a Demo?

How Critical is a Demo?

Do you always need a demo?

Do you always need a demo?

Is a demo always needed?

One of my more popular presentations I have been doing as of late is my “The Spy Who Loathed Me – An Intro to SQL Server Security” one. What is intriguing as that is is completely demo free.

This session covers a lot of material in a short time. The purpose of the session is not to teach you how actually take security measures, but to introduce you to the offerings SQL Server has in respects to security, and to inspire you, the learner, to want to learn more about the topics that caught your attention.

Each time I give this session, I state clearly that there is not a single demo being done in it. Then I expect about 1/2 the room to get up and walk out. Surprisingly they don’t. I then go on to state there are 2 reasons I don’t do any demo in this particular session.

 

1) There really isn’t enough time to include a demo with the quantity of content.

2) Any demo directly influences your choices and actions.

 

What does each of these really mean?

Let’s break it down a little more:

1) There really isn’t enough time to include a demo with the quantity of content.

I am going to take you through 90+% of the security features SQL has so you can get an idea of what is possible. I could easily make an entire week long course with demos on each topic covered; and that may happen (stay tuned…). I feel in this particular session it is important that you are introduced to what is possible. You get an overview of how different security features work, which hopefully peaks your interest, so you can then go and do further due diligence and determine what would actually work best for your environment.

2) Any demo directly influences your choices and actions.

If you see a demo the odds are that you are going to go and try it yourself. Even higher are the odds if it was a real cool demo that could help you. Demos directly influence what you know and do in SQL Server, or anything else really. A lot of people learn via observational learning. We see something done successfully, like a demo, and then try to emulate that something for our own benefit. There are times that is completely appropriate. For example, learning proper syntax, performance tuning suggestions, etc. There are also times when doing exactly as someone else did is not entirely appropriate, such as when database security is involved. Are there times it would be helpful to have a demo? Of course there are! Is it in the session I designed to relay as much information and inspire people to learn more about the different security features that SQL Server offers. Not so much. When we are talking security, especially at an introductory level, I feel that you should get informed of what is available with as little “influence” as possible. You can then dig in deeper with more focused sessions, training or documentation, that contains demos, to determine if and how the security feature is feasible for your environment.

There is a third item I don’t mention above, but is worth considering. Here’s my though on anyone who is making the effort to pull together a 60+ minute session without a lot of demos to help you learn. If they are good at what they do, you can rest assured that they have gone over the top to make the presentation strong enough to stand on its own and it isn’t something to be missed.

So, what is there to take away from all this rambling. It is quite simple. Just because a session doesn’t have a demo does not mean that it is of little or no value.

Broaden your horizons and accept that maybe, just maybe, you can learn a lot without seeing a actual demo all the time.

You may be surprised at the number of amazing sessions you have been missing out on.

Announcing SQL Summer Camp

A new way to train in SQL Server.SQLSummerCamp_logo1

 

I had an idea a few years back about a training event unlike any other. One where you could bring family, all have fun, and yet still learn about SQL Server from top people in the industry. After years of working things out, that idea has come to be.

That idea is now SQL Summer Camp.

SQL Summer Camp is a combination of making new friends, having fun and learning new things about SQL Server. This is not a rigorous, intensive boot camp style of training, nor will it be like a typical conference where you just sit and listen to speakers talk all day.

Instead, SQL Summer Camp will have a combination of fun activities, training, and social events related to SQL Server. We understand we will have people of various technical levels joining us, so we will have training and activities for everyone. Not only that, we are going to have it in Nova Scotia, Canada high on the wish list of locations to visit of many people.

You can look forward to things like presentations and training from some of the best SQL people in the world, camper-led panels, fun competitions and activities like the SQL Server Sea Shanty Sing Off and SQL Horror Stories by the nightly camp fire.

The goal is to create a near all-inclusive event that was educational and also promoted the social aspects of the SQL Server community. SQL Summer Camp provides a week of training, meals, events and activities centered on SQL Server and recharging your own batteries.

We know being alone for a week of training in a great location is sometimes awkward for friends and family. They usually want to go to the location as well, but don’t want to take the training, or maybe even aren’t all that geeky about SQL Server. Don’t worry, we have that covered. We offer a program for your friends and family, we call them Stowaways, and they have an entire week of events and activities they can participate in while you are in training. They also get to join you and participate in our larger group events like the SQL Server Schooner Sunset Sailing!

 

There is so much to be experienced at SQL Summer Camp it can’t be conveyed in just these few paragraphs.

Check out www.SQLSummerCamp.com for more information and we will see you at camp next year!

 

We’re All Mad Here!

We're All Mad Here The title says it all!

I just heard that I was selected to present at SQL Saturday in Columbus! I’m excited to be going back, and add another tick mark to the list of events we have been to. In reviewing recent and upcoming events though, I have come to realize “We’re All Mad Here”!

As many of you hopefully know, I have a SQL Server Boot Camp coming up in a couple of months that I have been working on putting together for a bit. Now, I will admit that there has not been a mad rush to sign up (even with 25% off pricing that end on 5/16/2014! Hurry now to join in!) and it has resulted in my slowing down some on getting the materials put together. It will be done on time though, but I may disappear for a couple of weeks while I get things caught up.

But fear not! I will not be completely gone. I may not be online much, but you will be able to see & hear me and make sure I am getting things done at this huge list of upcoming events:

5/9/2014 - SQL Saturday Houston

5/14/2014 – Webinar – PASS DBA Virtual Chapter Meeting

5/21/2014 – Webinar – Optimizing Protected Indexes

6/7/2014 – SQL Saturday Philadelphia

6/12/2014 – PASSDC Chapter meeting

6/14/2014 – SQL Saturday Columbus

6/21/2014 – SQL Saturday Louisville

7/17/2014 – Richmond PASS UG Meeting

7/21-7/24/2014 – WaterOx’s SQL Server Boot Camp DBA Level 1

Ok, so now you see why I just went into a mad panic mode. This is just a list of the events I will be presenting at until my Boot Camp fires up.

It isn’t even including any existing client activity, nor the potential activity of some new clients I am working on.

But, you know what?

It’s okay.

The advantage of being mad and having a schedule like this, is the big-ass Cheshire cat grin.

It is big enough that whatever you bite off isn’t too much to chew.