Dynamics CRM Install\Import “The SQL Server {sqlserver_name} is unavailable”

My apologies for the significant gap between my last post on our CRM 2016 Upgrade Adventure and this one. My time has been consumed with preparing for go-live, but I’ve been keeping track of the roadblocks and caveats we encounter as I go so that I can post about them at later dates.

One of the challenges with this deployment that we did not encounter with the last is a significant increase in firewall and context configuration. LU has, to their credit, made great efforts over the last several years to ensure our network is as secure as possible. With an increase in security, however, comes an increase in complexity.

While setting up CRM you might expect to open a port for the SQL instance (ie 1433). It might also occur to you that UDP 1434 should be opened for the SQL Browser Service. Now your app server has a clear line open to the SQL instance. Everything should be ready, so you go to create or import your organization only to encounter “The SQL Server {sqlserver_name} is unavailable”.

You might also encounter a message about not being able to verify that the SQL Agent is running. Being a thorough Sys Admin\DBA you check the SQL services for these and confirm both are up. You also use telnet or another utility to confirm that the ports are indeed open, so what on earth could CRM need in order to reach SQL?

TCP 445… that’s right. Because of the unique setup of CRM it requires TCP 445 to do any kind of setup. What is TCP 445 you ask? “Active Directory service required for Active Directory access and authentication.” (https://technet.microsoft.com/en-us/library/hh699823.aspx). Why an app server would need an AD authentication port opened to the SQL server is anybody’s guess, but it cleared our issue right up. All system checks passed and it happily imported our database.

It should be noted, if you’re using an Availability Group setup then this port will need to be opened to the other servers in the AG as well. I have had the most success when opening it to the AG listener name as well as all nodes.

Bonus Round

If none of this helps you, here are some other things I’ve found are necessary to appease the install\import wizards.

  • Make sure you’re in the local Administrators group on the app servers as well as every node in the SQL cluster or AG (added explicitly, not through a group) .
  • Make sure your account has the sysadmin role on the SQL instance.
  • Specify the SQL server name using the backslash notation, even if the AG name doesn’t contain it. For instance, if your AG is normally accessed as SQLAGINSTANCE,50000 you would use SQLAGINSTANCE\SQLAGINSTANCE,50000 in the wizard. It seems to be hard-coded to only accept it in that manner.

6 thoughts on “Dynamics CRM Install\Import “The SQL Server {sqlserver_name} is unavailable”

Add yours

  1. Hi Landon,
    445 is not simply a random port in AD. It is in fact SMB, also known as CIFS, also known as Microsoft FIle Sharing, also known as The-Gaping-Hole-in-MS-Security that 95% of viruses and worms make use of for their nefarious activities.
    The question is not, “how many hosts shall I open this service to?”, but rather, “Why TF am I being asked to open this towards my SQL servers?” There perhaps isn’t much choice in terms of alternative, but at the very least you should make clear to your readers that this should be as temporary as possible because it is a Very.Bad.Situation ™.

    1. Thanks Andrew. Sadly in this case I don’t think there is much of a choice, as the CRM installation will not continue without it. However, it’s good to be aware of all the implications and risks.

    1. Hi Bill, excellent question. I’ve never tested it personally but it’s likely that you could block it post-install. However, you’ll likely run into issues when running certain actions in the admin utility, such as importing/creating organizations, etc.

  2. We are experiencing this exact same issue and I found this valuable to provide this info to our enterprise team, firewall team and SQL team in helping us tell them where to look to troubleshoot this exact problem. Wish me luck.

Leave a Reply

Your email address will not be published. Required fields are marked *

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑