IP Address storage in Microsoft SQL

Work has been hectic for the last couple of weeks. I am working on creating one database while updating/tweaking another one. The database I’m developing isn’t causing me any problems because it’s pretty straight forward. The other one, though, is causing me a lot of headaches.

One of the improvements I’m adding to the second database is the ability to view all IP addresses available at any given location to help with inserting static IP use records. Sounds easy enough until you consider that I don’t store every possible IP address for the locations. There are 30+ locations and each location IP range supports over 1,000 IP’s (Class B subnet). Because each location uses less than 100 static IP’s, I don’t see a reason to store all those IP’s without any correlating data.

The initial database creation only had two tables for IP storage: location IP range & equipment (computer, printer, etc.) static IP mapping. The location IP ranges table holds the location ID (details are in another table) and the individual IP octets. That means a range like 192.168.1.1 - 192.168.5.255 uses eight (8) columns to store the range. That was the first tweak, breaking down the IP addresses already stored and storing the individual octets.

I know it sounds odd to not store the IP as a whole, i.e. 192.168.1.1, but there are a couple of good reasons. One is storage space and the other is the ability to perform arithmetic operations against the IP without having to parse it using PARSENAME. The second reason came in really handy for the stored procedure showing all possible IP addresses for the locations.

The storage space savings isn’t huge but it can add up later. I found an article showing why it’s better to store the IP octets individually in tinyint columns. Tinyint works well for storing IP octets because it only supports numerical values from 0 to 255. Storing the IP this way also helps if I want to index the octet columns (combined and/or individual) to make searches faster.

Another plus is sorting the data returned. While stored as a whole IP, sorting the returned record set can cause unexpected outcomes (i.e. .100 before .20). Storing the individual octets allows me to sort the data by octet three and four (respectively) to get the record set sorted in the proper order.

Storing the IP addresses as individual octets can cause a problems. One of the problems is it can complicates queries. Queries to find the full IP address have to concatenate the cell values. So, if 192.168.1.1 is stored as individual octets and you want to pull the IP back with the decimal point, the query would look something like this:

SELECT CAST(Octet1 AS VARCHAR(4)) + '.' + CAST(Octet2 AS VARCHAR(4)) + '.' + CAST(Octet3 AS VARCHAR(4)) + '.' + CAST(Octet4 AS VARCHAR(4)) AS IPAddress
FROM IPTable

The octets have to be explicitly converted or SQL will try to create a decimal number with the concatenation. The I/O cost to cast tinyint to varchar is minimal.

I’ll post an example of the stored procedure to show all possible location IP addresses on the next post. The stored procedure will not only return all of the IP addresses in the range but it will also show information about any equipment using a static IP address (or nothing if the address is free).

Men do not quit playing because they grow old; they grow old because they quit playing. - Oliver Wendell Holmes

21.Aug.07 Microsoft SQL Comments (0)

No, you can’t use the sa login. Not yours.

As previously posted, I’m in the process of locking down the MS SQL 2005 server that I am in charge of maintaining. The built-in admin account group was no longer in the sysadmin group so it was time to move to the next hole. The ’sa’ account.

The ’sa’ account is the default admin login that has full control of the MS SQL server. If the server is only going to use Windows Authentication, a random password can be created and forgotten. A Windows domain user would be assigned to the sysadmin SQL server group because ’sa’ could not be used (SQL logins disabled). This isn’t the case for my server. I had to set it up for mixed mode authentication. Mix mode means that Windows Authentication or SQL logins can be used. The reason I had to use mix mode is due to some software that we use that does not support WA for it’s SQL connection.
(more…)

04.Aug.07 Microsoft SQL, Security Comment (1)

Locking down the SQL Server

Security has always been a big concern for me. I finally had a chance to do a security check on my Microsoft SQL Server 2005 system. In order to perform the test, I used two free tools from Microsoft:

SQL Server 2005 Best Practices Analyzer
Microsoft Baseline Security Analyzer 2.1 (Beta 2)

The results weren’t bad but they weren’t great.

The test were fairly thorough. The BPA tool is the most detailed for SQL while the BSA ran general SQL and Windows 2003 test. I have already started implementing some of the changes. Other changes will have to wait because they will require shutting down the SQL Server.

The first recommendation I implemented was removing the BUILTIN\Administrators group (installed by default). This group allows anyone in the local Windows Server Administrators group to be a sysadmin. The local Windows admin group contains the Domain Administrators group so, in effect, they were SQL Admins.

I had left that SQL login group in if any of the other admins needed to control the SQL server in times that I’m not available (rare but it can happen). Recently, one of the Domain Admins had been taking advantage of that by using Windows Authentication to login and create databases or alter data. SQL DBA’s don’t like non-DBA’s playing around in their server.

This network admin wouldn’t even ask me about doing anything on the SQL server first. Sometimes, I would just find a database that wasn’t there the previous day. They would create the database and set up an application like SharePoint to use it before I would have a chance to configure it correctly. It’s always a little more difficult to correct a database after people start using it.

I don’t have to worry about that anymore. No more telling them to stop doing it when it’s too late. Now, none of the network admins have SQL rights above “connect”. They only have database specific rights (datareader, dbo, etc.).

Microsoft has a whitepaper entitled SQL Server 2005 Security Best Practices - Operational and Administrative Tasks that is a great pointer for securing a MS SQL 2005 server. It isn’t an “everything you need to know” kind of guide. It is a good starting point, though.

Trust your own instinct. Your mistakes might as well be your own, instead of someone else’s. - Billy Wilder

02.Aug.07 Microsoft SQL, Security Comments (3)

Take a load off

Getting information from a SQL database can be fast or slow. The speed depends on many factors like database design (tables, views, etc.) and how queries are written. It is usually faster to parse the data using a query on the database server versus returning all data rows to the application and then parsing.

A coworker that developed a couple of ASP pages came to me and said, “The page showing who is and isn’t clocked in for the day is really slow. It’s taking anywhere from 3 to 7 seconds to finally show up. The view being used needs to be fixed.” I tried to figure out what view he was talking about because I hadn’t written one to show who was/wasn’t clocked in for the day.

It turned out that the ASP page was written to use a view that would return all time records for each person that had clocked in or out. The view joined five tables together in order to show all of the information required (name, location, etc.). The view didn’t just show the people clocked in on that day, it was written to show historical records as well. Once all of the rows were returned, over 800 and growing, the web server would then parse the records and only display the ones that matched today’s date. Anyone not clocked in would just have blank values for the time in and time out.

The average execution time for that view is 90 milliseconds (16 ms CPU time). The parsing time required to only display records for the current day was killing the page load time. I wrote a new view, using the same tables, that would show anyone that was clocked in as well as anyone that wasn’t. It utilizes the DATEDIFF, DATEPART and GETDATE functions in the WHERE clause to determine who is clocked in on the same day the view is executed. Here is the WHERE clause:

WHERE (DATEDIFF(dy, GETDATE(), ClockIn) = 0) AND (DATEPART(yyyy, GETDATE()) = DATEPART(yyyy, ClockIn))

The WHERE clause tells the query to only return rows if two conditions are met.

1. Where the difference (DATEDIFF) of the day of the year (dy) between the date on the server (GETDATE) and the clock in time is 0 (today).
2. Where the four-digit year (yyyy) of the server date and the clock in date are equal.

The year check is necessary because up to two (2) years worth of records will be stored in the table. The “day of year” value range is 1-366. You can see where an error would occur when the same date, but different years, are stored. The SQL server and the web server are time synched to the same NTP server.

Another query in the view is used to show anyone not clocked in. The two queries are joined with a UNION operator so that the data is returned in a single result set. Anyone that isn’t clocked in will have NULL values for their time. The new view had an average execution time of 10 milliseconds (2 ms CPU time). The web page was re-written to display the values returned from the SQL Server and now loads much faster because there is no need to parse the returned rows.

Rudeness is the weak man’s imitation of strength. - Eric Hoffer

03.Jul.07 Microsoft SQL Comments (0)

No, I won’t use MAX

I was talking to the web developer at work the other day. During the conversation, a network tech came up and said he had a problem with the timesheet web page. The problem was an SQL error due to a comment being to long. The field is supposed to be used when they clock in late and need to explain why. I have the column set to varchar(200) so it should be plenty of room. Many of the techs have started keeping a few records in that space to track mileage.

The web developer said, “Why don’t you just set the column to varchar(MAX)? That will fix the problem.”. In case you don’t know, varchar(MAX) was introduced with Microsoft SQL 2005 to be used in place of the text and image data types. The reason is that those two data types are going to be removed from future versions of Microsoft SQL.

Yes, his suggestion would work perfect except for the fact that it’s a bad database design in this instance. The varchar(MAX) data type can hold about 2 GB of data! Each character entered into the cell equals one byte. That means that a single entry in that column can hold over 2.1 million characters. Why in the world would anyone need to make a comment that big? I would probably use varchar(MAX) if we needed to store blog/wiki entries or news stories. Those kind of entries can potentially exceed the 8Kb limit of the non-MAX varchar data type.

I told the web guy that using varchar(MAX) was out of the question. I said that I would increase the size for comment storage from 200 to 250. If they still have a problem saving a comment, they’re out of luck. We don’t need a freaking book, just a couple of lines.

The unforgivable crime is soft hitting. Do not hit at all if it can be avoided; but never hit softly. - Theodore Roosevelt

14.Jun.07 Microsoft SQL Comments (0)