Connecting to a remote instance of SQL Server 2008 Express from SQL Server 2005 Management Studio sounds easy, but Mark Pimperton had to overcome a series of errors before it would work.
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.
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.
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.
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.