Last updated on September 28th, 2022 at 09:33 am
If you take a copy of your System Center Configuration Manager (SCCM) database and put it on your laptop (or anywhere else) without SQL Server common language runtime (CLR) enabled, you will receive an error when accessing Role-Based Administration (RBA) functions. The error message that you will receive will look similar to the one below. This blog post will talk about why and when this happens and how you can enable CLR within SQL Server.
Msg 6263, Level 16, State 1, Line 6
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.
Why Would You Put a Copy of Your SCCM Database on Your Laptop?
During any of my presentations on either reporting for SQL Server Reporting Services (SSRS) or querying the SCCM database, I strongly advise against building queries or creating reports directly on a production SCCM database. Instead, you should always build your queries in your development SCCM database environment.
I also like to explain that I have a copy of my test lab database installed on my laptop and why I love having a copy of it there. Like the Boy Scout motto, “BE PREPARED,” in order to ensure that I have everything that I need to demonstrate reporting and querying a SCCM database, I keep a copy of my lab on my laptop and I even have SSRS setup on it too. I am only dependent on my laptop, which means that as long as I have my laptop, my presentation will go off without a hitch!
Difficult Development Environments
Why am I telling you all of this? I found out that many SCCM admins don’t have a development environment or their development environment is difficult to access. If you are in a similar situation, then I recommend that you install SQL Server, SSRS, SQL Server Data Tools (SSDT) and a copy of the SCCM database on your own workstation. Doing this will make your life a lot easier!
How to Enable CLR within SQL Server
You most likely won’t see this error message when you first start querying the SCCM database. You’ll see it, though, when you try to create queries that leverage Role-Based Administration (RBA).
The RBA functions leverage the CLR feature in SQL Server in order to run correctly. If CLR isn’t enabled the queries that use RBA will fail. The solution is really simple: enable CLR for the SCCM database. Start by opening SQL Server Management Studio (SSMS) and execute the following SQL Server query. After that, the problem will disappear and you can leverage all of the RBA functions!
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘clr enabled’, 1;
GO
RECONFIGURE;
GO
What does this SQL Server query do? In a nutshell, it first enables the advanced options for SQL Server. Next, it will enable CLR, the feature within SQL Server. There is no restart needed for the SQL Server in order to enable CLR.
For more details about how to enable CLR, please see the Microsoft documentation.
If you have any questions, feel free to contact me @GarthMJ.
Do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post!