Query the MDT Database via Web Services

When running a Zero-Touch SCCM/MECM/MDT imaging Task Sequence, it is common for the configuration data that is needed to enable Zero-Touch deployments to be stored in the Microsoft Deployment Toolkit (MDT) database.

Retrieving data from the MDT database is normally accomplished by adding sections to the customsettings.ini file that contains all of the information the MDT Gather process needs to query the database directly.

Sample customsettings.ini section using a direct SQL connection

[CSettings]
SQLServer=ServerName
Database=MDT
Netlib=DBNMPNTW
SQLShare=SQLShare
Table=ComputerSettings
Parameters=UUID, AssetTag, SerialNumber, MacAddress
ParameterCondition=OR

While this works relatively fast over a LAN connection, over WAN connections it is slower because establishing a SQL connection using Named Pipes, which is what is used when querying the database directly, is a lot heavier than performing a straight TCP/IP connection to the server.

In addition, you may not want to allow connections to the SQL server hosting the MDT database from everywhere in your environment. I have clients that lock down every SQL server so only certain devices can connect to it. This solution is a perfect fit for that scenario.

To speed things up, and limit what devices directly connect to the SQL server, you can instead query the MDT database using a web service call. This runs faster over WAN connections because the MDT Gather process uses the HTTP/HTTPS protocol, which is lightweight and fast compared to using Named Pipes. Plus, connections to the SQL database only has to be opened for the server hosting the web service. As an added benefit, the entries in customsettings.ini are much simpler.

The API is fully documented and supports both GET and POST operations. It doesn't matter which one you use as they both run the same SQL query on the backend. The SQL queries are identical to what MDT Gather sends when querying the database directly. Initially I only configured the API to respond to HTTP GET requests, as this complies with RESTful best practices. I added support for POST requests, even though best practices state POST requests should be used for adding new records, because as per the MDT documentation "A POST request to the web service is recommended, so the web service call must be able to support a POST". Presumably this was recommended by Microsoft because GET requests include the parameters in the query string, while POST requests include the parameters in the message body.

If you want to brand the Swagger UI, you can swap out my logo and replace it with your company logo. All you have to do is replace the logo.png file located in the wwwroot folder of the solution.

Before implementing the MDT Web API, you should be aware of the limitations imposed by the MDT Gather process. If Gather finds more than one value for a property, the MacAddress for example, only the first one found will be sent to the API.

This is a limitation hard coded by Microsoft into ZTIDataAccess.vbs. For the most part this shouldn't be an issue as most of the clients I've worked with use the Serial Number or Asset Tag to query the MDT database.

The only exception to this is Roles. It's common for a device to have more than one role associated with it, and the MDT Gather process will only send the first one it finds to the API. I've coded the API to get around this limitation. Refer to the API documentation in the Swagger UI for more information on how to query for settings based on Role.

Installation and Configuration

The MDT Web API is written in ASP.NET Core 8. To run the API on IIS, you first need to install IIS on a server. Then you need to install the .NET Core Hosting Bundle

It doesn't matter what port or protocols you assign to the site. The API will respond to both HTTP and HTTPS requests, on any port you configure in IIS. If using HTTPS (recommended), it doesn't matter if the SSL certificate is valid or not. Microsoft hard coded into ZTIDataAccess.vbs to ignore certificate errors because in WinPE, there is no Trusted Root Certification Authorities store.

Once you have IIS setup, copy the MDT Web API folder contents to the wwwroot folder in IIS. Open the appsettings.json file and change the SQL Connection string to match the connection settings required for the MDT database. If using a Trusted Connection to connect to the SQL database (recommended), change the account used by the IIS Application Pool. Whatever account you set the Application Pool to run under, is what will be used to query the MDT database. This account does not need to be the same account used when querying the database directly, it can be any standard service account. Just make sure the account has db_datareader rights to the MDT database

Make sure if you use non-standard ports that the customsettings.ini file is updated to reflect the port number. For example, if you set the web server to use HTTPS over port 8443, then the customsettings.ini should look like this:

[CSettings] 
WebService=https://servername.domain.com:8443/api/v1/CSettings 
Parameters=SerialNumber,AssetTag,UUID,MacAddress

Securing the API

If you configured IIS to allow Anonymous access, you're all set. If you want to secure the API and restrict it so only certain users can access it, disable Anonymous access and enable Windows Authentication. The MDT Gather process only supports Windows Authentication. After enabling Windows Authentication, grant the MDT Account if using MDT standalone, or the SCCM/MECM Network Access Account, rights to the web site. Don't forget to add yourself to the allow list so you can access the Swagger UI.

As always, test, test, test before putting into production.

Download: MDTWebAPI.zip

---

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.