Share via


System Center 2012 SP1 Configuration Manager (SCCM 2012) Installation: SQL Server Database Error when using Named Instance…

Hi All,

Last week one of our customers was trying to install System Center 2012 SP1 Configuration Manager. During the installation customer encountered an issue with SQL Server when using Named Instance instead of Default Instance. MSSQLSERVER.

I decided to try out the scenario on my own test environment to simulate the same issue by configuring the SCCM & SQL Server using the Names Instance instead of using the Default Instance that work on Port 1433.

Here is my test scenario using 2 virtual machines.

Domain Controller: DC.MSFT.COM

SCCM Primary Site: SCCM.MSFT.COM

There are steps I followed to begin my Configuration Manager deployment in my test environment.

INSTALL Windows Features for SCCM.CONTOSO.COM machine. Following are the prerequisites required.

- .NET Framework 3.5

- Remote Differential Compression (RDC)

- Bits with IIS Role

- PowerShell 2.0 Engine

Install WSUS Server Role

Install Windows ADK with USMT, WINPE & Other components. I have installed Windows Assessment Services as it includes its own SQL Express database (Just to avoid any additional complexity)

Create a Windows Firewall exception rule. Open WF.MSC. Create a new Inbound Rule for Port 1433, 4022 exception. Port 1433 to connect to SQL Database & Port 4022 for Service Broker. You can also enable these ports using a Group Policy.

Install SQL Server 2012 with Named Instance: SCCM

Till this stage everything was going smooth. I could open SQL Server Management Studio & connect to the database. Next step is to install the System Center 2012 SP1 Configuration Manager.

I launched the SCCM installer & started following the Primary Site Configuration. On the Database Information wizard screen I was getting the following error.

clip_image001[5]

The error states…

---------------------------

Configuration Manager

---------------------------

Setup is unable to connect to SQL Server with the connection information provided. Verify the following:

. The SQL Server and instance names are entered correctly

. The specified SQL Server instance is not configured to use dynamic ports

. If a firewall is enabled on the SQL Server, inbound rules exist to allow connections to the correct ports

. The account used to run Setup has permissions to connect to the specified SQL Server instance

---------------------------

OK

---------------------------

As you can see it has given me few details to check with. And then I started checking everything 1 by 1.

For the first issue, I am sure that I have created an instance with correct name.

For the second issue, I have assumed that SQL Server will use Port 1433. So we are good. We will come to the same point back after 2-3 lines Smile

For the third issue I have manually created Firewall Port exception. So that was ruled out.

For the last issue I have used the default system account that SQL Server 2012 configured so this was also ruled out.

Now, let’s come back to Point 2 i.e. The specified SQL Server instance is not configured to use dynamic ports. In this example, this is the reason we are getting the error when using the Named Instance.

Here is the FIX

1. Go to Windows Server Start Screen (yes Windows Server 2012 also has a new Start Screen as Windows 8 because of the shared codebase. NT Kernel 6.3 Build 9200 anyone J)

2. Search for SQL Server Configuration Manager

clip_image003[4]

3. In SQL Server Configuration Manager, expand SQL Server Network Configuration. Click on Protocols for SCCM (SCCM is your Named Instance)

In the right side pane, right click on the TCP/IP (Status Enabled) and click on Properties

clip_image004[4]

4. In that click on IP Addresses tab. Scroll down to the bottom & make a change to IPALL. TCP Dynamic Ports -> Blank. TCP Port -> 1433 (as this is our default port for SQL Server Database Connection). Click on Apply.

And notice that you need to restart SQL Server (SCCM) Service.

Additionally, you can also configure the same for all the IP Addresses. IP1, IP2…etc. In my case I just made the change as specified above.

clip_image005[4]

5. To restart a service. Click on SQL Server Service. Right click on Instance Name: SQL Server (SCCM) and click on Restart. Wait for few seconds.

clip_image006[4]

6. Now, last step go back to your SCCM Installation. Fill in the details

clip_image007[4]

7. And…NO ERROR. You are on next screen that means you are ready to install SCCM using SQL Server 2012 Smile

clip_image008[4]

So that’s how the SQL Server related issue got fixed. Enjoy Smile

Comments

  • Anonymous
    June 14, 2013
    thanks for this post

  • Anonymous
    July 24, 2013
    muchas gracias

  • Anonymous
    September 03, 2013
    Thanks for the info. Saved me a huge headache.

  • Anonymous
    October 21, 2013
    Thanks mate, for me I had to blank both fields (no 1433)

  • Anonymous
    November 02, 2013
    Thanks a lot :) I was stuck with this error from past 4 day, thanks for the help........ applause

  • Anonymous
    November 14, 2013
    awesome, thanks, this helped a lot!!!

  • Anonymous
    December 30, 2013
    Thanks For Helps

  • Anonymous
    January 14, 2014
    Excellent. It solved my issue.

  • Anonymous
    January 20, 2014
    Still getting the issue checked all of these?

  • Anonymous
    January 30, 2014
    The comment has been removed

  • Anonymous
    February 24, 2014
    Thanks for posting this - SCCM is complicated as is, glad I found your fix for this issue.

  • Anonymous
    March 19, 2014
    Thank you this helped me out big time!!

  • Anonymous
    April 17, 2014
    I don't know how can I thank you, I was troubled by this for the past 3 days. Excellent article!!! One of the best I read recently.

  • Anonymous
    April 22, 2014
    The comment has been removed

  • Anonymous
    April 23, 2014
    RockStar, thanks. Sometimes I think MS like to make themselves seem clever...

  • Anonymous
    May 07, 2014
    Awesome, thank you so so much. This was huge for me and made my day a whole heck of a lot better.

  • Anonymous
    May 16, 2014
    Thanks everyone for your comments. I share your feelings :)

  • Anonymous
    May 28, 2014
    Thanks from me too

  • Anonymous
    June 09, 2014
    Wow! You're the best...thank you so very much.

  • Anonymous
    July 30, 2014
    thanks

  • Anonymous
    August 02, 2014
    worked great thank you

  • Anonymous
    August 18, 2014
    Thank you alot! :)

  • Anonymous
    August 28, 2014
    Thanks for your help.

  • Anonymous
    September 11, 2014
    great and straight to the point

  • Anonymous
    September 15, 2014
    Will needing to remove the dynamic port setting also apply if you were to be moving a site DB to a named instance from an aliased SQL server?

  • Anonymous
    October 07, 2014
    Really something Grate in this article Thanks for sharing this. We are providing DATABASES courses training online. After reading this slightly am changed my way of introduction about my training to people. And also refer my website for DATABASES Training and solutions of DATABASES applications. Please Visit Us @ http://www.tectist.com/databases-online-training.html " >DATABASES training courses online

  • Anonymous
    November 03, 2014
    Just Salute up

  • Anonymous
    November 10, 2014
    Thank you very much

  • Anonymous
    November 16, 2014
    THANK YOU!!!

  • Anonymous
    January 27, 2015
    What if you need to provide a fixed port other than 1433, We do not use default ports in our company for SQL Server.

  • Anonymous
    March 07, 2015
    Much appreciated.It works like a charm