Failure: The SQL Server ‘{0}’ is unavailable

The Background:

I’ve started working with Microsoft Dynamics CRM since early days of version 3.0 and have done “quite a few” installations over the years. However, this is a new one to me…

This particular setup has a front end CRM Server and a SQL Server 2008 and SSRS backend. It was for a client in the banking industry that runs  mission critical applications, as a result their IT infrastructure is very much locked down and they have M to the power of N layers of firewall in between the CRM Server and their SQL Server; with *everything* enforced via group policies.

The Problem:

When we install CRM on the CRM server, on the very last check list dialog it one warning and one error that prevented the installation from going ahead;

The warning message:

|Warning| Check SqlServerAgentValidator : Warning: Setup was unable to verify that SQL Server Agent (SqlAgent$crm) was running.

The error message:

|  Error| Check CrmSqlDomainValidator : Failure: The SQL Server ‘{0}’ is unavailable.

And sure enough, we saw the following details getting logged in the server setup log.

09:11:50|Verbose| Calling BaseGroup.Validate
09:12:11|Warning| Check SqlServerAgentValidator : Warning: Setup was unable to verify that SQL Server Agent (SqlAgent$crmdev) was running.
09:12:11| Info| Check SqlInstanceNameValidator: Success
09:12:11| Error| Check CrmSqlDomainValidator : Failure: The SQL Server '{0}' is unavailable.
09:12:11| Info| Check SqlServerValidator: Success
09:12:11| Info| Check SysAdminValidator: Success
09:12:11| Info| Check WordBreakerValidator: Success
09:12:11| Info| Check FullTextInstalledValidator: Success
09:12:11| Info| Check FullTextRunningValidator: Success
09:12:11| Info| Check ConfigDatabaseFilesValidator: Success
09:12:12| Info| Check CrmDatabaseFilesValidator: Success
09:12:12|Verbose| BaseGroup.Validate completed

Diagnosis:

Judging from the warning and error messages, we initially suspected the following causes:

  • The SQL Agent window services wasn’t running on the SQL server.
  • The SQL Server instance itself wasn’t running.
  • The user account used to install CRM wasn’t a Local Admin on the SQL server.
  • The user account used to install CRM wasn’t a System Admin on the SQL Server instance.

After checking with the system administrator and the DBA, all of these have been configured correctly.

But, we were quite adamant that the CRM installer was, in one way or another, unable to access SQL Server. So, we ran a SQL profile trace to see if the responsible SQL Server instance was getting a reaction from the CRM installer. To our surprise, it did execute a bunch of scripts against the SQL server instance. (FYI: The sort of stuff that the CRM installer does to the SQL Server is beyond sanity… Try to run a SQL profile trace on the CRM installer and you’ll know exactly what I mean.)

Then we started digging deeper around how else does CRM interact with SQL Server apart from TCP port 1433 for SQL Server Sockets service… With help from the support team from Microsoft Australia, we found out that there is a whole raft of other ports that the CRM Web Application and  the Data Connector require to connect to the SQL Server.

Resolution:

In our case, we needed a burn through the M to the power of N layers of firewalls on TCP port 445 between CRM Server and the SQL Server. After this firewall burn, the CRM installation was completed successfully.

“So, ” I hear you ask, “what the hell is TCP port 445 used for?” – Microsoft Directory Services (Microsoft-DS), and I quote, “Active Directory service required for Active Directory access and authentication”.

Your inevitable second question is, “what other ports does CRM use?” Here is an exhaustive list of Network ports used for Microsoft Dynamics CRM 4.0.

Network ports used for the Microsoft Dynamics CRM Web application

The following table lists the ports used for a server that is running a full-server installation of Microsoft Dynamics CRM. Moreover, with the exception of the Microsoft SQL Server server role, and the Microsoft Dynamics CRM Connector for Microsoft SQL Server Reporting Services server role, all server roles are installed on the same computer.

Protocol Port Number Description Explanation
TCP 80 HTTP Default Web application port. This port may be different as it can be changed during Microsoft Dynamics CRM Setup. For new Web sites, the default port number is 5555.
TCP 135 MSRPC RPC endpoint resolution
TCP 139 *NETBIOS-SSN NETBIOS session service
TCP 443 HTTPS Default secure HTTP port. The port number may be different from the default port. This secure network transport must be manually configured. Although this port is not required to run Microsoft Dynamics CRM, it is strongly recommended. For information about how to configure HTTPS for Microsoft Dynamics CRM, see the article “Make Microsoft Dynamics CRM 4.0 client-to-server network communications more secure.”
TCP 445 Microsoft-DS Active Directory service required for Active Directory access and authentication
UDP 123 *NTP Network Time Protocol
UDP 137 *NETBIOS-NS NETBIOS name service
UDP 138 *NETBIOS-dgm NETBIOS datagram service
UDP 445 Microsoft-DS Active Directory required for Active Directory access and authentication
UDP 1025 *Blackjack DCOM, used as an RPC listener

Network ports used by SQL Server running the Microsoft Dynamics CRM Connector for Microsoft SQL Server Reporting Services server roles

The following table lists the ports that are used for a computer that is running SQL Server and has only SQL Server and the Microsoft Dynamics CRM Connector for Microsoft SQL Server Reporting Services server roles installed.

Protocol Port Number Description Explanation
TCP 135 MSRPC RPC endpoint resolution
TCP 139 NETBIOS-SSN NETBIOS session service
TCP 445 Microsoft-DS Active Directory service required for Active Directory access and authentication
TCP 1433 ms-sql-s SQL Server sockets service. This port is required for access to SQL Server. Note that, this number may be different if you have configured your SQL Server to use a different port number.
UDP 123 *NTP Network Time Protocol
UDP 137 *NETBIOS-NS NETBIOS name service
UDP 138 *NETBIOS-dgm NETBIOS datagram service
UDP 445 Microsoft-DS Active Directory required for Active Directory access and authentication
UDP 1025 *Blackjack DCOM, used as an RPC listener

* This port and service are not strictly used by Microsoft Dynamics CRM but may be required for the various underlying platform components, such as Windows or Active Directory.

There are a couple of source for this information.

1. http://rc.crm.dynamics.com/rc/regcont/en_us/op/articles/crmports.aspx  (Don’t worry, I totally knew where to find this page by heart… NOT!)

2. Page 60 of the Planning Guide in the Implementation Guide, I should have known this one better.

Advertisements

4 responses to this post.

  1. Man! Thank! You! Try installing SQL2008 en CRM 4.0 on Windows 2008 server without all the proper courses. After finding out that turning of the firewall didn’t do it, I found a batchfile listing at Microsoft opening up ports for the SQL server so you could actually connect to it; but not CRM.. Amazing, I am getting more and more the idea that Microsoft isn’t secretive, they just don’t do documentation, at all.

    Reply

  2. Thanks
    it really worked!

    Reply

  3. Hi Jian,

    I’m facing the same issue, but my firewall is off on both servers? does the port still matter in this case? (I dont have much experience so please bare with me)

    Thanks,
    Farhat

    Reply

  4. Millard has a degree in psychology and a Ph – D in computer science with over 23
    years in research and development of design systems
    for major call centers and corporations around the
    world. People then see your gigs and if they’re interested they will pay you 5 bucks for you to complete whatever service it is you are offering.
    We seek to expand our category base, increase our market share in each of the markets we cover,
    increase the scope of products offered on Fiverr, and broaden our global reach.

    Reply

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: