SCCM 2007 OS Deployment SQL Database
When developing a Windows build via MDT it can be useful to connect to an external SQL Database to retrieve data from the MDT Database. But sometimes the connecting to the SQL Database can however be a problem… This is an old problem but I thought it may come in handy because I was not able to find any details regarding this behaviour online. Hopefully it will save someone some time.
I apologize up front, this is a rather technical post.
Usually the ZTIGather.wsf file in combination with the CustomSettings.ini and the ZTIDataAccess.wsf file establish a SQL connection string that allows a connection to the SQL server by connecting to a SQLShare using Integrated Security (SSPI) and Named Pipes (DBNMPNTW). This process is supposed to use the SCCM Action Account to make this connection and then retrieve the data that you have requested from the SQL Database.
[Settings] Priority=DynamicPackages, Default
Im my case, when initiating a BareMetal deployment with WinPE it was trying to connect using the ANONYMOUS USER account and with the Refresh deployment it was trying to connect with the Machine account.
I tried all the usual steps to try and resolve this issue:
- Create a new SCCM Action Account.
- Ensure that the Accounts have the required privileges in SQL.
- Testing using OBDC and Named Pipes that all the Accounts work.
- Ensure that ADO is enabled on the WinPE imag.
- http://support.microsoft.com/kb/938701 – Although this refers to the SQL Connection string using TCP/IP instead of Named Pipes, which my one is already doing (but I tried this as well)
- Ensured that the SQL Remote Connections allows both TCP/IP and Named Pipes
- Ensured that the SQL Browser is enabled.
I decided to take another approach, instead of resolving the issue with the SCCM Action Account to start with, what I would try and do it get it working first and then go back to look at the Action Account issue.
I created 2 new SQL Signin accounts: ‘NT AUTHORITY\ANONYMOUS USER’ and ‘<domain name>\Computer Accounts’ and granted them both permissions to the MDT database and the Stored Procedure – this worked without any issues, except now the databases are less secure.
I decided that I should try using the ‘out of box’ functionality of the MDT Database as well as using the custom connection for the Dynamic Packages stored procedure. What I found was that while the CSettings SQL connection that I added into the CustomSettings.ini used the SCCM Action Account, the Dynamic Packages connection still tried to use the ANONYMOUS or Computer$ account.
Parameters=UUID, AssetTag, SerialNumber, MacAddress
Once I realised this it was easier to address the problem.
This problem is not really documented and was unable to locate on the web, I am currently working with Microsoft to determine where the bug is, it could be MDT 2010 Update 1 scripts for the SQL connection string or just the nuance with using it in combination with SCCM 2007 R3.