Hey All,
I am working with Business Objects XI Release 2. I am trying to fetch encrypted data from SQL Server 2008. This data is Pass Phrase encrypted. Hence I need a mechanism to pass the “pass phrase” to SQL Server. Please let me know if there is any hook in Business Objects to do the same.
It would be great if I can prompt the user to enter the pass phrase.
Any help would be appreciated.
Thanks.
nasha (BOB member since 2009-09-02)
Are you trying to query the system database directly? This is not possible, you need to use the query builder or SDK.
If you are talking about a separate source database that is encrypted, I don’t think I can answer that.
bension (BOB member since 2005-09-01)
Nope this is not a system databse this is a normal user made databse in SQL -Server that holds encrypted data.
nasha (BOB member since 2009-09-02)
Welcome to B:bob:B!
Can you let us know how do you get the encrypted data from a database when you use a query tool other than BO? What kind of query you need to run in a query tool to get the data?
Marek Chladny 
(BOB member since 2003-11-27)
From the application I call a store procedure passing the pass phrase to it as a parameter. This store proc decrypts and returns the data back.
nasha (BOB member since 2009-09-02)
Then how about using the same stored procedure in a data provider with the parameter being passed using a report prompt ( @prompt() function) ?
Marek Chladny (BOB member since 2003-11-27)
I was told by my local BO dev that BO XI Version 2 does not support Store procedures.
nasha (BOB member since 2009-09-02)
Hi,
You can use stored procedures as data sources in DeskI reports. You can’t use them in WebI reports.
Maybe your BO developer was talking about WebI reports.
Marek Chladny (BOB member since 2003-11-27)
You can call stored functions. You will have to make each column refer to a function that does what you want.
In the select portion of EVERY object you’re going to need something like:
decrypt_me(table.column, passcode)Be careful, your performance may not be very good.
Steve Krandel 
(BOB member since 2002-06-25)
These reports will be viewed using DI. I was told that Version 2 does not support store procedures hence this question arised.
Sorry, I myself I am working on BO for the firsrt time hence I dont know its capabilities. Thanks for the pointer. I will check with my dev and let you know.
Thanks for all your help. Appreciate it !
nasha (BOB member since 2009-09-02)
Does DI mean Data Integrator or Desktop Intelligence.
If you are talking about Desktop Intelligence, then you should be fine. Desktop Intelligence does support Stored Procedures.
This is a universe issue. You can build derived tables in you universe which decrypt. Then, none of this matters.
Steve Krandel 
(BOB member since 2002-06-25)
Please let me know how to call store procedures in Business Object XI Version 2 ? My team believes its NOT SUPPORTED. It would be great if someone could clarify this.
Also is it possible to read a file from the disk or call a web service from business objects. As if we cannot call the procedure then we would like to call a webservice that can perform this
Thanks once again for all your help.
nasha (BOB member since 2009-09-02)
Please do a search and you will find topics that discussed this already.
Marek Chladny 
(BOB member since 2003-11-27)
Again. Are you trying to have stored proc as a source for a report? If so, then you can’t.
If you are calling a function that does the work, then yes.
Steve Krandel (BOB member since 2002-06-25)
Steve,
You can use a stored procedure as a data source for a DeskI report.
In a New Data Wizard or New Table Wizard, in the Specify Data Access window you do not select Universe radio button but Others and then you
select Stored Procedures from the drop down menu.
There is even a way how to use a stored procedure in a WebI report if you use a certain database:
Marek Chladny (BOB member since 2003-11-27)
What I think is being missed here is that Steve is suggesting that a stored function is more appropriate than a stored procedure anyway. I believe this encrypted field is supposed to be displayed with other values, in which case is should be an object. By creating a function to decrypt the information (using the same logic currently found in the procedure) then it can be put into a universe, and any of the query tools that support universes can use the data.
Dave Rathbun (BOB member since 2002-06-06)
Thank you all for the responses.
I can use store procedures from the Desktop Intelligence Reports. The second step is to a store procedure from Designer to create classes and objects. I am trying to create a derived table to call the store proc in the sql expression but it does not accept it.
Is there any way I can call store proc in the Designer (BO XI Version 2)? Thanks.
P.S. We are also trying to get the logic on Store proc in a UDF but it seems there are some contraints in terms of the kind of operation that can be performed by a UDF. Hence the choice or a technical requirement might be to use a store procedure.
nasha (BOB member since 2009-09-02)
Honestly, you’re not thinkning this through.
A store procedure returns several columns and rows.
You cannot use these in Designer.
In Designer, you are linking tables/views. If you can build a derived table that uses your stored proc, that’s OK. But, what you’re trying to do simply does not make much sense.
You have not explained to us what the purpose of this procedure is. Does it return the entire database in an unencrypted format. It seems to me that this is usually done on a column by column basis. In that case a stored function that returns a single column per row is the right way.
Steve Krandel (BOB member since 2002-06-25)
Yes, I am unable to think this through due to my lack of knowledge on BO. Sorry if this causes any inconvenience.
As you rightly said “If you can build a derived table that uses your stored proc, that’s OK.” … I am trying to do that but I get an error saying "Exception: DBD, [Microsoft OLE DB Provider for SQL Server] : Incorrect syntax near the keyword ‘exec’. State: 42000
I have also tried to exec the store procedure between “Begin” and “End” keywords and I get the same error for the keyword “Begin”.
nasha (BOB member since 2009-09-02)
a derived table is an inline view. Forget doing it in BO. If you wanted to do this in the database, how would you do it? If you can do it outside of BO, it should work inside.
Also, why not just make this a real database view or table and use a BO connectcion ID that has rights to it.
Steve Krandel (BOB member since 2002-06-25)