kohera-logo-regular.svg

Using Azure Blob storage in SSRS reports

Vector_BG.png

Using Azure Blob storage in SSRS reports

Vector_BG.png

Attractive reports are not only showing the hard material statistics and numbers. Visualization of your data leads more and more to graphical decision making. One particular part of your reports are images for logo’s, product images etc. All this data and images were stored on-premise in the past. But when moving to the cloud, where do we need to store and find our data and images? How do we connect to it how do we use it?

New dashboards are made with powerBI, where you can connect to whatever kind of data stored on whatever kind of storage provider. You can embed pictures in your dashboard or get pointed to them by some URI you specify. But what do you do with existing SSRS reports? Do you convert them all to a powerBI dashboard? There are a lot of reasons why you will stay put on them in the current SSRS reporting serving for now, waiting to transform…

Next, I’ll explain what you can do when your data and images are transformed to the cloud.

Case description:

  • Azure SQL database containing application data
  • Azure blob-storage (hot or cold storage account) where all images are stored in a virtual directory
  • On-premise SSRS 2017 report server
  • Existing reports with logo’s and images used

Case purpose:

  • Use the same reports deployed to on-premise SSRS report in a HYBRID-cloud infrastructure solution
  • Using the images stored in Azure blob-storage on the report

Case solution:

1: Create SAS-token for your Blob-container:

In the azure portal, create a SAS token

This results in a long string, something like,
https://myaccount.blob.core.windows.net/sascontainer/sasblob.txt?sv=2015-04-05&st=2015-04-29T22%3A18%3A26Z&se=2015-04-30T02%3A23%3A26Z&sr=b&sp=rw&sip=168.1.5.60-168.1.5.70&spr=https&sig=Z%2FRHIX5Xcg0Mq2rqI3OlWTjEg2tYkboXr1P9ZUXDtkk%3D

2: Define external data source in your Azure SQL DB:

If not already done, create mater key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD='****’;

Create database scoped credential: (use part from sas token beginning from ‘sv=…’)

create DATABASE SCOPED CREDENTIAL RW_hotblobcontainer
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
,SECRET='sv=********************************'
GO

Create external data source:

CREATE EXTERNAL DATA SOURCE HotBlobContainer WITH
(
TYPE = BLOB_STORAGE,
LOCATION = https://myaccount.blob.core.windows.net/sascontainer',
CREDENTIAL = RW_hotblobcontainer
)
GO

3: Define Azure SQL DB connection in your REPORT solution

4: Create shared or non-shared dataset for a get image byte stream data request

Stored procedure:

CREATE proc [SSRS].[up_GetLogoImage]
(@id int
)
as
begin
set nocount on;
/*grant
grant execute on [SSRS].[up_GetLogoImage] to [SSRS_user];
Grant ADMINISTER DATABASE BULK OPERATIONS TO [SSRS_user];
--*/
declare @SQLstmt nvarchar(max);
select top (1) @SQLstmt=N'select imagedocument.BulkColumn as BinaryData from openrowset(BULK '''+lofi.logoimagepath+''', DATA_SOURCE=''HotBlobContainer'', SINGLE_BLOB) as imagedocument;'
from dbo.vwLogoFiles lofi
where lofi.id=@id
;
declare @tblImageData TABLE(binarydata varbinary(max) );
begin TRY
insert into @tblImageData(binarydata)
exec sp_executesql @SQLstmt;
end TRY
begin CATCH
insert into @tblImageData(binarydata) values(NULL);
end CATCH
select top(1) binarydata from @tblImageData;
end
GO

 

The view vwLogoFiles contains:

CREATE VIEW [dbo].[vwLogoImages]
AS
SELECT baseT.[LogoImagePath]
FROM [dbo].[Establishment] as BaseT
;
GO

 

Result:

[LogoImagePath]='pathlevel1/pathlevel2/logo/squarelogo.png'

 

Resultset:

5: Create image object on the report

And that’s it.

The logo image is viewed on the report.

Enjoy…

Photo of successful woman coder hacker web creator sitting armchair comfortable workspace workstation indoors.
The hurdles and pitfalls of moving or migrating a System-versioned temporal table cross database
Maybe you already have your own way of doing this and are wondering about alternative methods, or maybe you are...
Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...