If you’ve been keeping up with my VMware View 5 Install Series then you noticed that in the last part (Setting up the ODBC Connection for View 5) I ran into some issues when trying to create the ODBC connection to my brand new install of Microsoft SQL 2008 R2 SP1. For someone (me) that has really never messed with databases troubleshooting issues can be really frustrating !! Once again, Twitter came to my rescue. The first thing I did was to send out a quick tweet and was overwhelmed with the amount of GREAT responses on how to fix my issue. What was really nice was Jase McCarty fellow vSpecialist over in Louisiana rang my cell phone. I might want to point out that this was at 10:30ish PM and he had a 4am “wake up/go to the airport” schedule the next day !! The great news was he was able to point me in the right direction in less then 5 minutes. I can’t thank him enough for his assistance!
Apparently doing a “next/next/next” install of SQL 2008 R2 requires you to go back and make a couple of changes. I thought I would give you a couple of screen shots of what I needed to do to make the ODBC connection work correctly.
There are 3 parts to this troubleshooting.
- First is enabling SQL to listen on the IP addresses assigned to the server. By default these are set to “disable”. So I take you through how to enable these.
- Second is around enabling SQL Server to accept “Mix Mode” authentication. This means it will take domain ID’s as well as local SQL accounts created on the SQL server. This is required for View 5.
- Third is an all around shotgun blast to solving any sort of firewall issue that might be blocking the remote connections. I like to refer to it as “scorched earth” approach.
Enabling SQL TCP/IP Connections
1. RDP into your new Windows 2008 SQL 2008 R2 SP1 machine and then click on: Start – Programs – Microsoft SQL Server 2008 R2 – Configuration Tools – SQL Server Configuration Manager.
2. Click on SQL Server Network Configuration – Protocols for <YourDabase Name>
3. First, make sure it’s set to enable. Then right click on it and click on IP addresses.
4. Let me also point out, cause I made this “face palm mistake”, make sure you simply don’t enable the IP1 choice and then call it good. Especially if you run IPV6 because IP1 is the IPV6 choice, which is great, but I’m running IPV4 and totally missed enabling IP2 so I spent a little more time troubleshooting until I realized I forgot to enable it on the IP address I wanted to you. Jase suggests just enabling it on all the IP spots.
5. Once you are done with that, it will prompt you to restart your Database.
6 The easiest way to restart is to just click on SQL Server Services, right click on SQL Server and hit restart. This is listed in the same window you used to enable the IP stuff.
Enabling Mix-Mode authentication on your SQL Database.
The next thing you need to to do is change the default install from “Windows Authentication Only Mode” to what is referred to as a Mix Mode or SQL Server AND Windows Authentication Mode. This is once again because View requires the DB to have a local authenticated SQL user and not a domain one.
1. To do this you need to go to: Start – Programs – Microsoft SQL Server 2008 R2 – SQL Server Management Studio
2. Go ahead and just log into your database:
3. Then right click on the root SQL Database you have and right click on Properties towards the bottom:
4. Once that opens, then click on Security and then click on “SQL Server and Windows Authentication Mode” and then restart the DB. Go back to #6 if you don’t remember how to do that.
Scorched Earth/Shotgun troubleshooting Firewall issues.
The only other thing I did, and I really need to go back and turn it back on is I disabled the firewall for SQL Server. It’s probably not that big of a deal since this is my home lab, behind a firewall already but just to eliminate that as an issue I need to do it. If you need to do this to make it work, here is a quick HOWTO:
1. On the SQL Server just go to Start-Run and type “Firewall” and the first option should be “Windows Firewall with Advanced Security” – click on that.
2. Then click on “Windows Firewall Properties”
3. From the “Firewall State” dropdown menu, select OFF
That pretty much covers all the changes I needed to make to get the VMware View 5 ODBC connection to work inside VMware vCenter 5. I’m a Database noob so more then likely not many people will run into this. If you did, hook a brother up and leave a comment below 🙂