System Center 2012 SP1 Configuration Manager (SCCM 2012) Installation: SQL Server Database Error when using Named Instance…
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.
The error states…
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
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
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
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
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.
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.
6. Now, last step go back to your SCCM Installation. Fill in the details
7. And…NO ERROR. You are on next screen that means you are ready to install SCCM using SQL Server 2012
So that’s how the SQL Server related issue got fixed. Enjoy