Monthly Archives: August 2011

SQLSaturday #84 Kalamazoo, MI

I am excited about this, my first SQL Saturday event. I decided to volunteer since I am familiar with the Kalamazoo area. The one task I have been assigned so far is to get the word out. The word is this will be an awesome event. Thanks to Mr. SQLAgentman we have secured a great linup of speakers They will no doubt be able to educate even the most educated. Students with no database experience to seasoned IT vets will be able to choose from the menu of sessions.

I did attend SQL Rally which was a PASS event targeted for people looking for something inbetween the Free SQLSat events and the expensive week at the PASS Summit. One of the unexpected bonuses of any of these events is the people you meet.

KVCC and the AITP leaders and members have been very hospitable and invited us to use their facility. I have not been there but their website makes it look pretty sharp

Kalamazoo is home to the campus of Western Michigan University with about 30,000 students. Come September, Fall session will be in progress so expect the nightlife to be a college age crowd, especially after 10pm. Contact me on twitter @nujakcities if you are interested in checking out what Kalamazoo has to offer.

If you want to make something of yourself as a database professional you need to get involved and learn at events like this. Don’t miss out.

Leave a comment

Posted by on August 19, 2011 in PASS


tempdb log is full

For those of you who have gotten here with a real issue, here is the fix. If you run this, your server will probably start to cook until the bad tempdb eating query is finished.

USE [master]
ALTER DATABASE [tempdb] ADD LOG FILE ( NAME = N'templog2', FILENAME = N'C:dbtemplog2.ldf' , SIZE = 131072KB , FILEGROWTH = 131072KB )

Now, we can get into the why part. First, a little about tempdb. This system database is used by sql server for many behind the scenes processes. It is truncated every time sql starts. Its proper configuration is vitally important to performance. tempdb is in the simple recovery model and cannot be changed. If your server crashed you would not restore tempdb so there isn’t a need to back it up.

Simple mode databases overwrite previous log entries once a checkpoint occurs. Linchi Shea points out that huge long running transactions are more likely to fill up a simple log but it is possible to do it with fast enough small transactions.

When I ran into this problem, I saw a server that had a bunch of open connections that were waiting on something to actually do some work. No CPU or Disk was active at the time. The SSMS GUI was working slightly but no agent was showing and I would get errors when I tried to open certain features. The one thing I could do is run queries. The problem was shown when I first connected to the server. Now I just needed to fix it and find out why it happened, maybe not in that order.

The first few things that poped up on google are not good solutions or even solutions at all.

1. Backup tempdb log – nope can’t do this because its in simple mode.
2. Backup tempdb log with truncate_only – nope again
3. Shrink the log – bad idea, beside there is no free space…
4. restart sql

Technically, 4 would probably work. However, its a bad idea because you then hinder your troubleshooting abilities. There was probably one bad query or report that once it fails, the user will just try again. If you were in the middle of something, when your server comes back online it might be stuck “in recovery…” Before you grow the log you should try to fire off sp_whoisactive That will show you active sessions, the login, the query and their tempdb activity, all the ammunition you need to stop this problem dead in its tracks.

Leave a comment

Posted by on August 16, 2011 in SQL Admin, SQL Dev


Setting up Microsoft Certification Authorities

More of Microsoft’s great products are moving towards using certificates for general security and authentication. If you don’t have an internal Certificate Authority with all the bells and whistles you should start that project as soon as possible. Move away from self-signed certs and manually using OpenSSL to create a CA. Hop on the MS bandwagon because its a good one to be on.

For websites to secure traffic using SSL/TLS the web server needs to have a key pair installed, one public and one private. IIS7 has the option of generating self signed certificates which is very convenient. Self-Signed indicates the same machine or person who is using the certificate also issued it. So if someone walked up to you and said, “Hi, I’m Bob” you might take a look at their appearance and accept this as a fact. Just like a website, if it looks ok at a glance you will entrust it with your information. Browsers today are much more paranoid. If a webserver uses a self signed cert, they will warn you and beg you not to hand over your information.

Within your organization you can give Bob a friend that will verify his identity. So when Jim says, “Yup, thats Bob”, you can be relatively certain its really Bob. What you have now is a hierarchy. In the public internet there is a short list of “Trusted Root Certificate Athorities”. These are the Jim’s of the world that have been identified as trustworthy. Being a Jim is a lot of work so they delegate their work to an intermediate certificate athority. Chrome and IE will trust Jim’s delagates because they have that list built in, however Firefox is the most paranoid and does not have the list of intermediates.

To setup webserver TLS properly you need to have a a chain installed. If you install just the bottom cert, Firefox users will get errors. All of these certificates need to be trusted by all of your users. If your users are internal to your organization, Group Policy can be used to force the Root and Intermediate certificates into their certificate stores. To look at your local certificate store, run the certmgr.msc snapin. To check a webserver, you can use OpenSSL and the -showcerts option.

If you don’t want to purchase Jim’s(godaddy, entrust, verisign, etc…) services, and you don’t have any public users to your application you can setup your own CA. There is an excellent checklist on TechNet here:

The first thing to work on is the Root. This is the very top level CA and should be the most secure server. Not so secure that you lock yourself out because you may need to get in and revoke certificates. What Microsoft recommends is having an offline root CA. So if you install 2008 R2, patch it, set secure local passwords and then unplug the NIC. Now you can install the Role for Active Directory Certificate Services. This creates the private key and is the thing you need to protect most out of anything in the whole scheme. If someone asks you for your private key they do not know what they are talking about, or are trying to steal something. Using the Web interface makes things a bit easier so IIS7 would also be installed.

My advice is to be willing and ready to scrap your solution and start over from scratch. This process has a bunch of gotachas. It very much reminds me of a “some assembly required” desk I put together that I had to take apart multiple times to finally get it right. Its so easy to miss a step that you won’t find out about until months later. I missed a couple steps myself so hopefully I can help you out.

What you will setup is the Root and Intermediate servers. Pick good names for the servers that are not very close to each other CAOfflineRoot and CAInt might be good names. Setting up the root is easy, then you have the challenge of setting up the intermediate. Create a signing request, send that to the root, get the cert back and install it as the intermediate CA certificate. This cert signing is similar to what you do when you install a cert in a web server.

To reduce the administrative overhead you can extend life of certs your CA issues. In 2003 this is a registry entry, and in 2008 I believe its in the properties. The certs you have already issued are now fairly useless because they will expire too soon. This is the first gotcha because you will then have a list of issued certs on your offline root that is growing. You should revoke certs you are not using/replaced… or start over.

Also, another suggestion for the offline root only, increase the length of your CRL publish. By default CRLs expire once a week but you don’t want to manually publish your root CRL every week, so if you raced ahead… fix that and start over.

To use the web interface in 2008, you will need to setup SSL for the certserv website. This isn’t a requirement in 2003 but is recommended. If you used the offline root to sign the web cert… you may want to start over.

The last gotcha I ran into the with the CRL and AiA. The Certificate Revocation List is something every CA has to publish. Its a file that is hosted via IIS or the CertEnroll share. The problem with setting up an offline root CA is you have to publish the CRL manually. Removable media is the suggested route. This gotcha didn’t bite until Exchange2010. Before Exchange, the web certs I installed didn’t verify the entire chains CRLs. I had to renew the intermediate CA because I had to make changes in the root CA’s settings. These settings are in the Certificate Athority Snapin -> Root -> Properties -> Extensions. By default the CA will issue the certs and tells viewers to look on the local hostname. You have to replace the hostname variable with the intermediate server hostname. Then you have to restart the root CA, and manually publish the .crl to active directory specifying the name of the Root. This is the gotcha that made be decide to blog.

Now that everything is working perfectly, you need to modify group policy so that all your network computers trust your root and intermediate. Go to the intermediate CA website and download the chain. Don’t publish anything until you’ve cleared up all the gotchas. Happy securing!

Leave a comment

Posted by on August 14, 2011 in Network Admin


Use VB.NET to impersonate a domain user

There are a lot of reasons you may want to pretend to be something else. Maybe you have low self esteem and need a boost. To impersonate someone or something you need a good understanding of what that thing is all about. In Windows all you need is a username and password. If you want to connect to a database with higher privlidges you can impersonate another user by right clicking SSMS and choosing “Run As”.

Web applications can impersonate other accounts by using the application pool identities. Or web applications can impersonate the connecting user by setting the identity impersonate to true in the web config.

Services such as SQL Server can run as local system or run as any domain user with the right (or wrong amount) privliges. Have you ever had a developer contact you with the error “Access denied for “DOMAINCOMPUTERNAME$”. This is because they are trying to run a program as local system on that computer and connecting to SQL on another server. You can put these AD computer accounts in a group and give the group a SQL login but I wouldn’t recommend that. What I would recommend is to use .NET to impersonate a user when accessing SQL Server.

Supply a valid username and password to AD and authentication produces something called a token. This token can be used and re-used to get at the objects you need. MSDN has some very elaborate code samples that dive very deeply into the proper methods to use the framework when it comes to impersonation. Most of these instructions were quite far over my head and way deeper down the rabbit hole than I had intended to travel. My goal was to impersonate an account that actually had very limited privlidges so I did not need all the extra tedious and bland material.

Hopefully this code sample below from MSDN that I trimmed down considerably will satisfy your taste buds.

Imports System.Runtime.InteropServices
Imports System.Security.Principal

Module Module1

    Private Declare Auto Function LogonUser Lib "advapi32.dll" (ByVal un As String, ByVal domain As String, ByVal pw As String, ByVal LogonType As Integer, ByVal LogonProvider As Integer,  ByRef Token As IntPtr) As Boolean

    Public Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Boolean

    Public Sub Main()
        Dim tokenHandle As New IntPtr(0)
            If LogonUser("un", "DOMAINNAME", "pw", 2, 0, tokenHandle) Then
                Dim newId As New WindowsIdentity(tokenHandle)
                Using impersonatedUser As WindowsImpersonationContext = newId.Impersonate()
                    'perform impersonated commands
                    System.IO.File.WriteAllText("C:ttestimp.txt", "test")
                End Using
                'logon failed
            End If
        Catch ex As Exception
        End Try
    End Sub
End Module

Posted by on August 1, 2011 in .NET, Network Admin