Ben Miller’s World

Being that I have never missed a PASS Summit yet (in the US), I thought I would share my experience about last PASS and previous PASS conferences.

I attended a Pre-Con with Kalen Delaney on Query Tuning and Plan Guides. I also remember sessions last PASS that were very relevant to my job. Each year that I attend PASS, I find that there is more to know and there is more to learn from many that are there to present.

I was pleased that when I got home each time, I applied my learning directly to my job. I remembered the Plan Guides day, and ironically while troubleshooting some performance issues with a database, found that I could leverage plan guides for a specific query, and it worked as Kalen illustrated. It actually saved many queries from bloating the plan cache, and was pretty helpful. But I had not done anything at that time with Plan Guides, so the topic and the time spent was very timely.

The other topics included Powershell and Wait Stats and Wait Types. The more practical sessions are very helpful and very applicable to my job as a DBA and manager mentoring DBAs. I can only say that I still plan attending all the SQL PASS Summits in the US. One day I hope to attend one outside of the US, but for now, the value of these Summits in networking, vendor introductions and all out knowledge transfer is worth every minute spent for me.

I went to the SQL PASS 2007 Summit this past week, and I have to say that this was one of the best ones I have been to. I went to a pre-conference session with Kalen Delaney on Managing and the Reuse of Query Plans. Everything that I learned, I could use in my current work. That was very important for me as a new DBA in a project that needed this information.

Thanks to all speakers who took the time to put together presentations for us to learn from.

I recently came across this while managing a clustered SQL Server. There were various messages listed out on forums, etc., but for me we found that the error was originating because we have DNS issues and Domain Controllers (DC) was unreachable due to a network switch configuration problem.

So if you see these errors, you may see in the EventLog a NetLogon error relating to not being able to reach a domain controller to login, and you will get the error below because the DC is the one that hands the Kerberos token to the process for use in SSPI related activities.

Logon,Unknown,Error: 17806 Severity: 20 State: 2.
Logon,Unknown,Login failed for user ”. The user is not associated with a trusted SQL Server connection. [CLIENT: IPAddress]
Logon,Unknown,Error: 18452 Severity: 14 State: 1.
Logon,Unknown,SSPI handshake failed with error code 0×80090311 while establishing a connection with integrated security; the connection has been closed.

Good luck and have a great SQL day.

I thought I would document my exploits in SQL Server SSIS 2005 conversion of DTS packages and the scheduling.

I had a DTS package that used the ProtectionLevel “Encrypt Sensitive Data with User Key” that I did not design or create. When I migrated them from SQL 2000 to SQL 2005, I used BI studio to bring them to the file system and then modify them in SSIS and put them back into SQL 2005 into a SSIS Store.

What I did not realize is that when you have the Encrypt with User Key, in 2005 the only option to run an SSIS package is to run it as SQL Server Agent, which in our case is running as Local System. Now the User Key would not be the same as the one that created it, nor would it have Admin rights (per se) to be part of Administrators Group to run this package.

I went about looking for the solution and found a KB article (918760) that indicates that I can put it in a SQL Server storage protection level and then it would use SQL Server roles to get the data out of encryption.

I promptly went into the BI Studio and opened the package and tried to change the protection level to SQL Server Storage and it would not let me save the package because it could not verify that it was secure. But there is no way to put it into SQL Server Storage to use that Protection Level. So I had to change it to Encrypt SensitiveInfo with Password.

Here is the fun part.

I went into BI studio and Added a Package from SSIS store (because the packages were already in there) and then modified the option and then saved it. What I did not know was that it saved it to the file system and did not save it back to the SSIS Store, so the package still failed to run under SQL Agent.

I figured out that I still needed to import it into the SSIS store and I did so, but it still would not run. I could not figure out why, but then after digging and looking at all the options, I saw this dialog and decided to click on the button circled.

I put the password into the package and then saved it out to the File System, then imported it into SSIS Store. I needed to schedule this package to run and so I went into SQL Agent jobs and created a job with a step that indicated to run an SSIS Package. I knew that I had to specify a password so I looked up the dtexec utility page and found the /DECRYPT option. Now here is what threw me. I thought that in the first place that BI Studio saved the package or that the import of the package saved the option of Encrypt with Password option so I went into the CommandLines tab and edited the option and put in /DECRYPT with the password in the Edit Manually option, and then clicked OK. I ran the package and it worked. Well I found out later that it had not, but another weird part is that when I edited the job step the option in CommandLines was set back to Restore original options and I did not see the DECRYPT option in the window below.

So I battled over this for a while, then tried the import again and clicked on the dialog button to change the protection level and chose the Password option, set the password and then it imported. After doing this I changed the job and an interesting thing happened. It prompted me for the password for the package this time. I went into the Commandlines and it did not show any difference, but I went ahead and left it.

I found out that the DECRYPT option was in the CommandLines window, but there was no password specified, which means that it was hiding it from the UI, which is OK I guess, but I had never seen it in there saved before when I manually edited it.

I also found out that when I right clicked on the job and scripted it as CREATE to a new query window, that the option in Command showed the DECRYPT option with the password I specified when I created the Job Step.

So wild stuff and I have survived and the job actually runs now under SQL Agent so I am happy now. Just more wiser too as I spent a great amount of time working this one out.

Sorry if this does not make complete sense. I wanted to get this out now so that I did not forget it.
Have fun and I will have more SSIS stuff to share after the next couple of days.

I just created a Connect item about my post on SQL Server 2005 clustering. Feel free to vote on this bug.

Connect Item 289092

Have a happy day.

In an earlier post, I mentioned a bug that I think that we found in SQL Server 2005 Clustering. I have confirmed the bug. The bug goes again like this:

You have 2 Windows 2003 machines and one or both have been named with lower at least 1 lower case letter in the name. Now you may think, how can this happen, because when I installed the Servers I saw in the name of the machine all upper case letters in the dialog box. What you don’t know is that when you don’t put CAPS LOCK on or don’t hold the shift key down, it is recording the name in mixed case, even though the dialog shows upper case. This will be important later.

This is my findings (with help from another student in the Clustering Class from Ameriteach in Denver

Now you have 2 servers and you are ready to cluster them together. Now you launch Cluster Administrator in Windows 2003 and it allows you to cluster. Now the interesting thing is that when you see the dialog box to add the server to the Cluster, it will show you a name in the box. This name is retrieved from HKLM\System\CurrentControlSet\Services\TcpIp\Paramters\HostName registry key. The interesting thing is that when you add it to the Cluster, it will show the cluster machines in the list in Cluster Admin, and the name is retrieved for the list from HKLM\System\CurrentControlSet\ComputerName\ActiveComputerName so if they are different (which I have not found a reason that they would be, but this is what our testing revealed) then you would see wierd behavior.

When you get the machines in the cluster, then you will attempt to install SQL Server. If you have any lower case letters in the machine name, it will let you run setup, and then it will get to the part where it wants to install SQL on the clustered machine, and it fails to find the node. My hunch is that it just does a binary compare on the machine name and does not find it since “ben1″ is not equal to “BEN1″ unless it looks case insensitive. You will get an error that the “Parameter is incorrect” and it will fail and rollback the install.

The other thing to note, is that when SQL Setup rolls back the install, it will not remove the Cluster Resource based on the dll and it will not delete the SQL Resources that it created, you will have to do that manually. Not a very good cleanup routine for a setup.

I will be filing a bug in Connect so that the SQL Team can have a look. I will update this post or create a new one when I have created the bug and it has a URL.

Happy landing and see you out there.

I will be setting up an environment to repro an issue that we found in installing a SQL Server cluster. We had 2 Windows Server 2003 R2 Enterprise servers that we were to cluster and then put SQL 2k5 in a cluster as well.

We go to the point of installing the SQL Server in a cluster and just at the point of completing the install and removing the temporary files, a message came up and indicated failure. No real earth shattering message, but just that it failed because a Parameter was incorrect. That is it.

After we spent some time trying to reinstall and force it through and a support call with Microsoft, we and the support engineers observances were put to the test. The one thing that was noticed first by the support engineer, was that the server names in the Cluster Admin were not the same case (one was UPPER123 and one was lower393). We could not imagine that it could be that, but were willing to try anything. So with a little registry editing, we got the names in the Cluster Admin to be UPPER and LOWER. Then we attempted the install of SQL Server.

To our amazement, the install completed. We completed our install and then for good measure we rebooted the machines. Again to our amazement, the cluster would not start.

To finish a long story, we unclustered, ripped SQL out by hand and took the lower machine and removed it from the domain and renamed it with UPPER case and then rejoined the domain and reclustered and reinstalled SQL Server, and all was well.

I am going to repro that today in a test environment so that I can make sure that it repros, and then I am going to file a bug.

Any ideas from others that have done this, please feel free to comment.

There is a new Cumulative update released as a hotfix. The KB Article is 936305.

It looks like there are quite a number of fixes included and many have their own KB articles. I will be testing this out this week when I get my hands on it. It is a very large hotfix (around 100 MB) so take care.