Failed to grant permission to execute error and Zen Barcode for SSRS 2016 Reporting Services

Copy assemblies into the correct reporting services folder

When migrating to a new SQL server the barcodes were causing an issue, firstly because we lacked the barcode assemblies in the reporting services bin folder for the new version of Reporting Services that came with SQL server 2016.

Finding the dlls still in the path for the old Reporting Services server service, they were copied to new active location as shown:

Copy the Zen.Barcode.* files into the bin directory of currently active reporting services server

Permissions Error

After moving the required .dll into the correct location for this server, we then got a permission error.

Failed to load expression host assembly. Details: Could not load file or assembly 'Zen.Barcode.SSRS, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b5ae55aa76d2d9de' or one of its dependencies. Failed to grant permission to execute. (Exception from HRESULT: 0x80131418) (rsErrorLoadingExprHostAssembly)

 

rssrvpolicy.confg

Setting permissions to the assembly for reporting services is a matter of adding some grants to the policy file. Insert the following block after the last </CodeGroup> tag in that file.

"Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\rssrvpolicy.config"

<CodeGroup class="UnionCodeGroup" Name="BarcodeControl" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission."> 
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.SSRS.dll"/>
</CodeGroup>
<CodeGroup class="UnionCodeGroup" Name="BarcodeControl2" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission.">
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.Core.dll"/>
</CodeGroup>
<CodeGroup class="UnionCodeGroup" Name="BarcodeControl3" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission.">
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.Design.dll"/>
</CodeGroup>
<CodeGroup class="UnionCodeGroup" Name="BarcodeControl4" version="1" PermissionSetName="FullTrust" Description="This code group grants Zen.Barcode.SSRS.dll FullTrust permission.">
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin\Zen.Barcode.SSRS.Design.dll"/>
</CodeGroup>

 

At this point we were able to start using the barcodes again in reporting services.

 

Reminder on setting up barcode in a report

 

Create references in the Report>>Properties for the .dll files as shown

Zen.Barcode SSRS references

 

In the code tab of the report properties set a function that will return an image binary  that will be used as an expression for the image source in the report.

2019-02-27_09-45-13

 

Drop an image into the report that will be the barcode and set the expression for the image MIME type as shown.
2019-02-27_09-43-33

 

Example of the expression for the image, in this example, using the value of another field as the source for generating the barcode.
2019-02-27_09-43-20

 

If you found this helpful- then do comment, it helps motivate me to document more of these kind of things.

 

Reference: https://www.barcoderesource.com/configurereportingservices.shtml

SQL server Optimize for Ad hoc Workloads setting in server advanced properties window

TL;DR Turn this option on, from false to true

Whilst looking at a problem SQL server instance, I was on the diagnostic journey looking at why the query plan cache was getting totally cleared every few minutes. It turned out the server had bad memory setup and SQL server was suffering some bad memory pressure. However I did learn about a setting that I find hard to think of a reason why you’d not want to use it in a typical (what install is typical tim?) setup. This option Optimize for Ad hoc Workloads tells SQL server to not cache the query plan for a query until it sees it twice.

Server properties window - highlighting Optimize for Ad hoc Workloads setting

 

This is important where the SQL query work load is very varied, particularly where ad-hoc, non-stored procedure queries are being ran as they can bloat the query cache. The query cache is used to store the compiled execution plan required to execute a particular query. When a new query is encountered, the plan is calculated and put in the cache to save having to compute the plan again, if the query is seen again. The problem is that with ad-hoc queries they are unlikely to be seen again, thus SQL server is using up memory unnecessarily that could be used for better things.

I understand, but need to verify, that this is also true of ORMs such as Entity framework, that although it does create parameter based SQL to execute, in the SQL it sends to the server, the length of those parameters can vary depending upon the length of the values in those parameters. Thus this can create a large number of query plans for what are essentially very similar queries. (OK technically the length of the searched text can vary the query plan but run with this).

The setting will greatly reduce the number of plans and memory used for them on the server as only if they the query is seen twice will it be fully cached. The first time it is seen a stub is created that is enough to spot if the query is seen a second time, only then will the query plan be cached. Truely ad-hoc queries wont be seen again and space in the cache is saved. The compile time is not really worth worrying about because having to compile the query twice is no big deal as after the second compile, the further 100,000k executions can come from the cache, so proportionally its an efficiency worth having for a tiny, tiny hit of compiling the query plan twice.

There is a good article here on how start looking into if you have bloat,http://www.sqlservercentral.com/articles/SQLServerCentral/91250/

There is a similar setting of Forced Paramatization for details of this setting see this post by Brent Ozar,  https://www.brentozar.com/archive/2018/03/why-multiple-plans-for-one-query-are-bad/ basically this setting forces the server to look more carefully at the plans and infer where parameters would exist if you were to paramatize the query. This is great for reducing plans in the cache but increases processor work as it has to examine queries a lot more to work out where the parameters lie. It can also lead to bad parameter sniffing as before the plans would have been totally bespoke, now they will be shared and the exact parameter can change the optimal plan.

SQL Server upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25

Upgrading SQL server to SP4 or SQL Server 2016 encountered error 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25

When doing an dummy run for a SQL instance upgrade, I encountered this error and it resulted in the SQL service not starting after upgrade and the upgrade wizard reporting errors.

After a couple of attempts I had to dig into it to find what was going on so referencing the article SQL Server Service fails to start after applying patch. Error: CREATE SCHEMA failed due to previous errors I tried deleting the DatabaseMailUserRole Schema from msdb but the server still failed to start.

This was SQL Server 2012 so I checked the SQL server logs found in at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG.txt (that path may vary between versions).

The tail of the log looked like the following, where I had already removed the DatabaseMailUserRole, so it may have reported that too previously:

019-02-15 17:04:20.53 spid4s      Setting object permissions...
2019-02-15 17:04:20.63 spid4s      Error: 2714, Severity: 16, State: 6.
2019-02-15 17:04:20.63 spid4s      There is already an object named 'TargetServersRole' in the database.
2019-02-15 17:04:20.63 spid4s      Error: 2759, Severity: 16, State: 0.
2019-02-15 17:04:20.63 spid4s      CREATE SCHEMA failed due to previous errors.
2019-02-15 17:04:20.63 spid4s      Error: 912, Severity: 21, State: 2.
2019-02-15 17:04:20.63 spid4s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 2714, state 6, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2019-02-15 17:04:20.63 spid4s      Error: 3417, Severity: 21, State: 3.
2019-02-15 17:04:20.63 spid4s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2019-02-15 17:04:20.63 spid4s      SQL Server shutdown has been initiated
2019-02-15 17:04:20.63 spid4s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2019-02-15 17:04:20.80 spid14s     The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/***] for the SQL Server service.
2019-02-15 17:04:20.80 spid14s     The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/***:1433 ] for the SQL Server service.

So it seems I had the same problem as the referenced blog post but with another schema role.

Solution

After running the upgrade and it failing, I started SQL server with the trace flag 902, using net start mssqlserver /T902 from an elevated command prompt. This prevents the startup scripts running.

image

Then connected to the SQL server instance using SSMS and located the 'TargetServersRole'  under the msdb database and right click, deleted it.

image

I then stopped SQL server and restarted it normally, without the trace flag, so it runs the 'msdb110_upgrade.sql' at startup again.

This time it started normally.

image

 

Problem sorted!

 

If this was helpful please comment as it motivates me to blog more!

Prevent SQL job running when another job is running

 
CREATE VIEW myschema.Admin_RunningSQLJobs
AS
SELECT job.NAME
,job.job_id
,job.originating_server
,activity.run_requested_date
,DATEDIFF(SECOND, activity.run_requested_date, GETDATE()) AS Elapsed
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
JOIN (
SELECT MAX(agent_start_date) AS max_agent_start_date
FROM msdb.dbo.syssessions
) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL
AND stop_execution_date IS NULL


Using this view we can check for a running job before running our job…
In this example we don’t want the nightly pricing build to run if a monthly build is in progress, note, nightly is scheduled later than monthly. So wrap the call to the stored procedure in the job step like this…
 
IF NOT EXISTS(SELECT * FROM myschema.Admin_RunningSQLJobs WHERE name='Pricing - Monthly Rebuild') 
BEGIN
EXEC myschema.Nightly Pricing build
END

Note about backups:
We restore the live company into the test company of our ERP system, once a week, to keep the test company data fresh. It is worth noting that it is wise to keep the above SQL view in a database that is not involved in a restore, otherwise errors will occur trying to access the view as that database is in restore mode and cannot be accessed by the SQL job.
For this reason we keep this view in an “admin” database that is not part of the automated restore process, using the view in SQL jobs used against the test database prevents them erroring when the restore process in in progress.