SQL

Likely a query I wrote to support another tool.

Is your SCCM SQL stuck in Evaluation mode? Don’t despair!

FoxDeploy1 Comment

Have you ever wondered what happens when you install SCCM 2012 or 2007 on top of SQL and choose ‘Evaluation mode’, then forget to enter the SQL key?

SQL will turn off the SQL Database Service on day 180, and never turn back on until you license it

How’s that for getting your attention?

Recently at a client, my contact was searching for their install keys, and promised to provide them later. No problem, I said, and proceeded to install both ConfigMgr 2012 R2 and SQL 2012 SP1 in evaluation mode. Typically what would happen here is that after a week or so, the VAR would get back to us with some keys…This time however…

The big rub was that this was an educational institute. They are sold licenses in different quanities and at different rates than your typical enterprise or SMB, the sorts of customers I deal with much more often. Those larger firms often buy a bundle of SQL licenses, and I’d just use one of them, which would get re-added / comped to their license pool the next time they re-upped their licenses. Schools don’t typically need SQL Server, relying instead on Postgresql or MySQL (shudder) or even worse, Oracle.

I contacted Microsoft about my dilemma and posted online, I was told that System Center does include a special SQL installer that won’t prompt you for a key, meaning that there are no install keys for me to find in my Volume License account. What should happen is that if you use the right iso, it will contain your own license key for SQL pre-embedded, and you can actually see it when you run the installer.  However, this installer will detect pre-existing SQL features, and will dump/not display the license info if it detects any.

When I posted online about it people told me to backup my SQL Server DBs, delete my SQL install, then reinstall using the special installer ISO (and hope that SCCM’s accounts restore without any pain).

Thanks to the help of my friend, MCT and SQL Badass Sassan Karai, we found a better way.

Continue reading →

Advertisements

Working with SQL using PowerShell

FoxDeploy7 Comments

I’ve seen a lot of people ask about how to do this, so I thought I’d provide some information on the matter. Previously I had a project in which I had to pull a ton of data from an extended SCCM database and massage it before importing into MDT to setup location settings and things like that for an automated zero-touch install with very granular configuration settings determined based on workstation location.

I had to learn this the hard way, but it saved me weeks worth of painstakingly combing through records, and was also pretty fun once I dug into the guts, and was able to get old customized SQL records to jive with the Microsoft Deployment Toolkit Database!  It’s fun to teach machines to talk to each other!

Because many PowerShell enthusiasts may not be familiar with SQL, in this post I’ll walk through the steps to install SQL, setup a database and then create a table, before digging into how to directly influence SQL from the PowerShell prompt. Continue reading →

Solving 5447, MP has rejected a policy request because it was not approved.

FoxDeployLeave a comment

You may see the error message like this from time to time:

 Component: SMS_MP_CONTROL_MANAGER

MP has rejected a policy request from GUID:XXXXX-XXXX-XXX-XXX-XXXXXXXXXXXXXX because it was not approved.

This really means that the client…is not approved.  This can happen for a few reasons, but namely one.  If you set SCCM to Manual Approval mode at some point, be it for testing or troubleshooting, any clients that attempted to be approved at that time are marked as Unapproved, pretty much until the end of time.  You have to find these systems and mark them as approved.

If you use my method to[get system names from a status message], you can just run this on the status message Id of ‘5447’ and see the computer names.  Now, just copy and paste these into the SCCM WQL query I’ve provided here [Where machine name is in this list of names], approve them all and here you go, problem solved.

Solving SCCM Error ID 682, authentication key did not match

FoxDeployLeave a comment

I ran into an issue at a client in which dozens of systems were not replicating their PKI key data up the hierarchy, thus causing the Central site to trigger critical alerts while processing the Data Discovery, Software Inventory and Hardware inventory records from these clients every night.  This bugged me, and I wanted an easy way to track down these systems.

The Error ID is 682.

Component Discovery_Data_Manager

Message Text :

The data file “E:\SCCM\inboxes\auth\ddm.box\5tn3ejdr.DDR” that was submitted by the client whose SMS unique ID is “GUID:”, was rejected because the file was signed but the authentication key did not match the recorded key for this client. Continue reading →

SCCM Report – All Packages, Programs and their command lines

FoxDeploy17 Comments

Hi all,

I recently had a requirement at a client to make a list of all SCCM Packages. their Programs,  and then the command lines of each, along with the package source.  I wondered if this was possible, and fortunately the SQL Views were already present to do so.

This ended up being a very short query:

SELECT Program.PackageID,
Package.Name 'Package Name',
Program.ProgramName 'Program Name',
Program.CommandLine,
Program.Comment,
Program.Description,
Package.PkgSourcePath
FROM [v_Program] as Program
LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID</p>
Order by Program.PackageID

And the output  Some of these command lines were VERY long, so it came out a bit wide.:

apps and command lines - 1 apps and command lines - 2