Lessons Learned on SQL Server Instances and Ports

While working on a SQL 2008 install and problems encountered when a second instance was added, some questions came up about how SQL uses ports and allow remote access - especially when a second instance is added to a host computer. Here is the summary:

1) SQL Server 2005/2008 Express, Eval, Developer all install their instances (root OR follow-on) remote access turned OFF by default. References: http://support.microsoft.com/kb/914277 and http://www.databasejournal.com/features/mssql/article.php/3692831/SQL-Server-2005-Express-Edition---Part-4---Surface-Area-Configration.htm )

2) SQL Server instances supplemental to the initial (aka "root") instance will use Dynamic port assignment for its "listening" port.

3) The default listening port for the initial instance of SQL Server is 1433 EXCEPT for Express which uses dynamic port assignment for ALL its instances by default.

4) The default listening port for additional instances is "dynamic" for all versions.

5) Because of the differences in how SQL Server installs and seems to modify Windows Firewall for the machine SQL is installed on, DSD-Best-Practices is to follow these steps (assuming your goals want remote access to the SQL Server AND you are allowed to make these mods):

a) Install the SQL Instances you want.

b) Use the (2005) "SQL Server Surface Configuration" / (2008) "SQL Server Configuration" program to enable remote client access to the SQL Server. (For 2008 details see: http://msdn.microsoft.com/en-us/library/dd857537(VS.85).aspx note that for 2005 it's done a bit different using the Surface Config tool)

c) Use the (2005) "SQL Server Surface Configuration" / (2008) "SQL Server Configuration" program to specify a (different!) set/static port for each instance (e.g. 1433 for the root instance 2433 for the next instance). (Restart the changed instances to put this into effect).

d) Create a rule to open the machines (Windows) firewall for the ports used for all SQL instances.

e) Test access from a remote machine.

6) The SQL Server Browser Service acts as sort of a proxy server or DNS server on a machine with more than one SQL Server instance. It handles attempts of SQL client's trying to find/reach a SQL Server (instance) by listening on port 1433 (on the server the SQL Server instances are on) and bouncing the requests to the actual port that one of the instances is really answering on. Ergo, on some items (System data source name wizard) you shouldn't expect to see or enter the instance name for a SQL Server. For example, if you have a machine named MySQLServer and the root instance is "MySQLServer" and the second instance is "MySQLServer\IamNumTwo" then you will only see "MySQLServer" in the dropdown.

7) SQL Server Browser Service Reference: http://msdn.microsoft.com/en-us/library/ms181087.aspx

"We have met the enemy and he is us."

- Pogo

So I've spent the last two days going crazy trying to get Microsoft SQL Server 2005 Reporting Services working on my laptop. I was getting an ASP.NET error when I tried to hit the report server's Web user interface (i.e. http://localhost/Reports). My error (for the datasphere search engines) was along the lines of:

Server Error in '/Reports'
Application.

Parser Error

Description: An error occurred during the parsing of a resource
required to service this request. Please review the following specific parse
error details and modify your source file appropriately.

Parser Error Message: Could not load type
'Microsoft.ReportingServices.UI.GlobalApp'.

Source Error:
Line 1: <%@ Application Codebehind="Global.asax.cs" Inherits="Microsoft.ReportingServices.UI.GlobalApp" %>

Source File: C:\Program Files\Microsoft SQL
Server\MSSQL.3\Reporting Services\ReportManager\global.asax

Line: 1

Version Information: Microsoft .NET Framework
Version:2.....

After much grasping at thin straws from the short list of online references to this error, and getting nowhere, I had gone so far as to uninstall and re-install MSSQL Server 2005 and IIS twice. I then spent a ton of time:

1. Running Process Monitor to see if file or registry ACLs were preventing something from getting to what it needed.
2. Modifying ACLs on the ASPNET temporary files folder.
3. Modifying ACLs on the Report Manager folder.
4. Modifying Local Security Policy settings.
5. Comparing a working SSRS install on a W2K3 Server machine.
6. Copying SSRS files from a working machine.
7. Repeatedly running aspnet_regiis with various parameters
8. Playing with the GAC trying to get my stupid laptop to "understand" the parent assembly (i.e. ReportingServicesWebUserInterface.dll) it couldn't seem to find.
9. Swearing, begging, giving up, swearing some more.

Then, in the fashion such successful epiphanies seem to come in - i.e. randomly, sans connection to proceeding efforts - I remembered that on some work laptops we had had problems with Mortgage Cadence Orchestrator installs and had ended up modifying the machine Web.Config file (i.e. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\Web.Config) to remove a line that was causing MCO to fail to launch.


<configuration>
blahblahblah...
<system.web>
<authorization>
blahblahblah...
</authorization>

<browsercaps>
blahblahblah...
</browsercaps>

<clienttargets>
blahblahblah...
</clienttargets>

<assemblies>
blahblahblah...
<add assembly="*">
blahblahblah...
</assemblies>
blahblahblah...

This wildcard directive to load all assemblies in the Web application's folder caused the MCO app to try and load non-.NET DLL files that were in the MCO app folder. I never heard a good explanation as to why this caused problems on only some consultants' laptops. Regardless, our "quick-fix" to circumvent that problem was to delete that wildcard line in the machine's Web.config file (this was known as the "Web.config line 61 wildcard problem"). Yet, now when I returned that line to the laptop's Web.Config file - SHAZAM! - the report server started working. Two days gone due to short-routing around a problem many months ago. Life at times seems nothing but dealing with the unintended consequences of previous actions/decisions...and I'm talking not just about I.T., parenting, or rescued turtles.