SQL Server Configuration

< Previous section | Table of Contents | Index | Next section >

As noted in SQL Server Installation, the default installation procedures for SQL Server set things up so that SQL Server allows access to users on other computers via TCP/IP. However, they may have been disabled after installation. (Also some older versions of SQL Server have TCP/IP disabled by default.) Therefore, you may need to enable TCP/IP connections. To do so, follow these steps:

In the Windows Start menu, locate the entry for your version of SQL Server. Under this entry, locate Configuration Tools and then SQL Server Configuration Manager. This opens a window where you can configure your SQL Server. (In Windows 8, just search for SQL Server Configuration Manager from the desktop.)

If you can't find an entry for SQL Server Configuration Manager, you'll have to add the software. See "Maintenance Installs" below.

In the left-hand side of the window, expand the entry for SQL Server Network Configuration.
In the resulting expansion, click Protocols for MAINBOSS. (This will have a different name if you chose a different instance name.)
If MainBoss will be used on a network, with or without a domain, right-click on TCP/IP in the right-hand side of the window and click Enable (if this isn't already enabled). You do not have to do this if you'll only be using MainBoss on a single computer.

A message will appear saying that your changes will only take effect once you stop and restart the service. The steps below do exactly that.

The list of protocols also offers the possibility of enabling SQL Server access through named pipes. However, Microsoft recommends that you don't use named pipes across a network. See below for more on named pipes.

In the left-hand side of the window, click the entry for SQL Server Services. You should see an entry for SQL Server (MAINBOSS). (If you used a different instance name than MAINBOSS, that name will be displayed instead.) Right-click on this entry, then click STOP. (This will actually stop both instances displayed in the right-hand side of the window.)
Right-click on SQL Server (MAINBOSS) and click START.
Right-click on SQL Server Browser and click Properties. This opens a window where you can set the browser's properties.
In the Service section of the properties window, click Start Mode.
Drop down the associated arrow (at the end of the line) and click Automatic.
Click the window's Apply button.
In the Log On section of the same properties window, click Start to start the browser again.
Click OK to close the window.

Named Pipes: As noted above, you have the option of allowing SQL Server to use named pipes in addition to TCP/IP. We strongly recommend that you disable named pipes. There are several reasons.

Each named pipe counts against the maximum number of sessions that Windows allows, while TCP/IP connections do not. Therefore, using named pipes decreases the number of people who can use the Windows system for other purposes.
The default permission structure associated with named pipes essentially means that Windows Administrators can access SQL Server but nobody else can. It's possible to change the structure, but this is seldom done.
If you do enable named pipes (e.g. because some other software using SQL Server requires them), you'll have difficulties debugging any TCP/IP problems that might arise. The reason is that the .NET framework (which underlies MainBoss) attempts to contact SQL Server first through shared memory; this won't work if SQL Server is running on a different computer. Next, .NET tries TCP/IP, which is the preferred connection method...but if this fails for any reason, .NET immediately goes on to try named pipes. You will not receive any error message explaining what happened to TCP/IP. If named pipes work, they'll count as a Windows session (as discussed above), and if they don't work, you'll get an error message related to named pipes, rather than one telling you the problem with TCP/IP. In other words, named pipes simply interfere with your ability to get TCP/IP working properly.

As this discussion shows, we see no good reason for using named pipes and a number of reasons not to. Avoid them if possible.

Maintenance Installs: When your SQL Server was installed, it's possible that SQL Server Configuration Manager was omitted—its installation is optional. If you're missing the configuration manager, you'll have to do a maintenance install to add the software.

To do this, start the SQL Server or SQL Express installation again. Tell the installation procedure that you want to change the installation, then follow the suggestions given in Installing SQL Express.

Starting the SQL Server Browser: The SQL Server browser makes it possible for other computers to detect SQL Server on the server system. If the browser is not running, instances of SQL Server on this computer will not appear in the drop-down list for "Database Server" when you are specifying a maintenance organization. (See, for example, Creating a Maintenance Organization.)

By default, the browser is installed when you install the rest of SQL Server. For full versions of SQL Server, the browser is also set up to start automatically. However, for SQL Express, the browser is not set up to start automatically. To set it up that way, follow these steps:

In the Windows Start menu, locate the entry for the version of SQL Server you are using. Under this entry, locate Configuration Tools and then SQL Server Configuration Manager. This opens a window where you can configure your SQL Server.
In the left-hand panel, click the entry for SQL Server Services.
In the right-hand panel, examine the entry for SQL Server Browser. If the state is Running, the browser is already configured to be active. You can quit and skip the remaining steps below.
If the state is Stopped, you must set up the browser for automatic start. Right-click the entry for SQL Server Browser, then click Properties.
In the resulting window, go to the Service section.
Click the entry for Start Mode. Click the value associated with this entry; this opens a drop-down list. In the list, click Automatic.
Click OK.
When you return to the previous window, right-click the entry for SQL Server Browser. In the resulting menu, click Start.
Exit the Configuration Manager.

< Previous section | Table of Contents | Index | Next section >