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