BDD 2007 – Troubleshooting Database issues


I have lost count of the number of times I have been asked to help some troubleshoot their database issues, so I decided it was time to write a blog about it. This blog details the common issues faced when using the BDD configuration database. The first part details the common causes of database issues and the second recommends troubleshooting methods.

Common issues


The following section details common issues and recommended configurations for each.

Database Rules – Ensure that the appropriate database rules have been created. Database rules are specified using the deployment point rules tab. Configuration database rules can be created manually or using the Configure DB Wizard in the Deployment Workbench.


A typical database section should look similar to the following:



[CSettings]

SQLServer=SERVERNAME

Database=BDDAdminDB

Netlib=DBNMPNTW

SQLShare=logs

Table=ComputerSettings

Parameters=MacAddress

ParameterCondition=OR


The critical components of the database section are:



  • SQLServer – must specify the SQL server that hosts the BDD configuration database.
  • SQLShare – must be a valid share on the SQL server and the user account (UserID as specified in the rules) must have access rights to that share. If SMS OSD Feature Pack is used for deployment then the SMS Advanced Client installation account will require access rights rather than the user account.
  • Netlib – there are many issues with using TCP/IP sockets based connections so ONLY USE NAMED PIPES (DBNMPNTW).

Database connectivity issues – There are a number of issues that can stop the client connecting to the SQL server, these are detailed below:



  • Ensure that Named Pipes is enabled for remote connectivity on the SQL server, this is not enabled by default.
  • Has the surface area been configured to enable Named Pipes? When using SQL 2005 it is important to ensure that the “Surface area” is configured in such a way that remote connectivity to Named Pipes is allowed.
  • If you have issues accessing the database from the workbench then ensure that you are using Named Pipes. The Deployment Workbench always uses Named Pipes to connect to the database.

Security – has the deployment account got the appropriate database rights? The account used will need read rights to the BDD admin database. The account can vary depending on the deployment type:


  • ZTI – The SMS advanced client installation account.
  • LTI – The user account specified in the rules (bootstrap.ini).

SQL instances – If you are connecting to a database instance then this instance must be specified. This value is specified using the Instance field in the deployment workbench.

Troubleshooting methods


During the troubleshooting process it is critical that you can easily test the database rules. The following instructions detail how to manually test the database connectivity. The rules specified in the deployment point can be tested without running through the entire BDD build process. Rules are processed using a script called ZTIGather.wsf, this script can be run separately from the other BDD scripts allowing easy testing. The steps below detail the process required to perform manual rule testing. To properly reflect the deployment environment it is important to perform these tests from a client device:

  1. Create a folder on the client device and copy the following files from the deployment point to this folder:


  • ZTIGather.wsf
  • ZTIGather.xml
  • ZTIUtility.vbs
  • CustomSettings.ini

  2. Delete C:\MININT directory if it already exists. This folder can also be located at X:\MININT if the C drive is not available.


NOTE: BDD stores configuration and progress information in the MININT folder, if this folder is not removed between tests then the results will be invalid.

  3. From the command prompt navigate to the newly created folder and execute the rule processing script using the following command:


“cscript.exe ZTIGather.wsf /debug:true”


      The script will then be processed and the results outputted to the command prompt and a log file ( .\MININT\SMSOSD\OSDLOGS\ZTIGather.log)

NOTE: The script can be run within Windows PE or the host operating system.

  4. Review the results of the script.

The following screen shots show five examples of the output that you will see from this process. The first three screen shots show general database lookups while the other two show common errors you are likely to see.

This screen shoot shows a successful connection to the database. The computer record is then queried based on the MAC Address and the OSINSTALL, COMPUTERNAME and MACHINEOBJECTOU values are returned.

ScreenShot1  

The next two screen shots show a database lookup based on the make and model of the client device. In the first screen shot shows a database lookup based on a Virtual PC client where the model is a “Virtual Machine” and the make is “Microsoft Corporation”.  This record does not exist in the database and the log reflects this – “Record count from SQL = 0”. I then created a record in the database using this information and assigned an application to the model type via the deployment workbench. The script is then rerun and and the lookup successfully returns the application. (Second screen shot).


SnapShot2


SnapShot3

NOTE: Applications are identified as GUID’s in the BDD logs. To determine which application is represented by the GUID you should reference the applications.xml file.

The following screen shot show the results of a database look up where the user account does not have rights to the database. The important thing to note is the error message – “login failed for user ‘WOODGROVE\BDDBuild'”. Remember that the user account must have at least read rights to the BDD configuration database.

SnapShot4


The following screen shot show the results of a database look up where named pipes has been disabled. The important thing to note is the error message, “SQL Server does not exist or access denied”. This problem can be caused by a number of issues, please refer to the database connectivity issues point above.


SnapShot5


Hopefully this will help get you started with troubleshooting your BDD configuration database issues.


For further information on rule processing please refer to this blog.


Disclaimer: The information on this site is provided “AS IS” with no warranties, confers no rights, and is not supported by the authors or Microsoft Corporation. Use of included script samples are subject to the terms specified in the Terms of Use.

Comments (28)

  1. Anonymous says:

    Many of you may have read my colleague Ben Hunter’s blog entry for troubleshooting connectivity issues

  2. Anonymous says:

    Many of you may have read my colleague Ben Hunter’s blog entry for troubleshooting connectivity issues

  3. Anonymous says:

    G’day Ben,

    Thanks for a truly great site, you knowledge has helped me out a great deal in understanding and implementing BDD.

    I’m having an issue with a BDD deployment using Lite Touch. I’ve setup a Database on an SQL 2005 cluster server, but I cant query the database from Windows PE using the above methods.

    When the script tries to map to the SQL share I get:

    ‘Validating connection to \sqlservershare’

    ‘FindFile: The file OSDConnectToUNC.exe could not be found in any standard locations’

    ‘Find File: The file Wizard.hta  could not be found in any standard locations.’

    ‘Error – unable to find Wizard.hta, so it is impossible to prompt for credentials’

    Then the script tries to open a trusted SQL connection and fails with:

    ‘ZTI error opening SQL connection: [DBNETLIB][ConnectionOpne (Connect()).]SQL Server does not exist or access denied. (-2147467259)’

    ‘Unable to establish database connection using [MMAPPS] properties.

    I dont claim to know much about SQL, but I’ve checked and had a DB Admin check that the server is definetly enabled for named pipes, the Domain account specified in the CustomSettings.ini has read,write and execute permissions on the database. The SQL server name is resolvable from the Windows PE environment.

    I can how ever query the database logged into a Windows XP machine with the same specified account, using the above methods.

  4. Anonymous says:

    Hi Ben

    I have this strange problem where during refresh (using MDT and SMS/OSD) i get a timeout while OSDconnectToUNCpath tries to establish at connection to the defined SQLshare=ZTI$. What happens then I that I get a "Login failed for user domainpcname$" while connecting to the SQL database. This results I no DB data being collected by ZTIgather.wsf and therefore no language settings, package installation etc. The really strange thing though, is that I created a workaround where I rerun ZTIgather.wsf as the first step in Validation, and then the data is collected without problems, and the installation runs as expected. Any ideas to why this is would be greatly appreciated

    Michael

  5. Anonymous says:

    Can you please email me a copy of the logs, "ben dot hunter at microsoft dot com".

    Thanks,

    Ben

  6. Anonymous says:

    Hy Ben,

    Great Blog,

    Did you encountered an error where the BDD tries to use the Machine account to access the DB ?

    I get an access denied (of course) but the logs specify only the machine account (Using LTIRefresh)

    Or.

    ort@yside.com

  7. Anonymous says:

    Can you speculate why there are no Record Counts in the SQL Server ?

    I’ve went over the setting time after time with no answer

    Or.

  8. Anonymous says:

    Could you please email me a copy of the full ztigather.log file. My address is ben dot hunter at microsoft dot com.

    Thanks,

    Ben

  9. Anonymous says:

    Unfortunately it is not possible to use wild cards directly. Although you could get creative with userexit scripts and create this functionality.

    Thanks,

    Ben

  10. Anonymous says:

    I am very happy with the progress of the deployment guys blog so far. Hopefully you all agree that we

  11. Anonymous says:

    I have seen this but only when there are no ther credentials availablke for the database lookup to use. Have you spoecified userid settings in the bootstrap.ini file?

    Thanks,

    Ben

  12. Anonymous says:

    Hi Jim,

    BDD/MDT should only change the credentials if you update the DOMAINADMIN user settings and password.

    Thanks,

    Ben

  13. Anonymous says:

    The SQLShare value is used to connect to authentication to the server. The SQLShare can be any share on the server.

    Have a look at the log file C:minintsmsosdosdlogsztigather.log for more information.

    Thanks,

    Ben

  14. Anonymous says:

    I would need more information to help you with this issue. This would include your customsettings.ini file and the logs.

  15. Anonymous says:

    Hi Jared,

    That is normal. BDD will try a number of connectivity methods and as one succeeds then everything is god 🙂

    Thanks,

    Ben

  16. Anonymous says:

    I am pretty sure that BDD 2007 will not display check marks either.

    Thanks,

    Ben

  17. Anonymous says:

    Hi Kongkuba,

    Have you given the advanced client access account rights to the BDD database?

    Thanks,

    Ben

  18. Anonymous says:

    There are three "spare" fields in the BDD DB. ServerA ServerB and ServerC you could use these fields.

    Other than that if you change the BDD DB the changes will ot be reflected in the Workbench.

    Thanks,

    Ben

  19. Anonymous says:

    I am not sure what you mean with this question. Can you elaborate a little?

    Thanks,

    Ben

  20. Anonymous says:

    Sorry, i was using ZTIRefresh when it happend

    And also, i can’t seem to use the instalaltion CD, it keep telling me "Access Denied" "Path not found" stuff

    Or

  21. OysteinK says:

    Using ypur troubleshooting tips i found that my database always returns "Record count from SQL = 0"

    What process moves the computer information into the database? Seems like mine is empty

  22. Garry Trinder says:

    Ben,

    I am trying to use the make and model in the database to install applications but I cannot get this to work.  Basically, there is no error message and the install just finishes.  It appears as if it doesn’t query the database.

    It looks like all the settings are correct in the rules tab, however I do not have SQLSHARE= defined.  Is that required?  If so is it just a share on that server and what permissions for what user ID are required?  Here are my rules properties:

    [MMSettings]

    SQLServer=SQLSERVERNAME

    Database=DATABASENAME

    Netlib=DBNMPNTW

    Table=MakeModelSettings

    Parameters=Make, Model

    [MMApps]

    SQLServer=SQLSERVERNAME

    Database=DATABASENAME

    Netlib=DBNMPNTW

    Table=MakeModelApplications

    Parameters=Make, Model

    Order=Sequence

    Lastly are there any logs that I can use to find out why it is not working (somehow I think the answer has to do with not having SQLSHARE defined).

    Thank you

  23. Hartmut Henk says:

    Ben,

    I am using the Database to preconfigure the client settings and realized, that not all of the configuration options that you can e.g. specify in MyCustomSettings.ini are available in the database. The option BDERecoveryPassword for example cannot be configured through the database.

    I already tried, using an SQL Client to manually add a line to the corresponding table in th db. However, the option did not appear in the DB view in deployment workbench.

    How can I extend the DB to include settings, that are not available out of the box, or is this not possible at all?

    Thanks,

    Hartmut

    p.s.: By the way – great blog! It helped me alot so far!

  24. Ben, when running the test to test for connectivity and to see if results are returned, I am getting an error

    ERROR – Unable to find Wizard.hta, so it is impossible to prompt for credentials.

    I get a positive result back about connecting to the sql server and it says it returned records=1 as result

    any thoughts, or is this normal when running from this method?  if this is not normal, and idea why i am getting this ?

  25. Ben, thanks for that info. i guess im confused as to why the wizard is not displaying the applications selection I have in the database when I run the wizard on a machine that has make and model info.  I get "check" marks in the applications that are to be installed on that particualr make and model as I did wiht the BDD-2007? any suggestions ?  

  26. Ben, I can confirm that the bdd-2007 does populate the applciation section from the databse with checkmarks. I do it all the time and it does do this. It must somehow return the results to the windows deployment wizard after it gathers the info from the databse..

  27. Martin says:

    Hi Ben.

    Is it possible to use wildcards in make and model? Like Make=Hewlett-Packard Model=*

    Thanks

    Martin

  28. jim says:

    Hi Ben

    I have a strange issue where the my domain joining credentials are changing in the middle of the process.  My unattend.xml has the correct information in it but the bdd.log shows that somewhere along the way it changes it back to the credentials I supplied in bootstrap.ini to gain access to the deployment point share.

    Thanks

    Jim