Thursday, November 27, 2008

One of the potentially most annoying things I've ever had to do with SQL 2005 is setup an instance to use a CA (certificate authority) issued certificate for SSL encryption. SQL 2005 will always encrypt the login traffic, but it'll use a self-signed certificate and you won't be able to "force encryption" without some other certificate being loaded. Since I think 99% of serious SQL installs would be part of a domain the instructions below assume that to be the case.Here's the imaginary setup that'll be referenced in the steps below:

Request a "Web Server" certificate with a name field of SQLCluster.drew.net, ensure the private key is exportable, and store the certificate in the local computer store.

Start->Run: mmc

Add the Certificate snap-in for the local computer and verify there is a trusted certificate named SQLCluster.drew.net there that has a details tab that indicates "Server Authentication (1.3.6.1.5.5.7.3.1)" in the "Enhanced Usage" section.

Scroll to the bottom of the details and highlight all the text in the "Thumbprint" section. Copy this (ctrl-c).

Start->Run: Notepad

Paste the data you just copied and remove all the spaces. Save this file on your desktop.

Verify that the certificate shows up in the personal folder of the local system and that it's valid.

Again, run the winhttpcertcfg command from above on this node.

Back on SQL1.drew.net in the Veritas Cluster Manager GUI Unfreeze the SQL service group.

"Switch" the service group from SQL1.drew.net to SQL2.drew.net

If it starts on the other node you're done! VCS has replicated the registry change you made and the certificate was loaded successfully.

Delete the exported .pfx file and the copy you made.

That's it. There's obviously a lot to modify if you've got more than one node and/or more than one SQL instace (service group) in the cluster but these steps will work 100%. If you're having problems by all means leave a comment and I'll post a response. This was a very tricky thing to figure out how to do as the only thing the MS documentation tells you is "if SQL is configred as a failover cluster use the FQDN of the failover cluster for the certificate name" and "SQL server will attempt to locate a certificate based on the FQDN of the host". Those two things really screw you if you're trying to load your cert via the SQL Configuration Manager tool!

Wednesday, November 12, 2008

This post is really just for myself... I'm learning to use Linux and there's so many commands I need to know that I constantly forget that I thought a quick post would be in order. If this helps anyone else then, great!

Installing Samba Server for SMB Shares:sudo apt-get install samba (or just try to share a dir and follow the prompts)SAMBA conf is in: /etc/samba/smb.confNeed to add yourself to sambashare group as well

btnx-Get this to get multi-button mice working like they should!-after changing settings run this to restart:$ sudo /etc/init.d/btnx restart

This post has the goods on getting going with it...This post has the goods on key bindings...

Monday, November 3, 2008

Occasionally you may see an error like this and be unable to start the SQL Agent (typically after someone doesn't use the SQL Config GUI to change service accounts)UpdateUptimeRegKey: Operating system error 5(Access is denied.) encountered.

This is easily fixed.

Fire up regedit, navigate to HKLM\Software\Microsoft\Microsoft SQL Server\[YOUR INSTANCE NUMBER] then right-click and select "Permissions..."Ensure that the SQLAgent Service and SQL Server Service have full control here and you should be all set. Be sure to use the local groups that SQL 2005 creates for this sort of access delegation to avoid having to do this again when you change service accounts. Note that you wouldn't want to use those groups (and instead use just the service account) if you're locking the permissions down to a point that you want to make it very difficult for an OS admin to get access to your SQL instance.