ReportingService2010 soap call to set report credentials
-
I have written a routine to speed up the upload and download of reports from our reporting server. The problem is that the reports do not retain their credentials when you download them.
So I've tried to update them after uploading the report, but get an error when the report is run.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
Format of the initialization string does not conform to specification starting at index 0.
If I edit the report on the server and just set the datasource credentials password, all works as expected.
I'm using the ReportingService2010 soap API.
Dim DS() As DataSource = SSRS.GetItemDataSources(Path)
For Each D As DataSource In DS
With CType(D.Item, DataSourceDefinition)
.CredentialRetrieval = CredentialRetrievalEnum.Store
.WindowsCredentials = False
.UserName = "username"
.Password = "password"
.UseOriginalConnectString = False
.ConnectString = "=Parameters!ConnStr.Value"
.Prompt = ""
End With
Next
SSRS.SetItemDataSources(Path, DS)Any idea what I'm doing wrong?
-
This was removed by the editor as SPAM
-
NOTE - the below is based on my understanding of your code and doing a quick translation into C# as I know C# a bit better than the language you used that I THINK is VB.NET, but I am not certain. I have not use SOAP for this either.
What I am seeing is that your loop MAY not be doing what you think it is. If I am reading that right, you are updating the values in D.Item BUT that object only exists within the for each loop. You are wanting to update the value inside DS. What I would recommend is printing out the values of DS[0] such as the username for example and see if it matches the value you tried to update things to. I would print out the value before the for loop and after the for loop just to make sure the value is changing. I suspect it is going to be the same value in each case. I am not sure on the syntax in that language for arrays, so replace DS[0] with whatever the syntax is to get the first element in the DS array.
If the above is giving you the wrong result, then what I suspect you will want to do is to insert D into the DS array, overwriting the value that already exists.
What I mean is that code like this doesn't even work in C#:
string[] test1 = new string[2];
test1[0] = "hello";
test1[1] = "world";
foreach (string test in test1)
{
test = test.ToUpper();
}
Console.WriteLine(test1[0]);It will complain that test cannot be assigned that way in the "test = test.ToUpper();" line. The exact error is "Cannot assign to 'test' becuase it is a 'foreach iteration variable'". So I need to store the value I am updating in a second variable, and I'm either overwriting the existing value in test1 (the array) which is probably the best approach, or I am writing the values to a new array and then overwriting the original array at the end. So something like:
string[] test1 = new string[2];
test1[0] = "hello";
test1[1] = "world";
int counter = 0;
foreach (string test in test1)
{
test1[counter] = test.ToUpper();
counter = counter + 1;
}
Console.WriteLine(test1[0]);The reason I recommend something like this rather than the second array is that you don't end up with 2 arrays in memory; you only have 1. You do end up with an extra variable (an INT) but those are small in memory compared to a second string array.
Now to modify your code, (remember I don't know what language that is, so I'm doing my best to tweak it here), I would do something like:
Dim DS() As DataSource = SSRS.GetItemDataSources(Path)
Dim counter As Int
counter = 0
For Each D As DataSource In DS
With CType(D.Item, DataSourceDefinition)
.CredentialRetrieval = CredentialRetrievalEnum.Store
.WindowsCredentials = False
.UserName = "username"
.Password = "password"
.UseOriginalConnectString = False
.ConnectString = "=Parameters!ConnStr.Value"
.Prompt = ""
End With
DS(counter) = D
counter = counter + 1
Next
SSRS.SetItemDataSources(Path, DS)Now, is the above syntactically correct? Probably not. You will likely need to tweak it. Is it the most efficient way to do this? also probably not, but it is easy to see what the code is doing and is easy to support.
BUT, as I said earlier, I would check the result of what is stored in DS[0] before and after your for loop to make sure the value is changing as you wanting as I expect it is not.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
The code is VB.Net and it is working correctly in that it does indeed update the values in the datasource which does update the datasource in the report on the server. I can vary the username and do see that change when I look in the report on the server, it's just that it is messing up some other part of the credentials.
As you can hopefully see from the screenshot, DS does contain what I'd expect when it's sent back to the server with SetItemDatasource
If we look in the report server manage report, the datasource does contain what I specified to set the username to, so I know it's getting there.. even with the correct user/password, it doesn't work when I try to run it. But, if I manually change it on the server to the correct credentials, it works fine.
Any thoughts on what parameters I'm not setting correctly?
-
Slight correct to the above, if I update the user / password in the Manage report screen as above, it doesn't work but if I change it in the Edit in Report Builder and save it, then it does work.
-
Ok, I've gotten a little further, it's not the password that's causing the problem, it's setting the connection string.
.UseOriginalConnectString = False
.OriginalConnectStringExpressionBased = True
.ConnectString = "=Parameters!ConnStr.Value"The .UseOriginalConnectString must be false, or the new .ConnectString will be ignored.
The newly added .OriginalConnectionStringExpressionBased = True is needed because we're using an expression to use a report parameter (ConnStr) as the connection string, e.g. Data Source=MyServer;Initial Catalog=MyDB is passed in from the web page to tell the report server which DB to run against.
But alas, it still fails with the same error.. unless I omit the set connection string bits, then it works just fine.
-
In case anyone is interested, the only way I have managed to do this is to download the report definition and directly change the XML to set the ConnectionString and then re-upload it. Setting the connection string via the Soap API just corrupts the report on the server. I don't know if this is something to do with the fact that I'm using SQL 2019 Reporting Services or not, but it just doesn't work properly.
B = SSRS.GetItemDefinition(Path)
B = FixDatasource(B)
Warns = SSRS.SetItemDefinition(Path, B, Nothing)
Private Function FixDatasource(B As Byte()) As Byte()
Dim xDoc As New XmlDocument
Dim nsMgr As XmlNamespaceManager
Dim xNode As XmlNode
Dim nodeList As XmlNodeList
Dim MStream As MemoryStream
Dim NewB As Byte()
Try
MStream = New MemoryStream(B)
xDoc.Load(MStream)
nsMgr = New XmlNamespaceManager(xDoc.NameTable)
nodeList = xDoc.SelectNodes("//namespace::*[not(. = ../../namespace::*)]")
For Each xNode In nodeList
If xNode.LocalName = "xmlns" Then
nsMgr.AddNamespace("df", xNode.Value)
End If
Next
For Each xNode In xDoc.SelectNodes("//df:Report/df:DataSources/df:DataSource/df:ConnectionProperties/df:ConnectString", nsMgr)
xNode.InnerText = "=Parameters!ConnStr.Value"
Next
MStream = New MemoryStream()
xDoc.Save(MStream)
NewB = MStream.ToArray()
Catch ex As Exception
Return B
End Try
Return NewB
End Function
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply