mstarnaud
Posts: 45
Joined: Mon Apr 15, 2013 9:44 pm

SSRS deployment : datasource selection and impersonation

Fri Jun 12, 2015 4:18 pm

Hi

I am trying to deploy SSRS reports on a server. I am encountering 2 problems.

#1 : impersonation
My windows user does not have permissions on the target server. So if I open the SSRS web page in Internet Explorer for example, I am prompted for a user/password. In my situation, I know the user/password of a user with sufficient permissions, so this works fine inside Internet Explorer. However, I don't get anything similar with Advanced Installer. The Impersonate User setting that is provided in Advanced Installer is useless in this scenario.
I get the following error :

Code: Select all

SQL Server Reporting Services deployment Deployment failed. Reason: Could not connect to server : http://MyServer/ReportServer/ReportService2010.asmx.
#2 : selecting the datasource
In my scenario, the datasource only exists in the server. I do not have a copy of it on my local machine. I am not deploying that datasource. How can I specify that I want to associate my reports with a specific target datasource?

I used the follwoing page as reference, but it did not help me with those 2 issues
http://www.advancedinstaller.com/user-g ... yment.html

mihai.petcu
Posts: 3860
Joined: Thu Aug 05, 2010 8:01 am

Re: SSRS deployment : datasource selection and impersonation

Tue Jun 16, 2015 11:53 am

Hello,
The Impersonate User setting that is provided in Advanced Installer is useless in this scenario.
Thank you for your suggestion. Although this scenario is not very common, I notified our development so they can consider the case.
How can I specify that I want to associate my reports with a specific target datasource?
The reports are linked to the datasources that have been defined in the designer. They just have to have the same names as the ones declared in the report file.

All the best,
Mihai
Mihai Petcu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

mstarnaud
Posts: 45
Joined: Mon Apr 15, 2013 9:44 pm

Re: SSRS deployment : datasource selection and impersonation

Thu May 19, 2016 4:42 pm

Hi

Coming back to this situation after a long time. I had a look at the release notes for more recent versions and found nothing related to the issues I had, but please do tell me if some of them were fixed. In the meantime, I tried to dig a bit deeper in what SSRS deployments can do and ran into a few problems.

#1 - I am still getting the impersonation problem as mentioned above. Luckily I can bypass this for the time being.

#2 - If I want to have items in different folders, the only way I found how to do this was to make new SSRS deployments for each folder. For example, if I want images to be in a Resources folder, I would need to create a 2nd SSRS Deployment with just my images, with ReportFolder = "Resources". Is this the best way of doing it, or do you have a better solution?

#3 - I am deploying a datasource that may or may not exist on the server. If it already exists, I do not want to override it. Is there a way to do that?

#4 - I am deploying both datasources and reports. I created a first deployment for only datasources, then a second deployment for the reports. The reports have the correct datasource name in their RDL content. After being deployed, my reports say they cannot find the datasource. This is a problem. The only way I managed to make it work is if the report and the datasource are in the same folder. But I do not want to deploy them in the same folder, I want a separate datasource folder.

#5 - Sometimes, an empty dataset folder gets deployed even though I am not deploying any dataset. This started happening once I split my project into 3 deployments : one for datasources, one for reports and one for resources/images. Back when I only had 1 deployment containing all 3 types, the dataset folder did not get created. My current workaround for this is to give the dataset folder name the same name as the folder I am pushing into that same deployment. It's also a bit annoying because it marks the folders (description) as being "Data Set Folder" instead of what they should be.

#6 : I have a dialog which asks the user for the server name, database name, username and password to connect to SQL. I am deploying an SSRS datasource, I would like to use the credentials passed in the dialog. I have seen the documentation on this (http://www.advancedinstaller.com/user-g ... ialog.html) but was unable to make it work. First, I was unable to save my datasource with brackets in it, so I could not use the feature. Second, even though I selected "use SQL Server Authentication, save my password", when I deployed it it became "use Windows integrated security". I tried modifying the RDS file manually to force the username & password directly in the connectionstring, but when I deployed it it removed those items from the connectionstring.
This is an important issue, as I will be deploying the same SSRS project to multiple different servers.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: SSRS deployment : datasource selection and impersonation

Fri May 27, 2016 12:07 pm

Hello,

Please accept our apologies for our delayed reply but we are quite busy lately. A major release is approaching here.
#1 - I am still getting the impersonation problem as mentioned above. Luckily I can bypass this for the time being.
This improvement is still on our TODO list and since our development team is very busy at the moment I'm afraid I cannot give you any ETA for this.
#2 - If I want to have items in different folders, the only way I found how to do this was to make new SSRS deployments for each folder. For example, if I want images to be in a Resources folder, I would need to create a 2nd SSRS Deployment with just my images, with ReportFolder = "Resources". Is this the best way of doing it, or do you have a better solution?
No, this is the only way we currently support.
#3 - I am deploying a datasource that may or may not exist on the server. If it already exists, I do not want to override it. Is there a way to do that?
Unfortunately, we don't have such a dedicated option. Maybe we will consider this improvement in the future. Thank you for your suggestion.

#4 - #6 : Please give us some time to test and investigate the reported behavior and we'll get back to you as soon as we have a resolution.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

mstarnaud
Posts: 45
Joined: Mon Apr 15, 2013 9:44 pm

Re: SSRS deployment : datasource selection and impersonation

Wed Jun 22, 2016 3:48 pm

Hi

Thanks for the answers so far.

But remember that I'm still looking for info on questions 4 to 6.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: SSRS deployment : datasource selection and impersonation

Tue Jul 05, 2016 1:55 pm

Hello,

Here are my replies to your outstanding questions.
#4 - I am deploying both datasources and reports. I created a first deployment for only datasources, then a second deployment for the reports. The reports have the correct datasource name in their RDL content. After being deployed, my reports say they cannot find the datasource. This is a problem. The only way I managed to make it work is if the report and the datasource are in the same folder. But I do not want to deploy them in the same folder, I want a separate datasource folder.
This is our default implementation. When a report is deployed (without its attached datasource being part of the same deployment), then at install time Advanced Installer will look for a datasource with the same name as the one specified in the RDL file in a datasource folder having the same name as the data source folder of the current deployment. It won't search in any other datasource folders. Therefore the only workaround for your scenario is to use the same datasource folder name (and path) for both of your deployments.
#5 - Sometimes, an empty dataset folder gets deployed even though I am not deploying any dataset. This started happening once I split my project into 3 deployments : one for datasources, one for reports and one for resources/images. Back when I only had 1 deployment containing all 3 types, the dataset folder did not get created. My current workaround for this is to give the dataset folder name the same name as the folder I am pushing into that same deployment. It's also a bit annoying because it marks the folders (description) as being "Data Set Folder" instead of what they should be.
Indeed, this is a limitation of our current SSRS support. I've added an improvement on our TODO list so that you can skipp the installation of an empty folder. Thank you for your suggestion. We will notify you when this will be out. Until then, I'm afraid I'm not aware of any suitable workaround. Thank you for your understanding.
#6 : I have a dialog which asks the user for the server name, database name, username and password to connect to SQL. I am deploying an SSRS datasource, I would like to use the credentials passed in the dialog. I have seen the documentation on this (http://www.advancedinstaller.com/user-g ... ialog.html) but was unable to make it work. First, I was unable to save my datasource with brackets in it, so I could not use the feature.
I have tested the scenario and I was able to successfully set the server name and database name in the RDS file at install time. All you have to do is like this:
1. edit the "ConnectString" element of your RDS file in a text editor like this:

Code: Select all

<ConnectString>Data Source=[SERVER_PROP];Initial Catalog=[DATABASE_PROP]</ConnectString>
2. add the RDS file in "SSRS" page from your setup project and check its Resolve content as formatted option
3. add a dialog in "Dialogs" page with two edit box controls (having the associated property: SERVER_PROP, respectively DATABASE_PROP) where the user will type in the server address and database name
4. rebuild and test the installation package
Second, even though I selected "use SQL Server Authentication, save my password", when I deployed it it became "use Windows integrated security". I tried modifying the RDS file manually to force the username & password directly in the connectionstring, but when I deployed it it removed those items from the connectionstring.
This is an important issue, as I will be deploying the same SSRS project to multiple different servers.
I've tested and replicated this behavior when using an RDS file with SQL Authentication. This seems to be an Advanced Installer issue. Thank you for bringing it to our attention. A fix will be available in a future version of Advanced Installer. We will notify you when this will be available.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

mstarnaud
Posts: 45
Joined: Mon Apr 15, 2013 9:44 pm

Re: SSRS deployment : datasource selection and impersonation

Thu Jul 07, 2016 8:39 pm

Hi

Thanks a lot for your answers! I'll need to try #6 on my side and see if I can acceptably make it work in our projects. Question #4 is a major issue, but at least #6 helps working around it.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: SSRS deployment : datasource selection and impersonation

Fri Jul 08, 2016 11:03 am

You're always welcome. Just let us know if you need further assistance.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

mstarnaud
Posts: 45
Joined: Mon Apr 15, 2013 9:44 pm

Re: SSRS deployment : datasource selection and impersonation

Tue Jul 19, 2016 3:53 pm

Hi

I tried your answer to #6. Unfortunately, it was only a partial success and not sufficient for our case. We want to pass server, database, user and password to the datasource. I managed to make them map to the properties as you explained and it correctly chose the server and database once deployed, so that is good. But there are 2 problems :

- It still only supports Windows Authentication and not user/password. I can try to force them in the connectionstring, but they're not going to be used anyway (plus the password would be in plain text, not encrypted).

- Every time we are ready to deploy, we have to edit the datasource to put [PROP_SERVERNAME] and such.

So everything that has to do with datasources (creation, credentials, link to reports), we'll need to do manually for the time being. Other than that, I think this is a good start and we should be using it in most of our projects.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: SSRS deployment : datasource selection and impersonation

Wed Jul 20, 2016 2:28 pm

Indeed, this happens due to a limitation/issue in our current SSRS support.

We'll update this thread when a fix will be available.

Thank you for your understanding.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

mstarnaud
Posts: 45
Joined: Mon Apr 15, 2013 9:44 pm

Re: SSRS deployment : datasource selection and impersonation

Thu Jul 21, 2016 2:29 pm

Hi

An update on that : I looked for further workarounds. Unfortunately, I still wasn't able to reliably set the username & password on the datasource. It looks to be possible using web service calls (see http://stackoverflow.com/questions/1454 ... erver-side for example). However those methods rely on specific SSRS versions and server configurations (IIS settings, etc.) and we can't allow ourselves to rely on that. So we'll continue to manually set the datasource credentials for now. Although we could at least make an SQL query to check if the datasource already exists and set the datasource's deployment condition accordingly (fix for my question #3).

But I do have good news! I was able to fix the reports' datasource link even if they are not in the same folder. Here is my step-by-step :

- In Advanced Installer, make sure you have the following install parameters : Datasource Folder, Default Datasource Name, Install Start Time, Report folder.
- In Advanced Installer, add an SQL query with only SELECT GETDATE() and assign it to the Install Start Time parameter. Then put the custom action at the very start of the sequence (before Wizard.Searches).
- Deploy datasources & reports as usual.
- In Advanced Installer, create an SQL Query with the script below. Also, add the following replacements : Install Start Time, Datasource Folder, Default Datasource Name (see the first lines of the script). Then put the custom action at the very end, in the Finish Dialogs section. This is the script that will fix all the broken datasource links for the reports we just deployed.

Code: Select all

DECLARE @StartTime				DATETIME,
		@DatasourceFolder		VARCHAR(425),
		@DatasourceFolderItemId	UNIQUEIDENTIFIER,
		@DefaultDatasourceName	VARCHAR(425)

SET		@StartTime = '!!!INSTALLSTARTTIME!!!'
SET		@DatasourceFolder = '/!!!DATASOURCEFOLDER!!!'
SET		@DefaultDatasourceName = '!!!DEFAULTDATASOURCENAME!!!'

DECLARE	@Reports TABLE
(
	ReportItemId	UNIQUEIDENTIFIER,
	ReportPath		VARCHAR(425),
	ReportName		VARCHAR(425),
	DatasourceId	UNIQUEIDENTIFIER,
	DatasourceName	VARCHAR(425)
)

SET		@DatasourceFolderItemId =	(
										SELECT	ItemId
										FROM	dbo.[Catalog]
										WHERE	[Path] = @DatasourceFolder
										AND		[Type] = 1 -- Folder
									)

INSERT
INTO	@Reports
(
	ReportItemId	,
	ReportPath		,
	ReportName		,
	DatasourceId	,
	DatasourceName	
)
SELECT	c.ItemID,
		c.[Path],
		c.Name,
		ds.DSID,
		ds.Name
FROM	dbo.[Catalog] c
JOIN	dbo.DataSource ds	ON	ds.ItemID = c.ItemID
WHERE	ds.Link IS NULL
AND		c.[Type] = 2 -- Report
AND		(
			c.ModifiedDate >= @StartTime
			OR
			c.CreationDate >= @StartTime
		)
--SELECT * FROM @Reports

UPDATE	ds
SET		ds.Link = ISNULL(c1.ItemID, c2.ItemID)
FROM	@Reports r
JOIN	dbo.DataSource ds	ON	ds.ItemID = r.ReportItemId
							AND	ds.DSID = r.DatasourceId
LEFT
JOIN	dbo.[Catalog] c1	ON	c1.Name = ds.Name
							AND	c1.ParentID = @DatasourceFolderItemId
LEFT
JOIN	dbo.[Catalog] c2	ON	c2.Name = @DefaultDatasourceName
							AND	c2.ParentID = @DatasourceFolderItemId

-- This select is needed because Advanced Installer is expecting a return column.
SELECT	ReportItemId
FROM	@Reports
ORDER
BY		ReportName	ASC
Quick recap:

#1 : Cannot use different windows credentials than mine when deploying. No solution yet, this is on the TODO list.
#2 : Deploying using a folder hierarchy. The workaround is to create multiple deployment items.
#3 : Deploying a datasource automatically overwrites it if it exists. This is a "maybe TODO". But I have a possible workaround via SQL query and putting a condition on the deployment.
#4 : Reports cannot find the datasource. This is an Advanced Installer problem. See my workaround above.
#5 : Sometimes empty folders get deployed. This is on the TODO list. There might be a way to create an SQL script to delete it at the end of the installation.
#6 : Cannot set the datasource credentials, it defaults back to Windows Authentication. This is an Advanced Installer bug and should be fixed. There might be some way to do it programmatically (see above) but it seems unreliable. In the meantime, I will have to manually set the credentials after installation.

Daniel
Posts: 8237
Joined: Mon Apr 02, 2012 1:11 pm
Contact:  Website

Re: SSRS deployment : datasource selection and impersonation

Fri Jul 22, 2016 3:32 pm

Hello,

Thank you for your follow up and for the posted workaround. Until a fix will be available in Advanced Installer, this will certainly be useful for future users facing similar limitation.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Dan
Posts: 4513
Joined: Wed Apr 24, 2013 3:51 pm

Re: SSRS deployment : datasource selection and impersonation

Fri Jul 13, 2018 12:33 pm

Hello,

This was fixed in version 15.0 of Advanced Installer released on June 28th, 2018.

Best regards,
Dan
Dan Ghiorghita - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”