SQL Server Integration with MDT
MDT is more capable when integrated with SQL Server or SQL Express for advanced deployment options providing far more control than is possible with CustomSettings. An example would be 10 different laptops assigned different Task Sequences by MAC address alone.
Before proceeding, MDT is required to be up and running, details can be found here on how to install and configure.
If you followed the previous 'MDT Install Articles' the MDT Virtual Server was configured for an internal network with a static IP. This needs either reconnecting to the Internet or following the instructions for offline installation (here).
Download SQL Express and save to 'D:\Media\SQL'.
Start the SQL 2019 Express installation and select 'Basic'.
Update the installation path to 'D:\Program Files\Microsoft SQL Server'.
Coffee time again....
Make a note of the connection string if you feel inclined.
Download and install 'SQL Server Management Studio (SSMS)' and save to 'D:\Media\SQL'.
Update the installation path to 'D:\Program Files (x86)\Microsoft SQL Server Management Studio 18'.
Restart the MDT Server at prompt.
If the MDT Server was temporarily connection to the Internet, revert the to 'Internal' and re-set the static IP.
At the Start Menu launch 'SQL Server Configuration Manager'.
Navigate to 'SQL Server Network Configuration' and set 'Named Pipes' to Enable.
Now navigate to 'SQL Server Services', select 'SQL Server Browser' and right click, 'Properties'.
Set 'Start Mode' to 'Automatic' and then start the service.
Launch 'Deployment Workbench', navigate to 'Advanced Configuration', right click on 'Databases' and 'New Database'.
Enter the following information:
SQL Server Name: MDT01 (hostname of server)
Instance: SQL Express
Network Library: Named Pipes
Select 'Create a new database' and name 'MDT'.
Enter the name of the MDT Deployment Share 'DeploymentShare$'.
The file share can be attained from either executing the PowerShell command 'Get-Fileshare' or right clicking the MDT Deployment share in 'Workbench'.
Click through the remaining parts of the wizard completing MDT's integration with SQL Express.
Right click on 'Database' and select 'Configure Database Rules'.
Un-check all but 'Query for computer-specific settings'.
Un-check all on subsequent pages.
Review the 'Summary' and complete the wizard.
Back in the main view of Deployment Work Bench, right click on the MDT Share name and 'Properties'. The Rules (CustomSettings) have been updated to include the Database integration information.
Finally the MDT Service Account requires read access to the MDT Database.
Connect 'SQL Server Management Studio'.
Navigate to 'Security' and then 'Logins'. Right click and 'New Login...'
Search for the MDT Service Account 'svc_mdtuser'.
Change the Default Database to 'MDT'.
Click on 'User Mapping' and select 'MDT'.
Then select 'db_datareader'.
To allow access to SQL from the client to open an inbound firewall on UDP port 1434. Type wf.msc at the command prompt to access the firewall snap-in.
In Part 2 the MDT Database will be populated on-mass with the hostname and mac addresses of nearly 100 clients.