SQL Pinhole for WSS/MOSS Installation at 6:34 PM

I am a big supporter of a multi-tiered environment when developing large applications especially in a corporate environment.  I don’t just mean having different class libraries for data access/business logic I mean true physical separation between the web server and the database server.  When I installed a quick sandbox site of WSS for work I just let the installation steps guide me through the installation.  Apparently with WSS 3.0 this creates an instance of Windows Internal Database.  This is even more stripped down than SQL Server Express edition I guess!  But, I was still able to connect to it using SSMS… so it wasn’t a total loss.

When creating the production environment I wanted to do it right.  I wanted SQL to run on our corporate DB server tuned for database activity and WSS to have a dedicated web box configured specifically for serving up web applications in ASP.Net.  A simple install proved that this was not easy… just specifying the database server on install was not enough, especially since this was not a default instance of SQL server but one of many.  I found a document buried in the MSDN library indicating that I needed to create a “Pinhole” for SQL to be seen by WSS.  Here is what I did:

First I needed to create a System DSN for the SQL server instance giving it simply the name of the server (no port number on it either):

Second on the next screen you will need to click the “Client Configuration…” button:

 

Third you will need to add the port number of the instance created (make sure TCP/IP is selected):

If you are unsure of the port number an SQL instance is using you will need to open up SQL Configuration Utility and browse to the TCP/IP configuration window and look at the second tab bottom.  There are two fields for ports here “TCP Dynamic Port” and “TCP Port”.  The Dynamic port is the one you want unless you need to set your own port… if so change the “TCP Port” field and click OK… you will need to restart the SQL services for this to take hold.

When done setting the port click OK and step through the rest of the DNS configuration wizard.  Test the connection to make sure that it works.  Now all you will need to do in WSS is specify the server name exactly how you did on the first screen on the DSN wizard and all should be good!