Monthly Archives: February 2014

Simple ways to test SQL connectivity

While installing Configuration Manager 2012 R2, you may have a problem connecting to the remote SQL server.  Here are some ways you can use to test remote SQL connectivity from your Primary SCCM server:

  1. Telnet to the SQL server’s listening port on 1433.  This approach will require a telnet client.  To install the native telnet client on Windows 7/8.1, run the command "OptionalFeatures.exe" and then place a checkmark next to “Telnet Client” and click on OK.
    OptionalFeatures.exe
    To use telnet.exe to connect to the SQL server’s port 1433, open a command prompt and type "telnet.exe <sqlserverhostname_or_ip> 1433".  If connection fails, troubleshoot your SQL server accordingly.  A successful connection will appear as a blank screen:
    telnet1
  2. Create an ODBC System DSN to see if connection is successful.  To add a “System DSN” open the ODBC Data Source Administrator by running the following command "C:\Windows\System32\odbcad32.exe" (64-bit) or "C:\Windows\SysWOW64\odbcad32.exe" (32-bit).  Then click on the “System DSN” tab and click on “Add…”.
    odbc1
    Select “SQL Server” and click on Finish.
    odbc2
    In the “Create a New Data Source to SQL Server” window, name the data source anything you want as we won’t be saving this connection – it’s just a connectivity test.  In the “Server” field enter your SQL server’s hostname or IP.
    odbc3
    Leave the next screen on defaults and continue.
    odbc4
    Again, leave the next screen on defaults and continue.
    odbc5
    The next screen you can also leave on defaults and click on Finish.
    odbc6
    In the configuration summary window, click on “Test Data Source…”.
    odbc7
    If test was successfull, you will see the below message.
    odbc8
  3. Last but not least and probably the less intrusive way of testing SQL connectivity is by use of Universal Data Link files (.UDL).  This method does not require the installation of a telnet client or has as many steps as the “System DSN” method and support for this method is built into every windows version.  You can delete the file afterwards to “clean-up” the system.  Right-click anywhere on your desktop and select “New”, then “Text Document”.
    udl1
    Name the text document anything but make sure to change the “.TXT” to “.UDL”.  Then double-click on the resulting file.
    udl2udl3
    Double-click on the .UDL file and type the hostname or IP of your SQL server, then select a user account with permission to the SQL server and select a database.  Then click on “Test Connection”.
    udl4
    If connectivity test was successful, you will receive the below message:
    udl5

This isn’t an exhaustive guide to testing SQL connectivity.  Your mileage may vary depending on what the root cause of your connectivity issue, but these steps will certaintly start you off in the right direction.

How to create a collection of computers by installed software.

We’ve all been there, needing to create collections of computers by installed software in SCCM.  Sometimes even needing to list computers that have two or more installed software on them.

So how do you create these collections?  Read on below…

For this example, I am going to create a collection that will list all computers with “iTunes”.  Then I will show you how to add multiple software.

  1. In “Assets and Compliance” go to your Device Collections and right-click and choose “Create Device Collection”.
  2. In the “Create Device Collection Wizard” enter a name for this new collection and you will want to limit the search to either “All Systems” or another collection of your choosing.  I have about 3000 clients in the All Systems collection so I will choose another smaller collection for this example.
    2014-02-20_11-57-31
  3. In the next screen, click on “Add Rule” and then click on “Query Rule”.
    2014-02-20_11-58-36
  4. In the “Query Rule Properties”, enter a name for this query, “All computers with iTunes” and then click on “Edit Query Statement..”
    2014-02-20_12-01-57
  5. In the next window, you’ll want to click on “Show Query Language” and then copy/paste the below code and click on OK:
    select
    SMS_R_SYSTEM.ResourceID,
    SMS_R_SYSTEM.ResourceType,
    SMS_R_SYSTEM.Name,
    SMS_R_SYSTEM.SMSUniqueIdentifier,
    SMS_R_SYSTEM.ResourceDomainORWorkgroup,
    SMS_R_SYSTEM.Client
    from
    SMS_R_System
    inner join
    SMS_G_System_ADD_REMOVE_PROGRAMS
    on
    SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
    where
    SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "iTunes%"
    and
    SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "%" order by SMS_R_System.Name

    2014-02-20_12-03-35
  6. Once back in the “Query Rule Properties” window, click on OK to close and go back to the “Create Device Collection Wizard” where you can add more direct rules, query rules or include/ exclude collections.  For now, click on Next.
    2014-02-20_12-04-34
    2014-02-20_12-06-10
  7. In the Summary step, click on Next.
    2014-02-20_12-06-23
  8. Once complete, click on Close.
    2014-02-20_12-06-45
  9. You will notice that the icon for your new collection is refreshing and that your member count will be “0”.
    2014-02-20_12-07-31
  10. Wait a few moments before right-clicking and selecting “Refresh” or press F5.
    2014-02-20_12-11-58
  11. All done!
    2014-02-20_12-09-50

To add additional software to the collection, you can add a separate query to the collection’s properties by modifying the line in the code which specifies the software to search for, but keep in mind this will result in an “OR” operation meaning it will list computers with either one or the other.  If you want an “AND” operation then just modify the search criteria on the existing query:

  1. Right-click on the collection and click on Properties.
  2. In the ‘Membership Rules” tab click on Edit.
    2014-02-20_12-41-59
  3. You can update the query name in the “Query Rule Properties” window or just click on “Edit Query Statement…”
    2014-02-20_12-42-31
  4. Click on the “Criteria” tab and then select the toolbar button which looks like a yellow star.
    2014-02-20_12-43-40
  5. Choose the “Installed Applications” attribute class and the “Display Name” attribute and then click on OK.
    2014-02-20_12-44-53
  6. Change the operator to “is like” and then type the software title in the “Value” input box and make sure to include the wildcard “%” (not required if you want a specific title and are sure of the spelling).
    2014-02-20_12-46-19
  7. You will notice that the new software title has been added with an “AND” operator.  You can also change the operator to an “OR” using the button second from last which reads “&|”.  Click on OK.
    2014-02-20_12-47-59
  8. Back at the “Query Rules Properties” click on OK to close this window and then click on “Apply” or “OK” in the collection properties window to save the new changes.
  9. Again wait a few minutes before refreshing and it should update the collection membership as long as it finds the specified software on other computers.
    2014-02-20_12-52-18

South Florida System Center Users Group – LinkedIn

If you are a System Center professional living in South Florida or are interested in networking with a System Center professional in South Florida, make sure to join our user group on LinkedIn.

It was started on January 30, 2014 and we are planning on meeting at least twice a month with permission to use Microsoft’s Fort Lauderdale campus and other venues to meet.

For any questions, please contact the group’s owner, fellow colleague, Balaji Karrupuswamy or feel free to browse directly to the group’s link.

See you soon!

System Center Universe 2014

I know it’s been almost a month since System Center Universe 2014 was held in Houston, TX.  There were many industry experts that served as guest speakers, a few names I’m sure you all have heard, Kent Agerlund from Coretech and Wally Mead from Microsoft’s Configuration Manager team.

While all presenters entertained us with a wealth of information, industry tips and tricks and excellent demos, the very first presentation by Kent Agerlund stood out the most to me.  His presentation titled “Become the Hero in the Datacenter” (link to his PowerPoint deck), featured all open source tools meant to making the administration of your SCCM infrastructure much easier.  Make sure to check out his blog entry for a listing of all tools he discussed during his presentation.

If you did not attend either in person or via live stream, I would suggest to not miss next year’s session as there is a treasure trove of information that is discussed.

Recordings: To view the presentations from this year’s conference, please view thevideo playlist.

PowerPoint Presentations: To download the speakers’ PowerPoint presentations, click here.

Manage Microsoft Office 2013 volume licensing via command-line.

Microsoft Office 2013 ships with a great utility to manage volume licensing in the form of VBScript.  You can use it to manage volume licensing for the local computer or even a remote computer; provided you enter credentials with administrative access to the remote system.

This VBScript is called OSPP.VBS and is located here:

%ProgramFiles%\Microsoft Office\Office15\OSPP.VBS

To run this file, open a command prompt in administrator mode and change directory to the above path.

Some common switches I use are below:

  • Activating a license
    cscript.exe ospp.vbs /act
  • Resetting a license
    cscript.exe ospp.vbs /rearm
  • Status of a license
    cscript.exe ospp.vbs /dstatus
  • Input a new key
    cscript.exe ospp.vbs /inpkey:<KMS/MAK volume key>
  • Removing a license (You need to get the last 5 characters of the license to be removed via /dstatus)
    cscript.exe ospp.vbs /unpkey:<last 5 of volume license to be removed>
  • Manually set KMS host
    cscript.exe ospp.vbs /sethst:<FQDN of KMS host>
  • Manually set KMS host port
    cscript.exe ospp.vbs /setprt:<port - 1688 is default>
  • Manually configure DNS domain in which KMS SRV records are found
    cscript.exe ospp.vbs /skms-domain:<FQDN>
  • Of course if you need help, you can always display the full switch list by running
    cscript.exe ospp.vbs

You can also combine several commands into one continuous command to achieve several actions.  For example, to manually configure a KMS host, port and activate, use the following command:

cscript.exe ospp.vbs /sethst:kmshost.domain.com & cscript.exe ospp.vbs /setprt:1688 & cscript.exe ospp.vbs /act

Office15_KMS

Also, here is a link to the KMS client setup keys for VL Office activations.  I hope this aids you with replacing and deleting volume licensing without having to reinstall the entire Microsoft Office Suite.

Microsoft Office 2013/ Windows 8.1 KMS not working? Install KB2885698 to your KMS host!

If you’re in an environment that is just starting to adopt Windows 8.1, Microsoft Office 2013 and Windows Server 2012 R2, make sure you install KB2885698 to your Windows Server 2008/ 2012 or 7 KMS host to add support.

While were on topic, remember to use a valid KMS Client Setup Key when attempting to activate.

The command to activate is:

slmgr.vbs /ipk <setup key>

Downgrade/ Replace Microsoft Office 2013 Professional Plus to Standard

This post will address the second requirement mentioned in my previous post.  In summary, we detected unauthorized installations of Microsoft Office 2013 Professional Plus and now we need to uninstall and replace these rogue installations with the Standard version.

For this tutorial, I am going to assume you already know how to create collections and work with queries as well as task sequences.

I am also only going to focus on the MSDN media types, but you can adapt the steps for the Retail versions by substituting the MSI’s for Retail instead of using the MSDN ones.

Collection

We need to create a collection of all the computers that currently have Professional Plus installed.  We will then script out a task sequence to uninstall Professional Plus (MSDN and Retail) and install Standard in one motion because if we run the uninstall separately, the computers will drop off the collection as they are being uninstalled.

This is because we will create the collection using a query instead of direct memberships since the latter will take forever to input, not to mention, I find it very difficult to maintain.

Create a collection and add the following query:

select
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client
from
SMS_R_System
inner join
SMS_G_System_ADD_REMOVE_PROGRAMS
on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId
where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "Microsoft Office Professional Plus 2013%"
and
SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "%" order by SMS_R_System.Name

That’s it for this step.

SilentUninstallConfig.xml

Create a XML file named “SilentUninstallConfig.xml” and save it in your “[Product Code].ww” folder in the root of your installation source files. The contents of SilentUninstallConfig.xml is below:

<Configuration Product="Standard">
<Display Level="none" CompletionNotice="no" SuppressModal="yes" AcceptEula="yes" />
</Configuration>

Task Sequence

We will now create a task sequence which will be advertised to the above collection.

  1. In the Administrator Console, go to your task sequences and right click and create a new custom task sequence.
    TS_Office15_1
  2. Make sure to name the task sequence something descriptive, mine is “Microsoft Office 2013 Standard”.  Then click Next all the way until the task sequence is created.
    TS_Office15_2
  3. Right-click and Edit the newly created task sequence.
  4. Create two folders for organizational purposes.  The first folder will group the uninstall commands for Professional Plus.  The second folder will group the install commands for Standard.  For the uninstall group command lines, you will need to have access to each media type that you want to target because you will need to import each respective MSI as a condition to each step.
    TS_Office15_3
  5. In the uninstall group, we will add the first step to detect and uninstall the MSDN version of Professional Plus x64.  Add a “Run Command Line” with the following command:
    cmd.exe /c \\[Path to ProPlus installation source]\x64\setup.exe /uninstall ProPlus /config \\[Path to ProPlus installation source]\x64\proplus.ww\SilentUninstallConfig.xml
    TS_Office15_4
    If you’re using Retail media the “ProPlus” must be substituted with “ProPlusr”.
    Add a “Run As” account that has at least ‘Read” permission to the installation source files because task sequences run as the “Local System” account and will not have access to the network share.
    In the “Options” tab, click on “Add condition” and click on “Operating System Version”:
    TS_Office15_6
    Again in the “Options” tab, click on “Add condition” and then click on “Installed Software”.  You will now need to browse to the installation source files and import the MSI inside the “ProPlus.ww” folder:
    TS_Office15_5
    When finished, the conditions for this step should look like this:
    TS_Office15_7
  6. If you want to detect and uninstall the x86 version of Professional Plus, repeat step 5 and use the following command:
    cmd.exe /c \\[Path to ProPlus installation source]\x86\setup.exe /uninstall ProPlus /config \\[Path to ProPlus installation source]\x86\proplus.ww\SilentUninstallConfig.xml
    Again, if you’re using Retail media the “ProPlus” must be substituted with “ProPlusr”.
    For the x86 version you only need to add one condition because the x86 version can also be installed in x64 operating systems.  Add the “Installed Software” condition and import the x86 MSI.
    When finished, the ‘Options” tab should look like the following:
    TS_Office15_9
  7. For the Retail versions, repeat steps 5 and 6.  The only thing that changes is you have to import the specific MSI for the retail versions.  You don’t need to point the command lines to each Retail’s media setup.exe.  The ProPlus setup.exe is sufficient, but you will need to change the “ProPlus” to “ProPlusr” in each command line right after the “/uninstall”.
  8. Once finished with the uninstall commands, let’s turn our attention to the install group.  Add a “Install Application” step and select the Microsoft Office 2013 Standard application we created in the previous post.  This step does not need conditions because it will install on both x86 and x64 architectures.
    TS_Office15_10
  9. Optional:  In my organization, we have a problem with Windows 8.1 systems which are unable to read the SRV records for our KMS in our local DNS.  Therefore, Microsoft Office 2013 Standard installs but doesn’t activate.  We can add two “Run Command Line” steps to manually point each installation to the KMS server and activate against it.
    The first command line is meant to activate Standard x86 on a 64-bit Operating System.  It is composed of the following command line:
    cmd.exe /c cscript.exe "%ProgramFiles(x86)%\Microsoft Office\Office15\ospp.vbs" /sethst:[FQDN of KMS Server] & cscript.exe "%ProgramFiles(x86)%\Microsoft Office\Office15\ospp.vbs" /setprt:[Port number; default is 1688] & cscript.exe "%ProgramFiles(x86)%\Microsoft Office\Office15\ospp.vbs" /act
    TS_Office15_11
    It has the following conditions, “Operating System” is set to “All Windows 8 64-bit” or “All Windows 8.1 64-bit” and Registry Key “HKLM\SOFTWARE\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-0012-0000-1000-0000000FF1CE}” with value “DisplayName” exists.
    TS_Office15_12

    The second command line is meant to activate Standard x86 on a 32-bit Operating System.  It is composed of the following command line:
    cmd.exe /c cscript.exe "%ProgramFiles%\Microsoft Office\Office15\ospp.vbs" /sethst:[FQDN of KMS Server] & cscript.exe "%ProgramFiles%\Microsoft Office\Office15\ospp.vbs" /setprt:[Port number; default is 1688] & cscript.exe "%ProgramFiles%\Microsoft Office\Office15\ospp.vbs" /act
    TS_Office15_13
    It has the following conditions, “Operating System” is set to “All Windows 8 32-bit” or “All Windows 8.1 32-bit” and Registry Key “HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{90150000-0012-0000-1000-0000000FF1CE}” with value “DisplayName” exists.
    TS_Office15_14

Once complete, deploy the task sequence to the above created collection and it should uninstall Microsoft Office 2013 Professional Plus and install Microsoft Office 2013 Standard x86.