I'm trying to view some data held in a SQL Server 2008 database using MapServer. I can run the GetCapabilities requests without any errors, but when i try and run a GetFeature I get the following error:
<ServiceExceptionReport xmlns="http://www.opengis.net/ogc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.2.0" xsi:schemaLocation="http://www.opengis.net/ogc http://schemas.opengis.net/wfs/1.0.0/OGC-exception.xsd">
<ServiceException code="NoApplicableCode" locator="mapserv">
msWFSGetFeature(): WFS server error. ms_error->code not found msMSSQL2008LayerOpen():
Query error. Couldnt make connection to MS SQL Server 2008 with connect string 'Server=xxx;user id=yyy;password=zzz;database=db;trusted_connection=yes'.
Error reported was '[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "db" requested by the login. The login failed.'.
This error occured when trying to make a connection to the specified SQL server. Most commonly this is caused by
(1) incorrect connection string
(2) you didnt specify a 'user id=...' in your connection string
(3) SQL server isnt running
(4) TCPIP not enabled for SQL Client or server <br>
</ServiceException>
</ServiceExceptionReport>
When I use the same connection string in a VB.Net aspx page, I can get into the database and table I'm trying to access with MapServer, so I think the connection string is ok. I've enabled the TCP/IP protocols in the SQL Server Configuration Manager. I think this is a MapServer issue in that it can't get to my SQL 2008 database in the same way as my aspx page can.
I've also tried setting the SQL 2008 layer connection details using the "plugin" connection type and also as an OGR connection in my mapfile but without any luck.
Can anyone give me any pointers as to what to try next please?
Best Answer
Must learn to read the MapServer docs more closely. Turns out I needed to specifiy "pwd" and "uid" rather than "password" and "user_id" in the connection string for the layer in the mapfile....
http://mapserver.org/input/vector/mssql.html#option-2-connect-through-mapserver-plugin