Resolving MS SQL Remote connection issues I

Setting up a vCenter Server with a remote MS SQL database is a common practice for environments other than very small, proof of concepts or labs. Chances are, you will face some difficulties setting up a remote connection from the new vCenter Server to the MS SQL server. In case the DBA is not around, this post presents a few points to help you setting up a remote connection to the database.

In this example, I installed MS SQL 2012 on a Windows Server 2012 R2.

The first part of the VMware vCenter Server is the Single Sign On (SSO) server. SSO also needs connectivity to the MS SQL Server; unfortunately, SSO comes with its own peculiarities. So my advice is to test the connectivity beforehand, by setting up an ODBC connection to the database. The areas of interest are:

  • Default Instance and Named Instances
  • Ports and Network configuration.
  • Services.
  • Windows firewall.
  • Permissions.

Default Instance and Named Instances

You can install multiple instances on a single MS SQL server. You can have one “Default instance” and multiple “Named instances”. There is no real difference between a default and a named instance. However there are some differences in setting up a connection and the underlying network.

On my test server, I installed a default instance and a named instance. We start with the default installation.

Ports and Network configuration

On the MS SQL Server, start the SQL Server Configuration Manager (SQL SCM).
Go to SQL Server Network Configuration.

20140127-01Figure 1 Default Instance MSSQLSERVER

If multiple Instances have been installed, there will be a “Protocol for <Instance Name>” entry for each Instance. For the default instance, select “Protocol for MSSQLSERVER”.

In the right hand pane, Shared Memory and TCP/IP should be enabled.

Right click TCP/IP and select the Properties.

20140127-02Figure 2

On the Protocol tab, “Enabled” should be Yes. “Listen All”, means that the SQL Server will listen on all available IP addresses.

Select the IP Addresses tab.

20140127-03Figure 3

By default the Default instance is configured for fixed port number 1433.
If, for some reason this instance must be configured with another port number, you can adjust all instances of the property “TCP Port”, to the desired value. Later on, we will see that using a port number other then 1433, has impact on setting up a connection.

Important: Make sure hat all TCP Port instances are 1433. On one occasion I forgot to change the IPALL.

In case you change the port number, you will be presented this Warning. So time to move on to the next topic; Services.

20140127-04Figure 4

Services

After changing the TCP properties, the SQL Server Service needs to be restarted. It is also a good opportunity to check the services.

Go to SQL Server Services.

20140127-05Figure 5

At a minimum, the SQL Server (MSSQLSERVER) must have a State Running. The Start mode should be on “Automatic”. Note: The SQL Server Browser is not needed in case of fixed ports.

Windows firewall

During the installation of the MS SQL Server you will receive a warning regarding the Windows Firewall. The link presented points to this page.

20140127-06Figure 6

To add the Firewall rules needed, open the Windows Firewall and choose “Advanced Settings”.

Select Inbound Rules and add a New Rule:

  • Rule Type: Port.
  • Protocols and Ports: TCP and Specified local ports: 1433 (should match the port number in the previous section).
  • Action: Allow the Connection.
  • Profile: check Domain.
  • Name: a descriptive name, like: MS SQL TCP 1433.

This should do for this moment.

Note: If the vCenter Server and the MS SQL server reside in different subnets, you have to make sure that traffic is not blocked.

Permissions

The final part is to check the permissions. In most cases, you install vCenter Server with a service account, so this account must have permission to access the database. So first create an account in AD.

On the MS SQL Server, start the SQL Server Management Studio.

Connect to the default instance.

20140127-07Figure 7

  • Server type: Database Engine.
  • Server name: <the name of the, MS SQL SERVER>, in my case the host name of the server is: SQL2012.
  • Authentication: Windows Authentication (I still use the account, I used while installing MS SQL).

Create an empty database.

  • Under Databases, Right Click and “New Database”.
  • General page, provide a name for the database, e.g. VCDB.
  • Leave other settings to their defaults.

Create a new Login for the user account that will use the database. In my case, the domain user account that will install the vCenter Server: virtual\sa_vc01_sql.

  • Under Security, Logins, right click and “New Login…”.
  • Login name: <user account>, in my case: virtual\sa_vc01_sql. You can use the Search button.
  • On the General page, leave other settings to their defaults.
  • On the User Mappings page, place a tick at the newly created database, and enter “dbo” under Default Schema.

20140127-08Figure 8

Test connectivity

Switch to your intended client machine, in my case my new vCenter Server.
Log on with the account that will use the database. In my case, the domain user account that will install the vCenter Server: virtual\sa_vc01_sql.
First we need to install the latest SQL Server Native client, download from here.
Open the Install instructions and search for the Microsoft SQL Server 2012 Native Client. Download the x86 or the x64 package (sqlncli.msi) and install the client.

  • Under Administrative Tools, open Data Sources (ODBC).
  • Tab System DSN, Button Add.
  • Select SQL Server Native Client 11.0.
  • Name: VCDB.
  • Server : <the name of the, MS SQL SERVER>, in my case the host name of the server is: SQL2012. If the drop-down list does not work, just type in the name.
  • How should SQL Server verify… : select “With Integrated Windows authentication”.
  • Place a tick at “Change the default database to”, now you should be able to browse for the available databases.

20140127-09Figure 9

Leave all other settings to their default. After finishing the configuration, you will be presented a window with the option “Test Data Source”. So test the connection, if everything went well, you should receive this message.

20140127-10Figure 10

Connectivity between the new vCenter Server and the remote MS SQL server has been established. You should now be able to start a successful installation.

A different port number?

For some reason, the default port 1433 is not OK and you must configure the instance for another port number, let’s say, port 11433.

First, return to the SQL Server Configuration Manager and select “Protocol for MSSQLSERVER”.
Right click TCP/IP and select the Properties. On the “IP Addresses” tab adjust the TCP Port properties from 1433 to 11433.

20140127-11Figure 11

After saving the changes, do not forget to restart the SQL Server (MSSQLSERVER) Service.

Change the firewall rule, you will find the port number on the “Protocols and ports” tab.

TIP: you can also check TCP connectivity on the MS SQL server with the SQL Server Management Studio tool. By default, on the local server, the MS SQL SMS, connects using Shared Memory. However, you can force a TCP connection, even with a different port number:

20140127-12Figure 12

Format, for a default instance: tcp:<server name>,<portnumber>. If this works fine, it is time to return to the new vCenter Server.

Test the connection. By default, the Data Source defaults to port 1433. However, you can force it to use another port number.

20140127-13Figure 13

Format: for a default instance: <server name>,<portnumber>.

Other settings

As this post explains, this option should be enabled, in case you want to execute stored procedures. By default this option is on.

On the MS SQL Server, start the SQL Server Management Studio.
To configure the remote access option, in Object Explorer, right-click a server and select Properties.

  • Click the Connections node.
  • Under Remote server connections, make sure that the Allow remote connections to this server check box is checked.

20140127-14Figure 14

I will discuss Named Instances and Dynamic Ports in my next post.

As always, I thank you for reading.

Disclaimer: I am NOT a MS SQL DBA, so I encourage you to contribute if you think this post can do better.

This post is the first episode about troubleshooting MS SQL remote connection issues. Also read the second episode.

Advertisements

One Response to Resolving MS SQL Remote connection issues I

  1. Matt Felzani says:

    Enabling TCP/IP and setting the ports helped us a ton!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: