SQL Server error when using Named Instance

I encountered this problem a while ago, when I was installing a test lab with Configuration Manager 2012 R2 in my own environment. I installed all prerequisites and was about to do the SQL database setup in the SCCM setup wizard, so I typed in all the correct information as well as the “Named Instance”, which I chose doing the installation of the SQL Server Database and the exceptions to the firewall.

At this point I knew it was all running as well as working – I could connect to the database, open SQL Server Management Studio and only needed the SCCM Installation Setup Wizard but when I pressed “Next” I got the following error message.

1

  • The Instance Name were created and typed in correctly
  • At this point I assumed the port for the SQL database would be set to 1433 (1433 is our default)
  • The exceptions in the firewall were made (Tried turning it off completely)
  • The account I used for this was the default Admin Account, configured by the SQL server

Verifying the above several times, I could not figure out why I got this error so I kept trying to get it to work when I remembered that I did not use a Default Instance Name (MSSQLSERVER) but instead created my own. This is the reason for this error as ConfigMgr does not support dynamic ports, and by choosing a Named Instance, it automatically configures it with a default port. NOTE: It is not wrong to use Named Instances, as you will be able to do better isolation in your environment than using default names but do what you prefer in your own environment.

You need to do the following changes to make it work when using a Named Instance:

  1. Open “SQL Server Configuration Manager” (Search for it if  you can’t locate it)
  2. In the “SQL Server Configuration Manager”, you need to expand “SQL Server Network Configuration” -> Click “Protocols for xxx” (xxx=The Named Instance) -> Right click the “TCP/IP” in the right side -> Click “Properties” 2
  3. You will see two tabs, Click “IP Addresses” -> Scroll down and locate the “IPALL” settings (as this is the information the SQL Server database use) -> Remove so it is blank in the “Dynamic Ports”   -> Type in the default SQL database port 1433 (1433 is also the default TCP port when choosing Default Instance name) for the “TCP Port” option.                                                                               3
  4. You could also do these changes to all the IP settings (IP1, IP2, IP3 etc.) if you want to be sure, but it is not required and should work fine if changes made for the “IPALL” is done, as in “step 3”
  5. To apply the changes you will need to restart the SQL Server Service for the Named Instance of yours, as shown in picture below. Press “SQL Server Service” -> Choose “SQL Server xxx” -> Right click and choose “Restart” 4
This entry was posted in Uncategorized and tagged , , , , . Bookmark the permalink.

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