Reading through the QGIS forums there is very little information about connecting to MS SQL Server 2008 spatial. However it is possible to do this via the OGR ODBC driver that is compiled into QGIS by default. Here's how.
Reading up on OGR ODBC at http://www.gdal.org/ogr/drv_odbc.html reveals that OGR looks for a table in the database called geometry_columns. This table lists the tables and views and their associated spatial fields. Note that the OGR help file is a little misleading in that it quotes the definition of this table in upper case, whilst OGR makes all the SQL calls in lower case. Not a problem in a default case insensitive collation, but will not work on case sensitive collations.
Create the table in your database:
create table geometry_columns(
f_table_name varchar(50) not null,
f_geometry_column varchar(25) not null,
geometry_type varchar(25) null,
constraint pk_geometry_columns
primary key (f_table_name, f_geometry_column)
);
The field widths I assigned were fairly arbitrary, and the primary key is probably not required.
MS SQL Server 2008 SpatialSQL Server 2008 geometry columns are not in Well Known Binary or Well Known Text formats, therefore QGIS/OGR does not understand them. However, SQL Server does have built in function to convert geometries into these formats.
create view Q_MYTABLENAME_WKB as
select top 100 percent MYTABLENAME.*
,MYSPATIALCOLUMN.STAsBinary() as MYSPATIALCOLUMN_WKB
from MYTABLENAME;
Now insert a row into the table with information about your tables with spatial columns:
insert into geometry_columns(f_table_name,f_geometry_column,geometry_type)
select 'Q_MYTABLENAME_WKB','MYSPATIALCOLUMN_WKB','wkb';
I have been unable to determine exactly what to put into the geometry_type column, and it does not seem to matter. When connecting with QGIS it always lists the geometry_type as Unknown.
I have only done this on the Windows platform so that is what I will describe here.
Firstly use the Windows ODBC manager to create an ODBC connection to your database. In QGIS it is a simple matter of adding a vector layer and choosing ODBC as the source. Fill in the Server, Database and login details and select OK. You will then see a list of all the tables listed in geometry_columns. Select the one you want and away you go.
Determine what is required by QGIS/OGR in the geometry_type column. Perhaps somebody with the QGIS source code handy could take a look.
Determine if it is possible to set an SRID in the database and have QGIS pick it up. The OGR ODBC driver can accept a reference to an SRID column in the connection string, perhaps this needs to be implemented in QGIS.