Microsoft’s Deployment Toolkit (MDT) supports integration with SQL Server providing far better control over deployment options, eg Client A gets Task Sequence 1, whereas Client B gets Task Sequence 2, both are assigned their respective static IP's.
Previously I completed a comprehensive series on deploying MDT (here) including SQL Server Express integration and baulk import of client data into SQL.
In this article, I’ll address common connection issues that may arise between MDT and SQL Server and how to fault find those issues.
If you had followed the guides, the subsequent steps are likely unnecessary. Nevertheless, it is beneficial to offer guidance on diagnosing connection issues.
The current MDT server is equipped with SQL, but in my haste, I had overlooked certain post-integration steps. As a result, there is a noticeable delay at the 'CSetting' stage during the initial WinPE for client deployment.
Certain prerequisites must be met, including the establishment of a functional MDT server and the installation and configuration of SQL Express with the necessary connection settings listed in CustomSettings.
PXE boot a client to the point where it's possible to select a Task Sequence.
As WinPE offers limited diagnostic functionality and tools, it's back to the basics with Notepad and logs.
Press F8 to access the command prompt
CD to 'X:\MININT\SMSOSD\OSDLogs\' or execute the following command:
Notepad X:\MININT\SMSOSD\OSDLogs\ZTIGather.log
Near the bottom of the log search for SQL Connection errors:
ZTI error opening SQL Connection:
Unable to establish database connection using [CSETTINGS] properties
If you are not aware SQL uses the SQL Browser service on port UDP 1434 for application communications.
Two potential issues warrant investigation. First, verify that the SQL Browser service is configured to start automatically by accessing services.msc. The second issue involves checking UDP port 1434 in the Inbound firewall rules.
However, if you prefer to confirm the port, proceed with the following steps.
Utilize either wf.msc or gpedit.msc to set up Windows Firewall Public Profile logging for dropped packets only following the example below.
Restart and PXE the client to the Task Sequence window.
On the MDT Server launch Notepad with Administrative permissions and open:
C:\Windows\System32\Logfiles\Firewall\PFirewall.log
Search for the IP of the client. Note the dropped packets on 1434.
While on the MDT server, launch either gpedt.msc or wf.msc.
Add an inbound UDP rule to allow port 1434.
Return to the client, restart and then review the ZTIGather.log as previously demonstrated.
The error is pretty self-explanatory. The MDT Service account requires login and access rights to the MDT SQL Database.
Switch to the MDT Server and open SQL Server Management Studio.
Browse to Security then Logins.
Right click on Logins and select 'New Login'
If you followed the preceding installation guides, you likely created a service account to grant access to the MDT share and its credentials are listed in CustomSettings.ini and BootStrap.ini.
Add this account as a Windows Login to SQL.
Adjust the User Mapping by granting db_dataread access to the MDT database for the service account.
Review the ZTIGather.log after restarting the client for a final time and confirm the successful access to SQL.
The settings for clients included in the MDT Database will now take precedence over CustomSettings.