Problem accessing MS SQL database through Visual Composer

[Former Member]

Platform: SAP EP 7.0

Hello,

I was able to create a system connection from the portal to an external MS SQL external datasource (not part of the SAP landscape) and it tested fine. However when I try to look for stored procedures and tables with that datasource in Visual Composer, there is nothing to looking for. Could anyone tell me what am I missing from the configuration?

Thank you,

Duy

[Former Member · Accepted solution]

Hi Duy,

did you check our great [WIKI|https://www.sdn.sap.com/irj/sdn/wiki?path=/display/vc/cannotseetables&]?

There are several of hints and examples.

Best Regards,

Marcel

[Former Member · Accepted solution]

Thank you for the tips. I tried to create the BI JDBC connector but now my connection test kept failing. I know I am using the right user account for the SQL. Is the URL and Driver for a BI JDBC connect the same as the regular JDBC?

Example:

jdbc:sap:sqlserver://<hostname>:<port>;DatabaseName=myDatabase

com.sap.portals.jdbc.sqlserver.SQLServerDriver

[Former Member · Accepted solution]

Hi,

can you take a look at the guide here: https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/92d80512-0a01-0010-32a3-cd3....

From page 13 on there is a very detailed section on how to set up a BI JDBC connection with MS SQL Server.

Hope this helps,

Holger.

[Former Member · Accepted solution]

As Holger mentioned check the document, if it doesn't work check if the microsoft webpage for the latest release of the jdbc driver and also check the log files on your db for errors in connection. You can also trace the authentication for finding further issues.

Best Regards,

Marcel

[former_member183850 · Accepted solution]

don't use the SQLServerDriver from the portal in BI JDBC, there is license problem. instead download the sql driver from MS site. the url and driver is the same as regular JDBC.

[Former Member]

We have decided to use stored procedures to get access to the back end SQL database using the original MS SQL driver. Thank you for all of your help.

[Former Member]

Hi Duy,

Just some thoughts:

what about permissions? Does the user you are using in the VC have the proper permissions?

Was the system check from the portal successful? Is the installation also with MS SQL database? Does it work with the database which is also used by the J2EE Engine?

Regards,

Holger.

[Former Member]

Hi, thanks for the quick response. The system connection tested fine and I mapped the user name with a read only account for that database. The database that I am trying to connect is on a Window 2003 server and there are no J2EE engine running on it. However our SAP landscape is using MS SQL for our database. When you mention permission, are you referring to what I meant by the read only account that I used to do the mapping or is there something else in portal that I need to be assigned to?