how to programmatically retrieve db password with sdk?

[Former Member]

hello all,

i'd like to instance a sqlconnection from an addin in order to directly connect to sap db, using microsoft sql client default libraries...

we are facing serious performance problems with simple queries using the standard sdk object recordset.

we tried building the connection string with this method but the property dbpassword is always a bunch of *

public static String GetSQLClientConnectionString()
        {
            String retVal = String.Empty;
            String connStringBase = "Server={0};Database={1};User Id={2};Password={3};";


            retVal = String.Format(connStringBase,
                                        CompanyHelper.Instance.Company.Server,
                                        CompanyHelper.Instance.Company.CompanyDB,
                                        CompanyHelper.Instance.Company.DbUserName,
                                        CompanyHelper.Instance.Company.DbPassword);


            return retVal;
        }

as documentation tells us about dbproperty this is an expected beahaviour:

Description
Sets or returns the password for establishing a connection to the database server.


The field is not mandatory, as the database credentials are stored in the license server and you can use these values instead.


Property type
Read-write property
Syntax
Visual Basic 
Public Property DbPassword() As String



Remarks
When you retrieve the value of the DbPassword field, ****** is returned.


If you set a value to the DbUserName field, then that value is returned when you get a value from this field. If you connected without providing a database user name (relying on the credentials stored in the license server), then an empty string is returned.

now i'd like to know if it is possible to get a clear password in order to build a proper sql connection string without storing it in a configuration file?

thanks in advance

[frank_wang6]

I think u went to the wrong direction. You are trying to increase performance on sql query.

Based on my past experience, direct connection with SQL is pretty much same as BoRecordSet object. I couldn't tell the difference.

If you suffer performance issue from query, I would suggest either tune up your sql query, or write functions or store procedure to increase performance.

Frank

[Former Member]

i can confirm our performance issue on a simple query ( select with 2 left outer joins made on indexed tables ) is present only on borecordset object.

this happens only on a couple of system tables joined with certain user defined tables.
the performance gain using sql client with the same query is about ten times.

borecordset is a wrapper around 2005 metods to query a db, maybe using odbc or if we are lucky ado, so i wouldn't compare it to a newer db connector.

we discovered with query analyzer that the underlying query more or less lasts the same, but clearly borecordset is doing something else before or after the query itself.

btw we submitted our problem to sap b1 support and as soon as we receive an answer i will post it there!

thanks for your support!

[Former Member]

Hi Christian,

Regarding your query - how many columns are you selecting? How many rows are returned?

Try minimizing the returned dataset as possible since the returnred dataset size greatly affects the borecordset performance.

Could you please post the code for your query?

Thanks,

Ori.

[former_member185682]

Hi Christian,

I believe that you need store the password in a configuration file.

As you said: "When you retrieve the value of the DbPassword field, ****** is returned" is the same advise that is in the sdk help

In this post Sql connection string SAP Business One | SCN, Owen Slater said: "There is no way to get the SQL connection string that the SBO client is using programmatically".

Regards,

Diego

[Former Member]

thanks diego, i've already read the documentation and read slater's comment, dating 2011.. i hoped someone from sap ( official support i mean ) could shed some light on this issue, since 4 years have passed!

[former_member185682]

I hope someone have some light about this, there is some threads open about it.

Good luck.

[edy_simon]

Hi Christian,

It would never happened.

Password is supposed to be secret and guarded.

If SAP is giving you a method to decrypt the password, then they are compromising the security of the system it self.

I agree with , Recordset object only put a wrapper to your SQL query and execute it in DBServer.

In terms of performance, I have never had much different. Especially when you said simple queries.

Can you give us your simple query and let us try so we can learn together ?

Regards

Edy