Monthly Archives: September 2012

#sqlsat160 recap

Josh is the man! There was a lot of people involved in making of SQL Saturday 160 in Kalamazoo and I don’t want to discount their efforts, but he overcame quite a bit of stress and made everything look very polished. I volunteered again this year and will continue to volunteer as long as I’m welcome and things go this well.

Speed Pass simplified the Friday preparations. There was quite a bit of printing, boxing and packing things that I wasn’t involved in but the bag stuffing definitely took much less time than last year. I think I was in and out in about 90 minutes on Friday with all of the 300 bags stuffed.

just some of the redgate bags

Saturday morning started sadly for me at 4:30am. It was the last day my brother-in-law was stateside on leave. The family got together at the Kalamazoo airport to wish him well.

The hustle and bustle helped distract me. I was happy to get to work at 7:00am and start accepting early arrivals to Kalamazoo Valley Community College. I like working the registration desk in the morning. It’s a great chance to make a good first impression and see a lot of familiar faces. I’m happy our volunteer coordinator @muaddba stuck me there.

I was free at 9:00 so I was able to attend all three of the morning sessions. Adam Belebczuk covered AlwaysOn very well in an organized fashion and all of his demos worked flawlessly. I understood it wasn’t a session about clustering but he entertained a gentleman who was obviously confused. This guy started to get very distracting asking question after annoying question and Adam handled it with class.

Next, I attended Eddie Wuerch’s Internals session. Eddie has some great depth on the topic. The thunderstorms were comically loud and severely distracting but he managed to fight through it. The new spotlight session format worked out quite well which allowed Eddie back-to-back sessions to dive a bit deeper. This format is a great service to the community. It worked out great especially for me because I had attended Eddie’s session on waits at SQLRally and now was able to get more information on how they related to internals.

For lunch Taco Bob’s came in and setup a buffet for us. I think it was a step up from the subs last year. It was a bit messy and I hope the KVCC janitorial team can forgive us.

Wendy Pastrick‘s Transactional Replication session was just what I needed. She covered the topic in a very easy to understand manner. Unfortunately her demo didn’t quite work which is pretty vital for beginners to see the secondary tables populate. It was really interesting to learn that you can modify some of the replication procedures to better suite your needs. Also, she mentioned that there are people who’s databases get so large they can’t re-initialize in a reasonable amount of time. Fortunately, it sounds like there are tools that can help us get around this hurdle.

Benjamin Nevarez has got some incredible depth to his knowledge of the query optimizer. I learned how to experiment with optimizer rules and do some pretty terrible stuff.

Finally, I attended Collen Morrow’s blogging session. She had a lot of great advice for me even though I’ve been at this a couple years now. I was especially glad to hear she had some of the same opinions as me regarding the personal blog/employer relationship. Sometimes I get a little concerned that one day my company might feel they somehow own part of my personal brand I am working on but I am more confident that concern is un-warrented.

The closing ceremonies had quite a bit more prizes than I remember last year. I printed my speedpass but forgot to bring it so I didn’t enter in a lot of the larger prizes.

After that I was off to the Kalamazoo Beer Exchange.

A couple hours later, a slight change of plans brought me to Tim’s house where Cards Against Humanity filled the place with lulz. Many thanks to @amyford70 and Tim for the generous hospitality. What a great day!

Leave a comment

Posted by on September 24, 2012 in PASS


Common SQL Server Security Mistakes

After demonstrating some attack methods, there is always one question from the crowd… “How do I prevent this?” It’s a great question and I would like to summarize my last few posts and answer that question. In the alternate ending the attacker actually got to see the DBAs password in plain text. So here is a list of things you can do to prevent that and why they work.

Don’t allow direct access to SQL server from the internet. Forcing attackers to jump through several hoops and pivot their attacks exponentially increases the difficulty.

Patch your servers. SQL has had a great track record for exploits specific to the database engine. Unfortunately, SQL only runs on Windows which has not had a great track record. Most exploits that fall into metasploit have a patch released by Microsoft… eventually. You need to make sure you get that KB hotfix. Make sure you are auditing your patching process.

Don’t enable the SQL Authentication protocol. This will block common tools that use SQL authentication and the known account ‘sa’. They won’t be able to connect with SQL Authentication so there is no way they will be able to brute force the password. Windows integrated is better. <– period

Don’t enable or use the ‘sa’ account. If it’s enabled, it can be brute forced.

Leave password policies in place. If the attacker is allowed infinite attempts, it’s just a matter of time before they get in. It’s a good thing if your application goes offline when someone tries a bunch of incorrect passwords. A: you identify an attack in progress or B: you identify an idiot.

Use policy based management to manage a large number of servers. You can create a policy to prevent the ‘sa’ account from being enabled. Common attacks try very old methods, the idea is that you may know your security up and down, but you don’t know where all your servers are.

Use PBM to secure xp_cmdshell. Don’t allow sysadmins to execute windows commands. PBM can not only tell you a server is out of compliance but it can prevent enabling this feature in the first place.

Use PBM to prevent escalation to ‘sysadmin’ privileges. Don’t lock yourself out but you should prevent other new DBAs from handing out this privilege like candy.

Use least privilege methods for service accounts. When installing SQL Server, use a domain user account and don’t give it any privileges. The SQL Server install will automatically grant the permissions it needs.

Use complex passwords. If an attacker does dump the windows password hashes, a complex password will make it very inconvenient to crack. Use several character classes and more than 14 characters on passwords you don’t have to type frequently. Don’t use dictionary words or number/character substitutions for common words. (eg l33t h4ck0r)

Don’t grant other people sysadmin or local Windows admin on the SQL Server. Even good intentions can cause terribly configured vulnerable servers.

Consider creating outbound firewall rules. Does your SQL Server need the ability to browse web (80/443)? Does your SQL Server need to connect to other sql servers? (1433) Try removing the gateway from your network settings.

Use non-standard SQL Server ports. This can get painful but one of the first steps for attackers is scan the network for open ports. It can be more challenging to identify the software if it’s on a non-standard port. Just a suggestion, but you should pick a port that isn’t used by highly vulnerable software.

That should give you a few layers of security to chew on. At this point the most important thing to do is create a recovery plan. Just assume that the attackers will win someday. Create a plan to rebuild from the ground up and restore data. Use the old data to preform forensic analysis on so you can prevent future similar attacks and maybe even identify the attacker who causes us so many gray hairs.

1 Comment

Posted by on September 21, 2012 in Network Admin, Security, SQL Admin


SQL brute force POC (Alternate Ending)

I hope you read my post on ethical computer hacking…

Last time, the main character left off using a feature that is built into SQL Server ‘xp_cmdshell’. This was done using SSMS so it wasn’t really an “exploit” per-se. So for round two, my goal was to make sure to wow the audience and [SPOILER ALERT!] kill off all the main characters. Here is how it happens…

The brute force attack did get us connected to SQL Server with a sysadmin account. I’ll need this for the next step in our alternate ending. Instead of using ssms to run xp_cmdshell I’m going to attempt to use real exploits within the metasploit framework. This framework comes pre-loaded in backtrack. Launch it using the ‘msfconsole’ command.

The specific module is mssql_payload That site comes in quite handy when searching for a plausable exploit. There are so many pre-loaded payloads and exploits in msf that sometimes its harder to choose one than it is to use one.

Meterpreter is a quite popular method of dll injection. It will get your target machine doing things that you want it to do rather than the actions that it’s used to. By operating entirely in memory and over normal ports on a TLS connection, it avoids any classic virus scan, firewalls, network traffic scans and even post carnage forensics.

If you have ever seen scary movie, this is the point the attacker is in the house and the victim has run up the stairs to get away (doh). The flimsy door latch seems to be holding on for now so the attacker goes rifling around the rest of the house. I was actually quite shocked I was able to pop the meterpreter prompt on my windows 2012 vm. That got me wondering if we could use this account to dump any hashes (the encrypted password strings stored in Windows). Turns out I can’t, at least not today with my current skills and the rather outdated msfconsole I have. I am also unable to do several other nasty things. The one thing I can do is clear the windows event log with that ./sql account.

We’ve all been in a position where we might have to choose one of these options, security or productivity. The developer calls up complaining about some loading procedure that isn’t working and just wants sysadmin for a second to fix it (lol). So you mistakenly think you have done a great job by fixing the problem a different way, granting the sql service the ability to run as system. It is very realistic to find SQL Servers setup to run as different accounts other than those suggested by best practices. On the install, any amateur administrator might think it’s fine to run as one of the pre-populated NT Athority accounts. NT ATHORITY\SYSTEM perhaps might be a good choice. If it were my first install of SQL, I might fall into the same trap of not trying to be secure but trying to get something to work.

Right now you might be wondering, why is this hacker continuing to press on? Maybe at this point they create a administrator user account or drop a backdoor payload and call it a night. But this hacker wants to teach the owner of this box a lesson.

This is another terrible idea to have IIS installed on the same host as SQL Server. That makes it easy on attackers to compromise one service and easily gain access to the other. That said meterpreter does have some interesting features I will have to explore on linking exploited computers together via “pivoting” and the ADD ROUTE parameter.

Enough fun, time for the finishing move…

If you look closely at the commands that were run, you will see that the first attempt to dump the hash fails even when running as NT AUTHORITY\SYSTEM. But, fortunately SQL out of the box has this handy service that nobody uses set to NT AUTHORITY\SYSTEM. Even better, it has even more access because it uses VSS. One process migration later we grab the password hash and run. Back in the safe zone we take less than 1 second to crack the lazy administrators extremely weak password.

What you have seen in this post is not really a lesson on how to be a great hacker, but more of a lesson on how NOT to be a bonehead administrator.


Posted by on September 11, 2012 in Security, SQL Admin


SQL brute force POC

I’ve written about SQL Sever security and mentioned that logins can be brute forced. Today I’m going to attempt a brute force attack on the latest and greatest SQL Server.

First I downloaded VMWare player. Its free and allows you to setup virtual machines easily. I installed the 180 day eval of Windows Server 2012. You will need to tell VMWare player that its Windows Server 2008 R2 but I assure you its 2012, see no start button.

In the screenshot you will also see the SQL Database engine install completing. Today we’re going to act like a developer and make it as easy as possible to connect to sql server. Our first misconfiguration, enabling SQL Authentication is on the left.

The other bonehead configuration move you will see is enabling the ‘sa’ account with a weak password. And mistake number three is unchecking “enforce password policy”. That will allow unlimited attempts at the password. I see this configuration frequently because business users don’t want the application to go offline if the SQL Login gets locked out.

To setup the attacker machine, download a BackTrack VM, login with root|toor and run “startx”. Then you pop a command shell and verify there is a service availabile at my target IP using a tool called ‘nmap’. This tool has been around for ages an still works like a charm to identify service listeners and open ports. Neo used it in the movie The Matrix.

After that I launched a tool called ‘SQLDict’, entered the IP, entered the target account ‘sa’ and then loaded up a password file. The file I used is in the John the Ripper folder which is used for cracking passwords but can also be used for our SQL Brute force attack.

I hit start and as you can see in fairly quickly (maybe 2 minutes @ ~30/sec) it is able to try enough passwords to figure out that my sa account password is ‘highland’. Just to make sure I connected with that account in SSMS. One catch is you will have a SQL Log that gets a little bloated. But that is ok, you can cycle it out of existance since you have sysadmin privlidges.

There is another tool called ‘sqlhf’. It can scan a range of IPs and will make three tries at the sa password. Those three tries are ”, ‘sa’, ‘password’. Don’t ever leave the sa password blank or one of the other two options.

Once you are in, there may be nothing in SQL of interest. I know my instance doesn’t have squat to steal. However, there is one last configuration mistake that I didn’t make. And that is running the service as a ‘root’ a.k.a ‘NT Authority/System’. Since you have ‘sa’ you can enable xp_commandshell and rain shells right inside of sql server. Fortunately there is at least one speed bump, I’m only the local user “sql” which has very limited privileges.


Posted by on September 6, 2012 in Network Admin, Security, SQL Admin


Ethical Computer Hacking

Defining hacker is like trying to define American. There is a pretty good stereotype but in reality there is much more diversity than meets the eye. That said, here is my stereotype.

Computer Hacker: Person who either breaks down or builds up technology for specialized use. A hacker doesn’t play by the implied rules of software or services. Sometimes hackers have to gain access to a restricted area to be able to modify the technology the way they see best fit.

The idea of Ethical Computer Hacking is fundamentally flawed. At their very nature, hackers don’t play by the rules which makes calling the activity ethical a challenge. However, the word ethical is up for interpretation. There are entire classes on ethics so it is not a topic that can be taken lightly. Ethical hacking is a lot of unethical behaviors who’s final product is used only for good or ethical purposes.

Take social engineering for example. In the movie Takedown(2000), Kevin Mitnick calls an engineer pretending to be someone else. The goal was to get information about a computer system in that employee’s company. Several lies later the person finds Kevin generally likable and decides to break the rules and hand over information they should not. That particular attack would not be possible if it were moral. The fact that the hacker has to lie and deceive makes it unethical. Had this been a test to make the engineer’s company aware of their weakest entry point, the final product would be for the greater good. Social engineering is particularly hard to include in ethical hacking because the victim, regardless of the test, is usually left feeling violated.

Penetration testing or pentesting is a form of ethical hacking. Since it is just a test, this activity can be considered moral. This activity can go very wrong if a few rules are not followed.
1. Pentesters must first describe the details of their test to their victim and get written consent to perform their test from an authority figure
2. Pentesters have to be familiar with the tools they are using. They have to understand the source and any potential side effects. Lots of available exploits overwrite memory on the victims computer which could cause data loss or a service outage. Also, these tools can be embedded with code that uploads the vulnerabilities to a Black Hat.
3. Pentesters have to release all of their results from the test but only to the predetermined parties
4. Pentesters must release the results in a professional fashion. It is only natural to LOL at the utter disregard for security but keep in mind real people are responsible for this security and they have feelings. Hurting feelings for the greater good may be ethical but is unnecessary.

That last point is up for debate. Sometimes security has been taken with such disregard that the only thing that will get through to them is a public shaming. It is very temping to take the results of a pentest and want to fire those responsible for the lackluster security. That would be the wrong thing to do. That would be incredibly naive.

Pentesters often go for speed. That by its very nature leaves many things up to chance. It is important to remember that the hackers are always ahead of the defenders. There are always security holes and all of those responsible for the security of systems are making mistakes. It is good to preform a thorough examination before jumping to any conclusions.

To the Point

Want to be good at computer security? Patch your computers. Want to be really good at computer security? Learn how to hack.

I recently played a capture the flag game. We were given two virtual machines, one attacker and one victim. The goal was to answer questions about the victim computer by hacking into it. I am looking forward to sharing this experience because it was a seriously eye opening challenge. It was probably the most fun I have had learning in a long time.

Special thanks to:


Posted by on September 3, 2012 in Network Admin, Security