Security

Dates can easily be the hardest datatype to work with.

The datetime data type can be one of the hardest to work with when it comes to index optimization.  Most queries that use a datetime data type for filtering (part of the where clause) only want to match the date portion of the value.  Most people handle this via a convert function around the date column.  This causes the index that you create to become useless as the convert function causes the index to be scanned not seeked.

Continued »

What do you mean I can’t access my own database when trying to attach it?

When you detach a database from Microsoft SQL (I’m talking SQL Server 2005 and up here) the SQL Server automatically changes the NTFS permissions on the file so that only the user who told the SQL Server to detach the file has access to them.  SQL Server does this to ensure that an unauthorized person isn’t able to access the data files. Continued »

If I wanted to learn the law, I would have been a lawyer

So your probably a lot like me, you were a teen who liked to play with computers and you managed to end up in IT.  This is awesome you figured, I play with computers and someone actually pays me for this.

During the 90’s life was good, there wasn’t any regulations to deal with, you followed best practices to the extent that the company you worked for could afford them.  Change control usually consisted of sending out an email saying “Hey, were going to change a bunch of stuff, nothing should break.”.  Today however things are different, very different depending on how large the company is that you work for, and if your company is public or private.

Today I work for a private company, so we are able to run things much like the “good old days”, but most are not so lucky.  Change control processes are cumbersome at best, and the number of legal compliance issues that we have to not only be aware of the existence of, but actually understand is quite daunting.  You’ve got everyone’s favorite SOX which says that lots of stuff needs to be controlled and duties must be separated, but doesn’t give any sort of guidelines as to how to do this, or what duties should be done by who.  If you take credit cards over the web, or process credit cards over the web then you’ve got PCI (which I’m dealing with now on our shopping cart server).  If you work for a company which stored medical records then got help you when it comes to HIPPA.  For those that aren’t aware of HIPPA part of it basically says that every lookup to medical records has to be logged.  Within an application that’s easy.  Windows SQL Server 2008 that’s easy, but what about the legacy SQL 2000 medical application?  It provides no guidance other than to say “do it”.

I remember that when Windows 2003 SP 1 was released (it may have been SP 2) there was a thread on a forum somewhere (probably tek-tips.com) where we were discussing SP1 and HIPPA.  Somewhere in HIPPA is says that you have to keep your systems patched.  Somewhere else it also says that systems which stored medical information on there cannot report data back to a vendor.  Well SP1 introduced code that would allow a sysadmin to have the server report usage and error data back to Microsoft.  So which part of HIPPA should you violate?

On top of all the federal regulations, states are now passing data encryption laws which have to be dealt with.  Here in California we’ve had data encryption regulations in place since 2003 or so.  At several companies that I’ve worked at the IT managers didn’t know anything about the law and what it meant.  The law here in California is so vague that it is almost meaningless.  It says (in laymen terms) that if your data is breached, and the data isn’t encrypted then you have to tell your customers either directly or via the media.  But it doesn’t define encryption, or how strong that encryption has to be.  It at least defines what data items it includes (name, address, username, password, social security number, etc) but if you take the law at face value doing a simple character replacement is sufficient to comply with the law.  While this complies with the letter of the law it obviously doesn’t comply with the spirit of the law, but the letter of the law is what matters in court.

Having to keep track of all these laws which apply to us is mind boggling at best, and impossible at worst.  And reading the laws is amazingly painful.  The California law I sited above, which I’ve read several times, still confuses me to no end; and I’ve reviewed it with the legal team at one company already due to a data breach.  And consider that there are data encryption laws in several states, all of which you have to comply with if you have customers in that state, or if you do business in that state.  I have no idea which states have these laws, or even how many states have these laws.  Even if I did, I’d then need to find the overlaps and the exceptions, then figure out how to build our database to meet these laws.  Beyond that I’d have to anticipate the future laws that could be coming in the other states and account for those potential laws at the same time.

At this point handling the database design is just getting more complex.

Now the new Transparent Data Encryption is great for handling data at rest.  It keeps your backups all encrypted and save.  But what happens when the bad guy breaks in and swipes the data by logging into the database.  Yea the data is encrypted on disk, so technically we are covered, but the data is still out there and usable because the bad guy was able to login as a database user with select access to the tables and dump the data to his system via a SELECT statement.  What has to happen now?

I don’t know about you, but I got into this field so that I wouldn’t have to worry about stuff like this.  I guess those times are over with.

This rant is now complete.  See what happens when I get on a plane at 6:30am and the nice lady starts poring coffee down my throat for the entire flight.

Denny

Who’s been logging into my SQL Server?

Knowing who has been logging into your SQL Server is one of the key things to know about your SQL Server.  It lets you know quite a bit about who’s using your server, and about who’s been trying to break into your SQL Server, and most importantly if they have succeeded.

Obviously you can setup a SQL Profiler trace to capture this information but that requires the overhead of running SQL Profiler, and who wants that.

All version of SQL Server (from 2000 and up at least) provide some level of logging about who has tried to log into the SQL Server.  Within Enterprise Manager or SQL Server Management Studio’s Object Explorer right click on the Server and select properties (if using Enterprise Manger select properties not connection properties).

Select the Security Tab and find the Login Auditing section.  By default SQL Server only logs the failed logons which is good as it tells you who hasn’t been able to log into the server.  However it doesn’t tell you it they have been successful which is why you may want to change this to both failed and successful logins.

Now changing this setting has an upside and a downside.  The upside is that you know who has been successfully broken into your database using a brute force attack and when.  The downside is that every client that successfully connects to the SQL Server will also log an entry, making it very hard to find the correct entry you are looking for.

Where do these entries get logged to you ask?  That’s the other downside.  They get logged to the SQL Server ERRORLOG file and the Windows Security log file.  Which means that these files will fill up fast.  And if you have a large enough client base logging into the database VERY FAST.

In a perfect world, I’d set this screen to both failed and successful logins.  In reality failed is probably all I can do.

SQL Server 2000 didn’t provide a whole lot of information about what is happening as it only says that Login n has tried to connect and failed.  Not exactly helpful as you don’t know who was trying to login to the SQL Server using the sa account over and over again.  SQL Server 2005 and up include a little piece of helpful information, the IP Address of the person who tried to connect to the SQL Server.  This will help tell you who is connecting to the SQL Server so that you can smack them around.

Denny

What’s the difference between encrypted data and hashed data?

The biggest difference between encrypted data and hashed data is that encrypted data can be decrypted later.  Hash algorithms such as MD5 are one way hashing algorithms which means that the value that is returned can’t be decrypted back to the original value.

It is important to know the difference between the two when designing your database encryption schema.  If you don’t need to retrieve the encrypted value then only store the hash.  This way you don’t have the actual data for anyone to steal.

Denny

Why should the DBA care about Network Firewalls?

All to often I see people online asking some sort of question about connecting to their CoLo’ed SQL Server and they connect directly over the Internet.  This is nuts people.  If you can access your SQL Server via Management Studio from anywhere on the Internet so can people who would love to break into your SQL Server and use the machine for their own uses.

SQL Servers shouldn’t every be directly accessible from the Internet.  Even if you have to use public IPs to host the machines, make sure that there is a firewall setup between that server and the public Internet so that no one has any sort of direct access to the machine from outside of the data center.

How do you manage the SQL Server in this case?  You use the router’s built in functions to setup a point to point VPN with your office router so that you can securely communicate with the servers in the CoLo without sending that data in plain text over the Internet.

For that matter while you are locking down the SQL Server, suggest that the Web Servers be locked down as well.  The only ports that they should have open are 80 and 443 unless you are running streaming servers, or known FTP servers.

If your servers have been sitting exposed on the public Internet then I highly suggest that you install an Anti-virus on them and check for Viruses, malware, etc that’s doing stuff you don’t want it to be doing.

Several years ago I was doing some work for a company that had Windows 2003 servers sitting directly on the Internet without an Anti-Virus and with no firewall.  When I got to the machines and took them off the Internet for cleaning there were over 200 viruses on the machines that they had no idea were on there.  There complaint was that the machines were running slow, and network costs kept going up.  God only knows what sort of network traffic these viruses were generating as they did what ever it was they were trying to do.

Denny

Back To Basics: What are indexes and what are they used for?

A while back someone posted on the ITKE forum asking what Indexes where, and what they were used for. I put up a quick answer, but I felt that it deserved a more in depth blog post; so here it is.

Continued »

Phishers Are Going After Twitter

Apparently Twitter has officially gone mainstream.

Why do I say officially when they are been around for years and have over a Billion tweets?  Because phishers are now going after Twitter accounts.

So far they appear to be only getting your twitter account info and sending more messages to other people.  I would imaging in a short amount of time these attacks will become more nafarous.

Denny

JournalSpace.com Says the site was trashed by the IT guy

The owner of JournalSpace.com has posted an update to the site (screen shot for posterity) giving more information about what happened.

Apparently the IT guy who liked to tell people how smart he was decided to rely on RAID as a backup for the database, but had automated backups of the web servers.  He was apparently caught steeling from the company and wiped out the SQL database on his way out the door.

Apparently my suspicions were correct and it wasn’t a system problem, but a person who deleted the data.

Andrew Hart posted a note on how some of the users are able to get there data back using the Google cache.  I tried using the Internet Wayback Machine but apparently JournalSpace.com was set to not allow it to be archived.

I would recommend to the owner of the site that the contact the local police department and file a report.  While company employees can’t be held liable for stupidity, intentionally destroying the company we can be held liable for.

Denis Gobo posted an update as well, as I’m sure others did as well.

Denny

UPDATE: I forgot to include that I’m following the JournalSpace.com user on twitter so that I can keep abreast of new updates.

SECOND UPDATE: My horrible spelling was pointed out to me, so I’ve corrected this. Apparently Firefox didn’t pickup the spelling problems the first time around.

Mirroring isn’t a backup solution

In case you live under a rock and haven’t heard about Journalspace.com’s little mistake, they have gone out of business due to a database problem.  Here’s a screenshot in case the site is down when you look at it.

In a nutshell it appears that they were relying on a RAID1 array as the database backup.  While we see this all the time in small database shops as noted on /. this site has been up since 2002 and had an Alexa page rank of 106,881 with 14k monthly visitors (according to Quantcast).  For a site so large to be making such a simple mistake is just unacceptable. Continued »