Troubleshooting View 5 ODBC connection errors

Posted on by vTexan

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 ODBCfrustr8tion 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.

SQL Server Configuration Manager

2. Click on SQL Server Network Configuration – Protocols for <YourDabase Name>

1 - Go to SQL Server Config Manager - Click on Protocols for MSQServer

3. First, make sure it’s set to enable.  Then right click on it and click on IP addresses.

2 - Make sure Protocol Enabled - then click IP Address

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.

3 - make sure you pick the IP that has your IP address listed and enable

5. Once you are done with that, it will prompt you to restart your Database.

4 - once you change that - you need to bounce the SQL Server

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.

restart SQL DB

 

 

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

60 - Start-programs-Microsoft SQL Server 2008 R2 - SQL Server Management Studio

 

2. Go ahead and just log into your database:

63 - connect into your DB

 

3. Then right click on the root SQL Database you have and right click on Properties towards the bottom:

64 - Right click on your local SQL Server - Properties

 

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.

53 - Make sure you enable SQL server and Windows Aut Mode on the Security of the SQL instance

 

 

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.

70 - start - firewall

2. Then click on “Windows Firewall Properties”

71 - click on Windows Firewall Properties

3. From the “Firewall State” dropdown menu, select OFF

72 - set firewall to 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 🙂

 

@vTexan

7 thoughts on “Troubleshooting View 5 ODBC connection errors”

  1. Pingback: Setting up ODBC for View 5 inside vCenter 5 - vtexan.comvtexan.com
  2. Pingback: Installing VMware View 5 Composer inside vCenter 5 « « vtexan.comvtexan.com
  3. Pingback: Setting up View 5 Connection Server for the 1st time « « vtexan.comvtexan.com

Leave a comment

Published by vTexan

AWS Media and Entertainment Solutions Architect Manager based in Plano Tx. Father|Husband|Live Fire Cooker|Cocktail loving Dad to twin girls and IT Nerd Son. Lover of all things BBQ and Spirits ! Can be found on all the socials at vTexan.