Sccm, Scom, Remote SQL 2005 & the Windows server 2008 firewall

July 29, 2008 at 7:49 pm in ConfigMgr 2007, MOM, Operations Manager 2007, Opsmgr 2007, SCCM 2007, SCOM 2007, SMS, Sms 2003 by Kenny Buntinx [MVP]

Hi All,

Let’s start by saying that this blog post is probably more OpsMgr related, but all topics are valid for a remote SQL Install for Sms, SCCM or any of the other System center products, so I guess it’s still ok to post it here.

Look, I am not all that good with popular quotes, never seem to be able to remember them just right. But this is one of them that I have never had trouble remembering. “It is all fun and games until someone throws a firewall into the mix”.

 

Not sure who the quote is from, but I am pretty sure he was refering to my lab environment. Yesterday, I redeployed my Opsmgr 2007 environment, to test the installation on windows server 2008. I figured, install a new sql server on 2008 on one machine, then install opsmgr 2007 on another, shouldn’t take more than a single evening. I’ll start rolling out agents and importing management packs the day after. Seemed like a plan at the time.

 

So I installed Asp.net, powershell, IIS, the II6 compatability tools in short all the requirements to install SQL 2005 reporting services on a Windows Server 2008 as listed here:

http://support.microsoft.com/kb/934164

Then I installed SQL 2005, the database engine and a default install of SQL reporting services, followed by applying SP2.

Next, I installed the Scom database, no problem at all, I am on a role here.

 

Then I started the management server and console install on the remote box. Err.

The root management server complained that it couldn’t find the database. I splapped myself on the forehead, sure you silly you still need to enable The Tcp/ip protocol in the SQL Server configuration. I checked, and found that Tcp/ip was already enabled as a listening protocol.

Hum, strange, opened a dos box, and ran netstat -a -n -p tcp to see whether my sql box was listening on port 1433. Lo and behold, it wasn’t. You see, I know it was something like that. Still took me a while to figure out that my SQL Server, which was running in a specific named instance was listening on dynamic ports. (If anyone knows how that could have happened just let me know).

Now, I wasn’t going to let something silly as that stand between me and my plan, so I configured the SQL tcp/ip protocol for this instance to listen on port 1433, and restarted the SQL Server service as listed here:

http://msdn.microsoft.com/en-us/library/ms177440(SQL.100).aspx

I subsequently ran netstat -a -n -p tcp again and tada, the server was listening fine on port 1433.

 

Back to the original task at hand install the OpsMgr management server. Err.

Database still not found, ok, I am getting fed up with this, I download microsoft’s portqry tool, and verified whether I could access port 1433 from the remote machine. The portqry -n sqlserver01-e 1433 came back with a response of Filtered. Another slap on the forehead, you nitwit, you have the Windows Server 2008 firewall running. So I went to the Sql box, and decided NOT to disable the firewall but to configure it to open port 1433, as described here:

http://msdn.microsoft.com/en-us/library/ms175043(SQL.100).aspx

Once done, I ran my portqry again, and it showed up as listening, great, we’re back on track.

 

I launched the Opsmgr management server installation again, and the darn thing failed on me again.

Luckily for me the log file came around telling me that a custom action in the msi had close the handle to soon, and that it should be configured not to do that. _SetRootHealthService_Wizard unexpectedly closed the hInstall handle was the error message at hand. So after telling the setRootHealthService_Wizard that it wasn’t allowed to close the handle so soon, or that I would put it in the naughty corner, I retried the installation.

 

Apparently my authority, that still works on my 3-Year old soon, didn’t impress the setroothealthservice_wizard. In a illuminated attempt to still get this to work I went back to the Sql server box and configured the firewall to log dropped packets. Retried the installation again, which obviously failed, and went back to analyze the windows server 2008 firewall log on the sql box. This revealed dropped packets on udp port 1434. Oh, now that’s easy enough to fix, let’s just open that port and we’re set. Erm wait a minute, I thought all sql database engine communication went over tcp port 1433, what’s up with this 1434 udp port all of a sudden.

 

Great after having this miracle idea of deploying sql on a box with the firewall still running, I’ll have curiousity kick in, this is going to set back my planning on this a couple of hours, or at least that’s what I thought, but Live search and Sql Magazine to the rescue the udp port 1434 reportedly is needed to access a named instance:

http://www.sqlmag.com/Article/ArticleID/39447/sql_server_39447.html

 

Now, that I had settled my curiousity, I was free to open udp port 1434 in the SQL Server firewall, and retry the opsmgr root management server installation, and kadadzing the install completed with success.

 

Enjoy.

“Everyone is an expert at something”
Kim Oppalfens – Sms Expert for lack of any other expertise
Windows Server System MVP – SMS
http://www.scug.be/blogs/sccm/default.aspx

http://www.linkedin.com/in/kimoppalfens

Tweet about this on TwitterShare on FacebookShare on Google+Share on LinkedInPin on Pinterest