How To Setup Report Builder to Create or Edit ConfigMgr CB Reports.

There are a few tricks to getting Report Builder to be able to edit or create reports in your ConfigMgr SSRS environment. I have had to do this several times, as I often change workstations, so I thought I’d finally blog it.  I’ll cover a few things in this post to help you get started.
In this Post, I’ll cover the simple setup of SQL Report Builder, connecting it into your ConfigMgr SSRS (Written and tested with CB 1610), and testing the queries to make sure you have permissions.

1. Download and install SQL Report Builder
   1. Download HERE
   2. To Install, just follow the wizard.
2. Export the SQL Cert on your ConfigMgr DB server.
   
   1. Right Click on the ConfigMgr SQL Server Identification Cert and choose Export
   2. Yes, export Private Key (Keep this in a secure location)
   3. Leave file Format Screen defaulted, and click Next
      
   4. Recommend Using a password to protect the Certificate
      
   5. Save the Cert somewhere secure for use later.
3. Connecting SQL RB to your CM SSRS Database
   1. Launch SQL RB 2016, (Make sure you launch the software as a user that has RIGHTS to modify Reports, you might have to check permissions.  My account is a ConfigMgr Admin, so it does have rights, but you can grant others less rights to work on reports, using the Security roles in ConfigMgr) – Good Blog article about setting up roles HERE
   2. Now, lets open a canned report – File –> Open
   3. Point it at your SSRS URL String, you can get this by pulling up your Reports in IE, then grab the URL and modify it.  Delete everything after “Reports” and change “Reports” to “ReportServer”
      
      
   4. You should now see your Site Code Folder, go ahead and drill down to Hardware – General, Computer Information for a specific computer and click open
      
   5. It should now load that report in the Builder and you can see the Data Source & DataSets
      
   6. Go ahead and look at the DataSet0 Properties, this will be the Query used to gather the data for the report.
      
   7. Go ahead and click Query Designer, you should be prompted for username / password, choose “Use the current Windows user”.  At this point, I get an error: Unable to connect to data source – The certificate chain was issued by an authority that is not trusted.
      
   8. Remember that Cert we exported, this is when it comes into play. 
      1. Right Click on that Cert and Install (You can leave Report Builder open while you do this)
      2. Choose Local Machine, click Next, then insert the password you chose.
          
      3. Click “Place all certificates in the following store and click browse, choose “Trusted Root Certification Authorities.
         
      4. Click Finish, and it will then give you the “Success” box.
   9. Going back to the Report Builder, click Query Builder again, and choose “Use current logged on user”, it should now work and bring you to the query designer.
      1. To test the Query, you can hit the red Exclamation mark
      2. It will now prompt for 2 pieces of information, @UserSIDs & @variable (Computer Name)
         1. @UserSIDs, this one is a bit of fun, you’ll need to get your SID for your account, then convert it to what SSRS is looking for. – PowerShell Time
            [wmi] “win32_userAccount.Domain=’YourDomain’,Name=’YourAccount'”
            
         2. Grab the SID from there and go back to Report Builder
            Open the properties of the “DataSetAdminID” dataset
            Go to Query Designer, click the Red exclamation Mark, when prompted, enter the SID
            
            This will give you the UserSIDs
            
            Save your UserSID in a safe place, as you’ll need it for working with other canned reports.
   10. Going back into the DataSet0 Query, run the query and insert info
       
       Click OK
   11. Now you’ll see the data pulled via the Query:
       
4. At this point, you’re able to connect, run queries, and see how it’s working.  You can now start modifying the Queries to add additional info, remove info, modify how the report itself looks, etc.

At a later date, I’ll go over importing reports, building one from scratch, and modifying the canned reports. – If you’re interested, please leave comments on any suggestions you’d like to see pertaining to ConfigMgr reports.

Other worthy notes:

1. To disable the RBA (@UserSIDs) stuff, check out this link: https://blogs.technet.microsoft.com/michaelgriswold/2014/09/17/disabling-rbac-during-custom-report-creation/
2. Report Builder Tutorials – This is where I learned a lot of what I know (Besides just messing around) – https://msdn.microsoft.com/en-us/library/dd239338.aspx