Using the MDT database from a web service without writing code

One of the new features in the .NET Framework 3.5 SP1 is ADO.NET Data Services.  This enables you to expose the contents of a data source, e.g. a SQL Server database, through something that looks roughly like an RSS feed, accessed in a similar manner to a web service.  That’s nice but why do you care as an IT pro?  Well, it’s a convenient way of making the contents of the MDT database available to programs or scripts without forcing them to use ADO to access SQL Server directly.

The best part of ADO.NET Data Services:  You really don’t need to write any code.  Just walk through a few Visual Studio 2008 wizards and you’re done – almost.  There are two lines of code that I added, one to day that all the selected tables and rows can be accessed read-only, and a second that generates detailed errors if something doesn’t work.  The basic process is described at https://msdn.microsoft.com/en-us/data/cc745957.aspx.  (So I lied – you have to write two lines of code.)

The harder part of this is deploying the resulting ADO.NET Data Services project to an IIS server.  You need to have .NET 3.5 SP1 installed, then IIS and ASP.NET need to be installed.  You might need to run “ServiceModelReg.exe –i” to get the ADO.NET Data Services and WCF logic registered in IIS, see https://msdn.microsoft.com/en-us/library/ms732012.aspx for details.  You will definitely need to edit the database connection string in the Web.Config file to point to your server (and optionally the instance) as well as the database (Initial Catalog) that needs to be used.  And you might need to grant access to SQL Server, the database, and the database tables and views.  (See https://msdn.microsoft.com/en-us/library/ms998320.aspx if SQL Server is on the same machine.)  Really, it’s not that bad :-)

The actual ADO.NET Data Services files need to all be dropped in a directory.  After doing this, set up an application in IIS that points to this directory.  At that point, if everything is set up right, you should be able to access the the web service via a browser.  To test it out, try a URL like this:

https://yourserver/YourApplicationName/MDTDatabase.svc

If that gives you a list of objects available in the database (you might need to tell IE not to display the result in RSS Reader view to see the real contents – in IE8, that’s configured on the “Content” tab from the “Feeds and Slices” settings dialog), you know at least IIS, .NET, and ADO.NET are fine.  Then try a more specific URL to request all the computer records:

https://yourserver/YourApplicationName/MDTDatabase.svc/ComputerIdentity

That should result in something that looks like this:

image

 

Yes, kind of weird looking, but pretty easy to consume in a script.  So that’s the next step – making use of this data.  More on that in the next posting.

The full solution (Visual Studio project, source, binaries, etc.) is attached.

MDTDatabase.zip