Resolving MS SQL Remote connection issues II

In my previous post, I discussed some steps for troubleshooting the connection from a new would be vCenter Server to a remote MS SQL Server. Because MS SQL servers can host multiple database instances, I added a second Named instance “Dynamic” to my SQL Server 2012.

Start the SQL Server Configuration Manager. Go to SQL Server Network Configuration. Now we see our second instance named “Dynamic”. Note that the TCP/IP protocol is (again) enabled by default. Open the properties and switch to the “IP Addresses” tab.

20140207-01Figure 1

Note the difference with our first instance! The entries for “TCP Ports” are left blank and the value for “TCP Dynamic Ports” is 0, instead of a blank. A zero means that “TCP Dynamic ports” is enabled. Using Dynamic Ports has pros and cons, see also here. However in this example, we continue the configuration with Dynamic Ports. In case you want or must use fixed ports, follow the procedure, described in my previous post. Make sure you select a unique port number for each instance.

How does a remote client know about Dynamic ports? Well, it uses the SQL Server Browser to receive information concerning the correct port number.

So time to check the SQL Server Services. Make sure the SQL Server Browser and the SQL Server (DYNAMIC) services are running and have Start Mode set to “Automatic”.

20140207-02Figure 2

Next is the firewall. We need to add two rules. To add the Firewall rules, open the Windows Firewall and choose “Advanced Settings”.

Select Inbound Rules and add a New Rule. The first rule is for the SQL Server Browser service.

  • Rule Type: Port.
  • Protocols and Ports: UDP and Specified local ports: 1434.
  • Action: Allow the Connection.
  • Profile: check Domain.
  • Name: a descriptive name, like: MS SQL UDP 1434.

The second rule is for the Dynamic port for the SQL Server.

  • Rule Type: Program.
  • This program path: Important, select the “sqlservcr.exe” from the CORRECT Instance! In my case: d:\Program Files\Microsoft SQL Server\MSSQL11.DYNAMIC\MSSSQL\Binn\sqlservr.exe.
  • Action: Allow the Connection.
  • Profile: check Domain.
  • Name: a descriptive name, like: MS SQL DYNAMIC.

The next part is the creation of a test database and a user account, as we did in the previous post.

Start the SQL Server Management Studio. Make sure, you connect to the new instance.

20140207-03Figure 3

Create an empty database.

  • Under Databases, Right Click and “New Database
  • General page, provide a name for the database, e.g. DYNADB.
  • 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.

20140207-04Figure 4

The final step is to test connectivity, so switch over to the vCenter Server or whatever client. Again start the ODBC Data Source Administrator and configure a connection as described in the previous post. A few screenshots.

20140207-05Figure 5 – Server : <server name>\<instance name>, like SQL2012\DYNAMIC.

20140207-06Figure 6 – After placing a tick, the available databases show up.

20140207-07Figure 7 – Yes, success

Again, if the client and MS SQL server reside in different subnets regulated by a firewall, ask yourself whether dynamic ports are the best choice.

As always, I thank you for reading and I welcome your comments.

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

Leave a Reply

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

You are commenting using your 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: