The requirement was simple
enough: add a new, empty database to the same SQL Server instance as our SharePoint Foundation 2010 databases. (SharePoint Foundation 2010 can be installed to
use any existing SQL Server instance, but the default option installs SQL
Server 2008 Express as the database engine. It doesn’t install SQL Server
Management Studio.)

SQL Server gurus will tell me at
this point that this would have been easy enough with the sqlcmd command line utility, but that never entered my mind; I much prefer a
graphical user interface (GUI), so I wanted to use Management Studio. In any
case, other database management operations are so much easier for me with
Management Studio.

I could also have downloaded SQL Server 2008 Management Studio Express and installed it locally. I thought this
wasn’t necessary. I run SQL Server 2005 Management Studio on another server, so
I thought it should be easy enough to connect from there.

Disabled

In Management Studio, I clicked Connect
| Database Engine… in the Object Explorer. In the Connect To Server dialog, I
clicked the drop-down arrow next to Server Name and selected <browse for more…> because I couldn’t see the
name of my SharePoint server. In the Browse For Servers dialog, I clicked the Network Servers tab and waited for the right server name to appear on the list. It
didn’t.

Hoping for the best, I cancelled
that dialog and typed the server name into the Server Name box. On clicking OK and waiting a couple of seconds, I got a
“26” error. A search for this told me it meant the SQL Browser
service was disabled on the remote server. I enabled the service on that server
and tried again.

On my second attempt, I could
browse for the server name (actually it was <server name> | SharePoint,
where SharePoint is the instance name). On trying to connect, I got a
“28” SQL Server error, which apparently meant that “the protocol
is not supported.” So, I went back to Google. Following the
recommendations I found, I opened UDP port 1434 on the SharePoint server firewall,
and I got the same “28” error.

Pipes

Back to the drawing board, or
rather, the keyboard. I found another suggestion that I should enable TCP or
Named Pipes with the SQL Server Surface Area Configuration Tool, but that tool
wasn’t installed on the SharePoint server. There was, however, a SQL Server
Configuration Manager. When I tried to run it, I got yet another error message.
It began:

"Cannot connect to WMI provider. You do not have permission or the server is unreachable."

I was beginning to think it would
be easier to install SQL Server 2008 Management Studio Express. I pressed on
and found this
discussion
from which I gathered that SQL Server Configuration Manager
wouldn’t run because of a problem with Windows Management Instrumentation (WMI).
This problem could be fixed by compiling an .MOF file. Apparently MOF stands
for Managed Object Format and is associated with Microsoft’s System Management
Server. By this stage, I really didn’t care what it was, I just wanted to fix
my problem.

After trawling through the
discussion I found the .MOF file at:

C:\Program Files (x86)\Microsoft SQL Server\100\Shared

…and from that folder ran the
command:

mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

This finally allowed SQL Server
Configuration Manager to start.

In SQL Server Configuration
Manager under SQL Server Network Configuration | Protocols For SHAREPOINT, I enabled
TCP/IP and restarted the SQL Server (SHAREPOINT) service. And, yes, I could connect
from the remote SQL Server 2005 Management Studio and add my new database.

Summary

After overcoming four different
errors, I was able to connect from SQL Server 2005 Management Studio on one
server to an instance of SQL Server 2008 Express on another. There are probably
quicker ways of achieving what I wanted, but sometimes in IT, you get more
experience than you bargained for.