RSS

#SQLSatDet has made the front page

The short list of upcoming events now includes SQL Saturday #292 in Detroit http://www.sqlsaturday.com/.

Free training, free networking and only $10 for lunch. Best you cancel your plans for May 17 and find your way to Lawrence Technological University.

The speakers who submitted by the original deadline have been confirmed for at least one session. That means you will have a chance to listen to me talk about SQL Server Security in my Hacking SQL Server session. I really enjoyed speaking last year at this event and look forward to this years event including all the pre and post activities.

Here is my recap from last year: http://nujakcities.wordpress.com/2013/03/20/sqlpass-sqlsatdetroit-recap/

 
Leave a comment

Posted by on April 10, 2014 in PASS, Security, SQL Admin

 

Tags:

Toying with In-Memory OLTP

In six days the bits for SQL 2014 RTM will be available for download. I decided to fling myself into its hot new feature of In-Memory OLTP with the CTP2 release. I’ve attended one user group that gave an overview of the feature set ( Thanks @brian78 ) but other than that I have not read much technical information about In-Memory OLTP.

One advantage point that seems to pop up in literature surrounding the release is the ease of implementation. Not all tables in a database have to be In-Memory and a single query can seamlessly access both classic disk based tables and In-Memory tables. Since the product isn’t released yet, the information available on the featureset is heavily weighted towards sales. I wanted to see if achieving the 5x-20x performance boost was really as easy as it sounds. Instead of my usual approach of collecting lots of information and reading tutorials, I decided to blaze my own trail.

The first thing to do is create a new database. I noticed a setting that I heard referenced in the overview called delayed durability.

delayed_durability

Scripting the new database out in T-SQL also shows this new setting. I’m assuming this will make things faster since they don’t have to be persisted to disk right away.

delayed_durability_script

Before I run that script I decide to poke around a bit more. I see some In-Memory settings over on filestream. I’m not sure if that is a necessary requirement or not, but I am going to add a filegroup and file just in case.

filestream

file_stream_script

Now that the database is created I want to create a table. There is a special option in the Script-to menu for In-Memory optimized tables. I’ll create a few dummy columns and try to run it.

first_error

There seems to be a problem with my varchar column. “Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables.” Well that is unfortunate, I suppose I will change the collation in this test but that won’t be easy in real life.

collation

After changing the collation I am able to create my memory optimized table.

in_memory_test2_success

I wondered if there would be any way to tell in my query plan if I’m actually optimized. It doesn’t appear so…

index_seek

Was that a 5x performance boost?? I’m I doing it right?? Not sure, but for now I need to take a break.

I’m hoping ISVs start supporting this feature but it might be a lot more work than advertised. After getting that error I found a list of many things that are not support on these tables and in the compiled stored procedures. http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx

This list does not encourage me to blaze new trails and start testing this as soon as it comes out. I prefer to wait a bit and let the other trail blazers blog about the issues they have.

 
Leave a comment

Posted by on March 26, 2014 in SQL Admin

 

Arp Spoofing with arpspoof

Consider someone has hijacked your DNS server. That person modifies the record for “prod_database.domain.com” to point to their IP address. At no additional charge, after capturing all the packets, they will kindly forward them on the the real prod_database. That would be a layer 7 to layer 3 link switcheroo.

Arp spoofing is a similar concept but instead of names to IPs, we modify the IP(layer 3) to MAC(layer 2) relationship. To demonstrate a successful spoof, I have to tell another client on my LAN that I am the gateway, and tell the gateway that I am that client.

In order to see the damage of arp spoofing you can look at your arp table while being spoofed. Type “arp -a” at a windows command prompt in order to view the contents of your arp cache. Arp spoofing is also called arp poisoning because of the false records that the tool is able to get added to a victim’s arp cache.

Using Kali Linux as the attacker, a fresh trial of Windows 2012 R2 as the victim, VMware Player, and the command line tool arpspoof I was able to successfully capture the victim’s traffic. For the traffic to flow through Kali, the first step is to turn on port forwarding. Then in step 2 and 3 we tell the subnet some lies.

arp_spoof_01

The poising has started. If you want to see this traffic you can use the “arp” filter in Wireshark.

arp_spoof_02

Finally, to offer some proof, I browse to Wikipedia on the victim’s machine and view the traffic on the attacker’s machine.

arp_spoof_03

The defenses to this attack include SSL/TLS, OS hardening and duplicate MAC detection among other things. Unfortunately this is how some proxy like tools work and you might not be able to use all of those methods to stop the attack.

Reference: http://www.irongeek.com/i.php?page=security/arpspoof

 
Leave a comment

Posted by on March 10, 2014 in Network Admin, Security

 

Comparing Server Processors

To pick the best processor for your server, a cursory understanding of how the application works is helpful. In virtulaized environments, that gets interesting because multiple applications run together on the same socket. Generalized compute clusters have their benefits, but some applications have “special needs.” Minimum support requirements could throw a wrench into your choice.

Single Threaded Applications

Multi-threaded code is challenging to write. That is why, even 10 years after multi-core processors became main stream, developers still write single threaded applications that require performance. If you are interested in the code side of this discussion, I wrote a simple post comparing single and multi threading http://nujakcities.wordpress.com/2010/11/16/single-vs-multi-threading/

If you have ever added a core to a VM and watched a process that used to run at 100% CPU on one core, now run at 50% CPU on two cores, you probably have a single threaded application. A multi-threaded process would be able to utilize all cores to 100%. Single threaded applications that cannot be upgraded to utilize multiple threads will benefit from higher clock speeds and not more cores.

Launch Date

This is the first thing I look at after I’ve flushed out any special requirements. I take the processor number from the server quote and find it on the manufacturer’s website. That way I can find out if I am looking at the latest processors. Since Moore’s law is still in play, I can be somewhat comfortable that the newer processor is going to be a lot faster in some way shape or form. If you are not comfortable jumping on the latest technology, you will also save quite a bit of money. Don’t let your hardware vendor talk about new processor features and slip an old one in the quote.

Clock Speed and Core Count

These are the two main factors. They are in the same heading because of the heat balancing act. Faster is hotter, and more cores is also hotter. We unfortunately can’t have the best of both worlds or the box will catch on fire.

If we compare two of the Q’14 high end Intel CPUs, we find the balance was found at 3.4 Ghz/6 cores and 2.8 Ghz/15cores. As a virtualization admin I see an obvious choice here going with 15 physical cores because 99% of my applications would be happier. Even if they are a single threaded applications, the additional .6GHz will be barely noticeable.

Knowing your application can come in handy when designing clusters and buying hardware. Enterprise code can sometimes be a decade old so be cautious of purchasing max cores over high GHz. You may run into more single threaded, latency sensitive application that you want to admit still exist.

If you have a need to run higher core count VMs, a higher number of physical cores per socket is best. I have a general rule of thumb to avoid creating VMs that have more vCPUs than pCores on a single socket. Even if it is a dual or quad socket server, you will see diminishing returns and sometimes problematic ready times if you are spanning a VM across several physical sockets.

Hyperthreading and Turbo

I remember owning a beige PC that had a nice button and red LED display. Press it once and the display would read 33 press it again and it would read 66. Even today, processors have an automatic turbo button however there are some caveats. Turbo won’t work if all of your cores are in high demand. If only a single core is in high demand and the others are idle, then Intel’s “Max Turbo Frequency” value kicks in.

Hyper threading sounds like a great idea but doesn’t actually perform as advertised. I like to refer to these as fake cores. With it on, your OS will see twice the cores. Sometimes they can help but I can guarantee it won’t give you a 2x performance improvement. It really depends, but I have heard ballpark estimates in the range of 25-50% performance improvement by turning on hyper-threading. So I do recommend turning this feature on, but be careful not to mix up physical core count with the hyperthreaded core count.

Cache sizes

About the only thing I understand in this field is cache is good. If some is good, more is probably better. In this area, server CPUs tend to blow Desktop CPUs out of the water. I’ll have to do some more research and testing to figure out if this value should be of great concern which buying processors.

Price

With software like SQL Server licensing by physical core, more than just the hardware costs need to be reviewed. If you are paying by the core or socket for premium software, it makes a lot of sense (and cents) to buy the best processors you can find. If you can increase the consolidation rate, you won’t need so many software licenses.

That is a big IF though. Make sure you are actually getting solid returns on high end processors. Some benchmarks have shown diminishing returns as manufacturers push the limitations. Lay it all on the table, even smaller or abstract costs like reliability, power consumption and productivity before making your decision. Most of all, have fun shopping!

 
Leave a comment

Posted by on February 24, 2014 in Hardware

 

Guest Memory Dump From the Hypervisor

Part of VMware’s VMotion process copies all the guest system’s memory from one physical host to another over the network. Snapshots and VM Suspends will force a memory checkpoint making sure there is a persisted full copy of memory on disk. The point here is that the hypervisor is very much aware of the guest’s memory.

Without the hypervisor there are a few ways to capture data in RAM needed for some serious debugging. A single process is easy, just fire up the proper bitness of task manager.

process_dump

If the Windows computer is actually crashing, you can have it automatically create a dump file. One requirement is enough space for the page file. http://blogs.technet.com/b/askcore/archive/2012/09/12/windows-8-and-windows-server-2012-automatic-memory-dump.aspx

If the problem you are trying to debug doesn’t crash your computer, you have a little more reading to do. https://support.microsoft.com/kb/969028 There are several tools including a registry entry for CTRL+Scroll and a PS utility who’s name I love: NotMyFault.exe

But wait! It gets better!


The hypervisor checkpoint process. Just hit the pause button on your VM and viola. Browse the datastore and download the .vmss file. VMware has kindly written a Windows version of it’s application to handle the conversion https://labs.vmware.com/flings/vmss2core To convert this .vmss file to a windbg memory dump file just run this command

vmss2core.exe -W C:\pathtodmp\vm_suspend.vmss

You can also perform this same process using a snapshot instead. This can be an even better option to avoid downtime if your guest is still mostly working.

Now What?


Well, this is the point where I call in the experts. I generally do this to ship the file off for analysis by the developers of suspect code. As a teaser to some future posts, here are the ingredients we are going to have to collect:

The file we created is consumable by WinDBG http://msdn.microsoft.com/en-us/windows/hardware/hh852365.aspx

http://support.microsoft.com/kb/311503: Symbols help map out the functions

Commands for analysis in Windbg: http://msdn.microsoft.com/en-us/library/windows/hardware/ff564043(v=vs.85).aspx

 
Leave a comment

Posted by on February 7, 2014 in Virtual

 

Tags: ,

Wireshark Skills Series 7 of 7

I had no idea what protocols I was going to cover when I picked the number of posts in this series. I’m glad I picked 7 or I would be at this for months. I find that when I look at captures I can turn vague concepts of how stuff works into a more thorough understanding of how stuff works.

As a speaker it can be a very awkward feeling walking off a cliff. The cliff is starting to explain something, and then not being able to finish the thought because you don’t actually know how it works. Now with wireshark skills, I can dive deep on purpose and avoid falling.

Kerberos

Kerberos is a good capture to wrap up this series. Kerberos is an authentication protocol. It is a complex protocol but I have found a great pdf that simplifies it… but not too much.

http://www.computerworld.com/computerworld/records/images/pdf/kerberos_chart.pdf

With all of those dotted lines we should be able to find something of interest in wireshark. I have a domain controller and another server setup that we’ll call a client. After setting up the domain, joining the client and creating a couple users, lots of kerberos is already happening but we are going to try to focus on those first few steps in the PDF.

The domain controller handles both the authentication service and ticket granting service. If we start a trace and add the filter “kerberos” we will get to see some traffic eventually.

First, to make it a clean run, at a command prompt type “klist”. This shows you the current tickets you have. Then type “klist purge” which will get rid of those tickets. If we have that capture started and lock our session (ctrl+alt+del lock) and re-login we will capture the first step AS-REQ. The second packet is selected in this screenshot and shows the details of the AS-REP. The “Client Name” and “Clients Realm” are self explanatory but the server name of KRBTGT may be a little misleading. There isn’t actually a server named that but it represents the place the client goes for the Ticket Granting Ticket.

Step 1 in the PDF is completed when we log in.

AS-REP_krbtgt

The next Kerberos action I see is a TGS-REQ and TGS-REP. After authentication we log onto the host(a.k.a. client) which gives us a host ticket.

TGS-REP_hostticket

This is all I will get unless I try to access network resources. Below I attempt to access a cifs share on my virtual host computer. Since it’s not joined to the same domain the authentication isn’t automatic. A popup window asks for credentials to connect to the computer “nujak” and I type in some junk. The domain controller correctly responds with “KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN”.

TGS-REQ_unknown

In these next packets I attempt to access a share on the domain contorller. Since I do have access to this share we get a successful SMB2 session start. If you dig into the security blob in the packet you can see the basic kerberos information.

SMB_KRB_sessionstart

Wrap-Up

The distributed platform forces computers to work together. If you want to have a deep understanding of why they do or don’t work together, Wireshark is the tool for the job.

 
Leave a comment

Posted by on January 21, 2014 in Network Admin

 

Tags:

Wireshark Skills Series 6 of 7

I’ve yet to hop into Wireshark with a database connection problem and figured it out because of the network capture. It is not the first place I stop. It has never come to that, I’ve never needed to go that deep. I have always used the other, more topical tools to diagnose a connection problem.

I have had an occasion where I wanted to go deeper. I wanted to find out for sure if the login information in a SQL connection was encrypted if you didn’t specifically setup TLS. The answer was it depends.. on the client. Most all implementations are encrypted. I happened to come across an old implementation that was not. It was a good answer to find out for sure and going deep with a network trace was the only way.

Today I set out to look at some packet captures of MSSQL connections and see if anything interesting showed itself. Turns out I found something very interesting. Have you ever connected to an instance of SQL server with SSMS and had it take for ever to load the list of folders? Lets take a look at a normal start to a SQL connection. I filtered out the IP and default SQL port.

normal_sql_01

This all happens in short order. In about 3 milliseconds, the client and server have completed their TCP handshake and negotiated on the TDS protocol in order to send the secured SQL username and login.

Now we have the jankity trace. The connection I attempted directly after installing SQL was much slower.

slow_sql_02

And no wonder it was slower. Look at all those re-transmissions. If an ACK is not received by the client affirming the packet was received and undamaged, the client will send the packet again.

In this particular case, I was reminded that the default amount of memory for a Hyper-V VM is 512MB. This was not quite enough to run through an install and have all the things open that I did. I shut the VM down added some RAM and my connections were running much more smoothly after that.

 
Leave a comment

Posted by on January 5, 2014 in Network Admin

 

Tags: ,

Wireshark Skills Series 5 of 7

Hopefully by now you have drank the proverbial kool aid and think Wireshark is the best tool since… ever. Yes, it is awesome but there are some security concerns. If a hacker compromises your server as a very under privileged user, it may benefit them to already have a packet capture utility installed so they can just run it instead of being denied to install it. You can also use it to discover other computers on the network and start making some dangerous assumptions.

One of the neato tricks to do is take a lengthy capture, then do a string search for your password. If your password is going over the wire, I would recommend dropping whatever service it is that you are using that would send your password over the wire, unencrypted.

Standard FTP is one of those services that will send plain text passwords. I have downloaded a .vhd for server 2012 R2 and installed the IIS + ftp role and feature.

ftp_misconfig

In the previous captures we haven’t limited the capture itself and instead we are filtering post capture. Instead of a post filter, lets apply a pre filter. Open up the capture options and filter on the standard FTP port of 21.

ftp_capture_filter

To test out the security of our authentication choice, open up the simple, built-in Windows FTP utility. Type at the command prompt: ftp [Enter] open 10.10.10.108 or whatever your practice server is and pause a second to take a look at the capture. Nothing too crazy here, just a 3 way TCP handshake and then the FTP server responding with code 220 meaning it is “ready for new user.”

start_of_ftp

I chose Basic authentication and allowed all users to connect with no SSL. That means if I enter a Windows username and password I should be able to log in.

Found_Standard_ftp_password

The login worked great! But I have also allowed my username and password to be compromised by someone capturing packets on the server.

 
Leave a comment

Posted by on December 17, 2013 in Network Admin

 

Tags:

Wireshark Skills Series 4 of 7

In some of our HTTP requests there was something interesting going on that didn’t make the cut to be explained. The magic of Ethernet was fast at work furiously chopping up data at one end of the pipe and reassembling it at the other. Packets can be all different sizes but if they exceed a limit they will get sliced and diced.

Increasing the size of the packet reduces the amount of overhead needed to transfer your data. Both ends of the pipe and everything inbetween have to support the packet size that is being sent. If a switch or router doesn’t support it the connection may fail. If it is supported but not properly configured you could run into a silent problem where that intermediate device is fragmenting the packets. It is best to change jumbo packets at the intermediate switches and communications devices first, then at the endpoints. Also, some applications like Microsoft SQL Server need configured to send larger packets too.

To illustrate this process we are going to use the ping tool. Ping runs in ICMP which is on top of IP and Ethernet. Ethernet is the layer where the MTU setting resides. Before we change the MTU lets take a look at a large ping getting sliced up like the HTTP traffic we saw earlier. This command will continuously ping with 10,000 bytes of data. For this test I am running Wireshark on a Windows 2012 R2 server. (VHD 180 day Trial: http://technet.microsoft.com/en-US/evalcenter/dn205286.aspx)

ping 10.10.10.108 -l 10000 -t

Segmented_default

If you squint really hard you can see 7 packets coming into the server and 7 packets going out of the server. Ethernet + IP require 36 bytes per packet so the overhead can add up quickly. If you look at the length, you will see no packet is over 1514 bytes in size.

Now we can try to make this more efficient. I’ll have to configure the vswitch and the virtual machine NIC to enable jumbo packets.

configure_jumbo_1

configure_jumbo_2

configure_jumbo_3

Now if we re-try that ping we should notice fewer packets.

jumbo_packet_confirmed

Success right? Well yes in this case we made the process 340 bytes more efficient. However, since I enabled jumbo packets I broke other things. Most notably my blogging software :]

 
Leave a comment

Posted by on December 16, 2013 in Network Admin

 

Wireshark Skills Series 3 of 7

In my previous post on HTTP I used a tool called nslookup. It helps debug issues with DNS servers. Today I am going to inspect a few DNS requests in Wireshark.

Before we hit start, lets cover a few basics. DNS lives in layer 7 and operates on IP and UDP (http://en.wikipedia.org/wiki/List_of_network_protocols_(OSI_model)). DNS is heavily cached so not all name lookups will end up in Wireshark. Hostnames are an example of private names. Today we are going to focus on public names that are served by the public internet.

In order to make for a good test, I am going to clear my DNS cache. At the command prompt type “ipconfig /flushdns”. If using chrome click the clear host cache button ( chrome://net-internals/#dns ). Next type ipconfig /all and take note of the primary dns server name.

Now we can start the capture. Apply the “dns” filter and visit this site http://www.laketrust.com. Notice the address bar change. Not only did we get redirected to https, we also got redirected to .org instead of .com. This is a slightly more interesting site than normal and it’s why I chose it. Lets take a look at that in Wireshark.

dns_response

DNS queries are quick, small and efficient. The first response is 107 Bytes and has two answers. The first answer is just a CNAME or pointer to the real HOST A record “laketrust.com”. The CNAME won’t ever have an IP but does have a Time to live of 1 hour which is much greater than the HOST A record. TTL instructs the client how long to cache the record before having to query for it again. Also, there doesn’t have to be an answer for no “www” there just usually is.

There isn’t anything in that first DNS query that directs us to .org. Based on the packet numbers, there were 7 things that happened that we have filtered out. Clear the filter to take a look at the missing packets. When you find that DNS response you will see what happens next.

301Redirect

The DNS query gave us an IP. Now we can do stuff :] In this next segment of the capture you see a TCP 3-way handshake. Once that is complete my browser makes a HTTP GET request. The HTTP 301 response redirects us and not DNS. Then we start this whole process again with another DNS query.

Performance

DNS is for convenience. Ain’t nobody got time to memorize all the IPs of websites they want to visit. In the example, we had to wait for 2 painful round trips just for dns in order to actually get the “doing stuff” point. Fortunately, this is only required for the first lookup and the rest is sent to the DNS resolver cache until the TTL expires. Had the first CNAME actually pointed to the host we need we could have saved some time.

Also in the example, you’ll notice that I haven’t changed any of my settings in my router or PC in regards to DNS. They are set to automatic, which means I went all the way to my ISP to figure out what DNS server to use and naturally they suggest themselves. Instead of Comcast, Google has another option and they claim it can be faster. Lets investigate by changing your DNS in your router, or just for your PC. Verify the settings took with ipconfig /all. With this configuration I’ll only have to use Comcast if both Google servers are down. The order is important because the first one you enter is your primary.

DNS Servers . . . . . . . . . . . : 8.8.8.8
                                    8.8.4.4
                                    75.75.76.76

If we test the same scenario, you can see in the capture that the Source of my dns responses are now Google. And, in at least very casual testing, the Google servers are faster this time.

GoogleDnsTimes

Because of physics, the location of this server you are contacting is very important. How far is a millisecond? Well that is a curious question which could be answered by the speed of light which is 186 miles. Multiply that by the pieces of equipment in between you and that server and you will have a vague estimate of how long your DNS queries will take in a no contention, best case scenario.

Before you settle on a different DNS server make sure to test everything. Especially services with a content delivery network that Google might not be privy to (https://developers.google.com/speed/public-dns/faq#cdn)

Conclusion

The OSI model doesn’t do DNS justice. So many things depend on it’s operation that it is best to envision it lower in your stack. Understanding and tuning at the lower levels can cause exponential benefits in user experience.

 
Leave a comment

Posted by on December 15, 2013 in Network Admin

 
 
Follow

Get every new post delivered to your Inbox.

Join 146 other followers