How to Cheat at Securing SQL Server 2005

I recently had the opportunity to contribute a couple of chapters to this new SQL Server security book from Syngress. The concept of the book is to provide a fundamental understanding for harried IT workers on how to use SQL Server 2005's security features to tighten down their SQL Servers. The book is intentionally broad but each author tried to put in best practices and Microsoft recommendations where possible.

I believe this is the first SQL Server 2005 security book on the market. There are a great deal of additions in SQL Server 2005 with respect to security which can prove to be a steep learning curve from SQL Server 2000. Hopefully this book serves to speed along the learning process.


Technorati Tags: | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2005 | Security | Database Security | SQL Server Security | Writing

One thing is always certain about information technology: there is always change. This past week I was pitching in on a Citrix upgrade for my organization and I went to tweak the web interface. Though I'm not primarily a "server guy" and directory services administrator, I do have a web developer skillset (in fact, that's how I got my start where I work now). However, it's been a few years since I've done anything but touch up work with regards to web development and initially I got that blank feeling... the one where you know how to do things but it's like your mind is cycling through the archives to pull back that information and bring it to the forefront. After a thankfully brief period of "brain thrashing," I went to it.

This experience reminded me of a .NET Rocks! episode with noted Windows programming guru, Dan Appleman. In the episode Mr. Appleman talked about the concept of discoverability. Quite frankly, IT has grown so big that no one can know it all. The key then is to know where to find the information you need to solve the problem. Facing this issue, he ran across Google custom search and used it to build SearchDotNet.com, a "search engine" which hits the sites Mr. Appleman, in his expert opinion, are the ones he'd want to search against for .NET questions. Rather than getting all the dross out there from everyone and his brother who might want to throw up a snippet of .NET information on a blog or web page, the search domain is intentionally narrowed to produce more usable results, thereby hopefully reducing the time to find a solution to a .NET related problem.

Sticking just to SQL Server, there is so much to it now that one person knowing it all seems less and less likely. SQL Server MVP, Kalen Delaney, has noted that there is a plethorea of topics for SQL Server 2005 in her introduction to Inside Microsoft SQL Server 2005: The Storage Engine, where she writes, "As I mentioned, even in four volumes, certain features and aspects of the product cannot be covered." MySQL is becoming much the same way. With each new version comes new features that eventually it's going to be like SQL Server, if it isn't already. You just won't be able to know it all. Finding the answer to a problem in either space then comes down to a discoverability issue.

The experience has reminded me I need to brush up on my CSS (Cascading Style Sheets) skills and I'll do that over the next week or so. However, I realize I won't be able to become an expert in CSS. However, I don't have to. As long as I know what I'm looking for and how best to find it, I should be fine. Web development isn't my core skillset any longer. Therefore, I can't spare the time to gain expert knowledge of it any longer. There's too much to keep with in Windows servers, SQL Server, and security to try and spread myself any thinner. Good thing I don't have to, as there are many, many experts who have given back by posting information that's only a targeted web search away.


Technorati Tags: Citrix | .NET | .NET Rocks | Dan Appleman | Kalen Delaney | SQL Server | Microsoft SQL Server | SQL Server 2005 | Books | Work | Skills

I just finished the book Brute Force: Cracking the Data Encryption Standard by Matt Curtin. It covers the work of the DESCHALL project, the first ones to crack a message encrypted with the Data Encryption Standard (link goes to .PDF Format of FIPS 46-3). This was in response to a contest challenge by RSA Data Security (now owned by EMC). The first person to crack the DES encrypted message would win a cool US$10,000. What followed were several groups using distributed computing to divy up the possible keys and then brute force until a key was found. The DESCHALL group got it first. I remember the DES message being cracked in 1997 and this book piqued my interest.

The book is an interesting look at how a loosely organization coalition of folks all focused on the same goal can accomplish a significant achievement. It's also a great demonstration of how powerful distributed computing is, even on desktop machines. From a raw computing power perspective, some problems are easier to solve in a distributed architecture than on a supercomputer. Cracking the DES-encrypted message was just such a problem. This is why projects like SETI @ Home offer us hope to accomplish things that otherwise might be impossible in today's age.

The book is light on the technical side. For instance, Mr. Curtin points out that the DESCHALL clients used UDP, which was a far more efficient protocol for what they are trying to do than TCP. But rather than delve into the minutiae and spewing techno-speak, he gave a high level explanation as to what made UDP better than TCP for their implementation at a level where non-technical folks can go, "Okay, that makes sense," without technical folks going, "You oversimplified it to where it's wrong!" Therefore, this is a book that's accessible to non-techies as well. If you are interested in encryption, especially with all the goings-on in the late 90s (remember low and high encryption versions of IE and Netscape?), this book is a good one for that.


Technorati Tags: Security | Network Security | Encryption | DES | Data Encryption Standard | DESCHALL

Watching a Chefograpy on Giada De Laurentiis, I learned that she was considered very much an introvert and had a hard time in front of the camera. However, over time she has overcome all of this and has become more comfortable around people and being on film. I've always been shy around people I don't know, and that makes meeting people hard. Whether it's due to my upbringing or what, the show made me think about the fact that this is one area of my life I need to improve on. I had heard good things about Never Eat Alone by Keith Ferrazzi, and checked it out from the library. The gist of the book is about how to become better at networking through the building of relationships.

It was a very good read and I took notes on every chapter. A lot of is common sense, but while the points are common sense, they aren't necessarily easy to do. For instance, chapter one points out it is foolish to not ask for help. In our "pull yourself up by your bootstraps" culture, asking for help is akin to admitting weakness. However, Ferrazzi gives examples from his own life where his father wasn't afraid to ask for help for his son and because of his father's willingness, Ferrazzi was able to get into doors for his education he wouldn't have been able to otherwise. Some of the key points I pulled out:

• We need others to succeed.
• Be willing to ask, even if others would consider it embarrassing.
• True networking isn't about using people to get what you want, it's about caring and concern for others regardless of status.
  
• Work hard to reach out to others.
• Be sincere in your efforts to make connections with people.
• Remain humble.
• Make something of the time and effort a mentor is willing to impart.
• Measure success by love and friendships, not by income, position, or hype.

As I said, these aren't stunning revelations. However, the fact that a "master networker" is pointing them out as the keys to his success is a reminder that candor, openness, sincerity, and compassion still work in this world. That's a nice, refreshing message and its solid encouragement for me to work on being able to meet and share with others.

If you're interested in reading some excerpts of the book, Mr. Ferrazi has a few chapters available on-line. Mr. Ferrazi also has a blog which he posts to semi-regularly.


Technorati Tags: Life | Work | Personal Development | Relationships | Networking

The Cuckoo's Egg by Clifford Stoll has been around for a while, having been published in 1989. It details how a system administrator (a trained astronomer who had to find something else to do) tracked a malicious hacker through his system and numerous others including defense contractors and unclassified DoD systems. It's one of those books a lot of folks who work security say should be read if you're in the field. When I was a cadet at The Citadel, one of the other guys in my company was reading it and said it was a good thriller of a book. I meant to borrow it from him and never did. Then I meant to read it for some time but every time I thought about it, I would subsequently forget to go look for it or check it out from the library. Well, I finally did read it and found that my friend's assessment was a good one. I think my wife would agree as she swiped it away from me before I was done and finished it first.

As I went through the book I watched for security principles in play and what was true in 1989 in large part holds true today. Some of the things that were revealed as Mr. Stoll went through his meticulous process of tracking the intruder who was working for the KGB:

• Honeypots are effective to attract an attacker and learn about his or her methods. In the book Stoll's roommate comes up with an idea to place what look to be classified documents on a military defense system on one of the servers and to keep it updated so as to look like a regular project that is progressing. This is ultimately how they get the attacker to stay connected long enough to trace him. Honeypots are used today to attract attacks, especially automated ones, so we can analyze them and learn to defend against them.
• Dictionary based passwords don't work. The attacker in the tale kept grabbing the password file from the servers he was attacking. Stoll at first couldn't figure out why because the passwords were encrypted with a one way function which meant if you had the actual password it was easy to get the encrypted hash, but the opposite, where you have the hash and want to get the actual password wasn't true. However, the algorithm used to encrypt the passwords was well know. So if you calculate all the hashes for a set of words, you can compare the hashes and figure out what the passwords are. BTW, this is an issue with Windows passwords. Do a search for rainbow tables and you'll find several sites that have rainbow tables for Windows-based passwords.
• Just because you can't see the monitoring devices doesn't mean you aren't being watched. Stoll put a line printer before the server itself, meaning he got an output of everything that was going back and forth on the line. this allowed him to watch the attacker as he came and went. Nothing was running on the server itself. This is analogous to two things in today's world: sniffers and rootkits. Sniffers watch the wire and from the server you can't tell you're being watched. This is why encrypting sensitive data across untrusted lines is important. Rootkits are running at a level where they can intercept any calls you make to try and detect them. That's why there was so much concern over rootkits (and still is).
• When doing forensics work, keep a log. This is a no-brainer. Log everything you do, who you speak to, every step. Time and time again Stoll went back to his log. Because he had it, he was able to connect a lot about the attacker's behavior, prove he had informed the right people of what happened, etc. This is actually a good rule for troubleshooting. Log everything you do because you (a) want to be able to undo anything that didn't work and (b) you want to know how exactly you fixed a problem.
• Don't assume your system has no value. Stoll's system didn't have classfied secrets on it. But it did represent a jumping off point to attack other systems. Frequently I have conversations with folks about securing development servers. To the attacker, a development server may be just as valuable as a production server. If a system is on your production network, it needs to be secured.
• Don't assume you are secure. Stoll found several folks who assumed their systems were secured. The evidence showed otherwise. Paranoia is good in the security field. Let me rephrase that... controlled and focused paranoia is good.
• Check your logs frequently and investigate inconsistencies. Stoll stumbled onto the hacker because of a 75 cent accounting error. That's what started the whole trace. The better an attacker is, the less likely he or she is to leave clues. Therefore, even the smallest details are important.
• Change default accounts and passwords. The attacker kept breaking into systems because administrators had left default accounts and passwords active. Blank passwords, passwords of password (or some derivative), and default passwords are all bad. If an attacker is knowledgeable of the defaults and we leave them active, we've opened the door. It was amazing how many systems the attacker got into using this simple method.
  

Technorati Tags: Security | Database Security | Network Security | Windows Security | SQL Server Security

I've taken some time to update my website. In particular I have updated:

• Articles
• Reading List
• Chess Reading List
  

Nothing fancy or special, but just some needed updates.

Technorati Tags: Chess | SQL Server | Microsoft SQL Server | Reading | Writing

Saw this first here: SQL Server 2005 Security Best Practices. It's on the blog for Microsoft UK's SQL Server Premier Field Engineers. There's also some security-related webcast information on that blog post, so check it out!

Microsoft has released a whitepaper on best practices for SQL Server 2005 security. You can find the whitepaper here:

  SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

It was written by Bob Beauchemin of SQLSkills.com. A lot of it is common sense type of stuff for old hat SQL Server DBAs, but there's coverage of Vista and the new pieces which are specific to SQL Server 2005 such as Surface Area Configuration (and transferring settings from one server to another), what to audit (including those with CONTROL SERVER rights), and securing schemas. It's only a 30 page document and should be considered a must-read by anyone responsible for administering a SQL Server 2005 system.


Technorati Tags: DATABASE | SQL Server | Microsoft SQL Server | SQL Server 2005 | Security | Database Security | SQL Server Security

I'm working on a new article for SQL Server Central talking about physical security and SQL Server and I was going to include coverage outside of the data center, especially with products like Microsoft CRM 3.0 putting potentially sensitive data on a laptop. There are significant legitimate business drivers to do this for business so therefore we IT professionals are charged with trying to secure that data as much as possible while still enabling our business folks.

Coincidentally, this blog post on Windows Mobile security came out this week, Windows Mobile Security Resources, which has good links to on-line resources in this regard. It's written by Security MVP, Jim Wilson, and includes a link to an article on the Windows Mobile Security Model, which he wrote earlier. If your organization uses Windows Mobile devices, especially if you have databases or mail on them, you may want to check out the link.



Technorati Tags: Security | Windows Security | Windows Mobile

I read through a lot of blogs each day in a variety of technology categories. I've always fashioned myself as a jack-of-all-trades and that helps me a great deal with my current position. However, it does mean consuming a lot of feeds to try and stay up in all the areas I have a profound interest in. Here are my shared feeds on share.opml.org.

Google Reader has a nice feature where I can share items I find interesting. There are a ton of good blog posts each day, so I've started marking them to be shared. There are two ways to view these shared items: one is the web page and the other is through the RSS feed.


Technorati Tags: Google Reader | Blogging | Sharing Feeds | RSS | OPML | Reading List

It has been a trying month to month and a half with the implementation of Microsoft's CRM 3.0 product. By the very nature of the application, there is a signficant amount of data conversion/import as well as mapping and aligning the application to the business processes of our customers. I finally feel like I can breathe again... with that said, I should hopefully be able to devote more time to "extracurricular" things like blogging, reviewing QoDs (and hopefully submitting a few more), and writing articles again.

There is a new site for SQL Server blogs, SQLBlog.com. "Brought to you by Peter DeBetta & Adam Machanic," it has been up for about a month but is starting to pick up bloggers. Adam will be mirroring over to SQLJunkies for a while, but will eventually cut over to just SQLBlog.com. Current bloggers with posts include MVPs Peter DeBetta, Andrew Kelly, Hugo Kornelius, and Adam Machanic.


Technorati Tags: DATABASE | SQL | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2000 | SQL Server 2005

If you're keeping up with Windows Vista and you're interested in the security aspects of it, there's a new blog, with an appropriate title, from Microsoft:

Windows Vista Security

Microsoft has said it has designed Windows Vista to be more secure than previous operating systems, having learned a lot from its Trustworthy Computing initiative. Comparing the difference between Windows XP SP1 and Windows XP SP2, that was definitely a step in the right direction. Now MS has a whole track devoted to Vista at the BlackHat conference (days 2 & 3). Given what Microsoft is saying about Vista, it certainly seems like Microsoft is attempting to walk the talk. Having played with Vista on a decent laptop I have at work, I'm annoyed at all the pop-ups whenever I try to work with the configuration of the system, but then, this isn't something the regular work user would be doing, so I'm not too displeased. I know there will be some finetuning of these pop-ups before RTM ships as too many pop-ups can lead to a fatigue where the user just clicks yes without thinking about it.



Technorati Tags: Security | Windows Security | Windows Vista

Haidong Ji, co-author of Professional SQL Server 2005 Integration Services, blogged on Learning Perl using the Perl Debugger. Haidong posts SQL Server related content here on SSC.com but for his other interests include regular life, DotNetNuke, and other technologies, you'll find them on The Ji Village News. Here is the post:

Learn Perl through Perl Debugger

As to the post, Haidong had just gotten back a training class on Perl and learned a trick using the debugger to get instant feedback on checking the syntax of individual statements. It's a great tip whether you're new to Perl or an old guru with the language. Check out his post if you want to learn this neat way of learning Perl better.


Technorati Tags: Perl

I wrote a new article for SQL Server Central on SQL Server 2005 Logins. It covers the basics. This is the first in a series of articles on SQL Server 2005 security.

Technorati Tags: Database Security | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2005 | Writing

The last few weeks I've been working on disaster recovery procedures for my organization. We review them at least yearly to ensure we've taken into account new applications, changes to the infrastructure, etc. This sort of thing is supposed to be done after any change, and our change control procedures handle that, but it's always a good idea to double check the procedures as a separate practice. After all, this is one area you don't want to make a mistake in.

One of the situations any SQL Server DBA may face is that there will be less hardware available in a disaster recovery situation. After all, each additional server purchased for a secondary operating location or placed on a disaster recovery contract is extra cost for the organization. Therefore, a smart organization tries to reduce this cost like it would any other expense. If an organization can get away with 1 SQL Server in the first few days of a DR situation when it would normally operate with 5, the organization will do so and plan on increasing capacity after critical systems are brought on-line. This can put the DBA in a quandry as he or she consolidates the contents of these 5 SQL Servers onto 1.

There are a lot of challenges to this, but one of the biggest is ensuring all needed logins are created. Windows logins aren't a big deal. Execute the appropriate sp_grantlogin and things go swimmingly well. Restore the user databases and the SIDs match up. Since the passwords are on the Windows-side, there isn't anything else for the DBA to worry about. But SQL Server-based logins are a totally different story. Some third party applications have a set password that cannot be changed. Sometimes this password is created by the application when it is installed, meaning no one knows what the password actually is. A couple of the applications I deal with have this problem. Therefore, a DBA can't just execute an sp_addlogin command with any old password and get things to work with the application. The application doesn't allow for the password to be changed. That means the correct password must be set. But if you don't know what it is to begin with, does that mean you have to crack the password? No, it doesn't. That's time-consuming and ultimately unnecessary. Another issue is matching up the SID between the login and the user. Yes, running sp_change_users_login can get the SIDs to match up again, but that's unnecessary, too. In SQL Server 7.0 and SQL Server 2000, both the password and the SID is stored in the syslogins system table. If there was a way to extract this information and build a script using it, we'd be all set. After all, sp_addlogin allows for us to set the SID and the password (even in an encrypted state). And Microsoft has provided a solution in the following Knowledge Base article:

How to transfer logins and passwords between instances of SQL Server (246133)

The KB article has a script which creates two stored procedures: sp_hexadecimal and sp_help_revlogin. These get added to your master database. When you execute sp_help_revlogin (which uses sp_hexadecimal), it'll create a script that recreates all the logins on the SQL Server in question. An example output is the following:

/* sp_help_revlogin script
** Generated May 20 2006  9:40PM on TESTSQL */
 
DECLARE @pwd sysname
 
-- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'

-- Login: builtin\users
EXEC master..sp_grantlogin 'builtin\users'
 
-- Login: TestUser
SET @pwd = CONVERT (varbinary(256), 0x0100B7745C01DBEE94BE4032E5A8382A75A6C334DBE408884646976A9B723EC1B6B8D43D596BC2DE97DEA7C4005F)
EXEC master..sp_addlogin 'TestUser', @pwd, @sid = 0x813E48ED68B83C438FA9DF3D1CBFD70D, @encryptopt = 'skip_encryption'

Looking at the output, you've probably noticed that it doesn't set the default database and language. Some applications aren't smart enough to change the database. In a disaster recovery situation this is not something you want to be troubleshooting. That means you want to go ahead and set the database and language when you create the login. That is easily remedied with a script like the following:

SELECT 'EXEC sp_defaultdb [' + name + '], [' + dbname + ']; ' +
       'EXEC sp_defaultlanguage [' + name + '], [' + language + '];'
FROM syslogins
WHERE name <> 'sa'

All of the generated login scripts can be combined into a set of "master" login scripts to run in a DR situation. Keep in mind that these scripts need to be protected because even though the SQL Server login passwords are encrypted, there are tools that can crack them. Therefore, treat them with the appropriate care.

I'll be blogging more about disaster recovery and SQL Server in the coming weeks. Let me take this opportunity, though, to plug an old friend, Chris Kempster. Chris has written an excellent eBook on SQL Server disaster recovery and it is provided free by Quest Software: SQL Server eBook: A Practical Guide To Backup, Recovery, and Troubleshooting. You will be required to create a login on the Quest site. You can find a review of the eBook here on sql-server-performance.com.