Portal Test Connection Failed for JDBC Connector

[raju_saravanan]

Hi,

We are trying to create a JDBC system in EP to connect SQL Server 2005 database. Our aim is to build a model in VC using data services from SQL Server 2005.

We deployed the Driver file sqljdbc.jar and connection was successful when tested by creating a data source in Visual Administrator-> JDBC Connector

We have created a system in EP with user mapping type (admin,user) and user mapping also done for system alias with the SQL Server administrator user id and password. UME is maintained in ABAP Stack. When we try for test connection with the system created in EP for JDBC connection, the system throws an error message like “Connection failed. Make sure user mapping is set correctly and all connection properties are correct“

Test Details:

The test consists of the following steps:

1. Retrieve the default alias of the system

2. Check the connection to the back end application using the connector defined in this system object

Results

Retrieval of default alias successful

Connection failed. Make sure user mapping is set correctly and all connection properties are correct.

The following are the URL and driver class name:

URL: jdbc:sqlserver://<host_name>:1433;DatabaseName=”db_ name”

Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

Is there any other setting to be maintained in the Visual Administrator or in the Portal?

Kindly let us know any clue to overcome this issue.

Quick response highly appreciated.

Regards

Saravanan.r

[raju_saravanan]

Solved

[former_member185086]

Hi Saravanan

I am suffering from same problum and still not solved i send u some link see if it

helpful for u.

http://help.sap.com/saphelp_nw70/helpdata/en/45/ef362dc57b1e46aa6f445ec6247103/frameset.htm

https://wiki.sdn.sap.com/wiki/display/VC/JDBCConnectionSetup

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/webservices/how%20...

Regards

````Satish````

[raju_saravanan]

Hi Satish

We have already gone through those documents. But no luck for us. If you have succeeded, Please let us also know.

Regards

Saravanan.r

[lanzlf]

Hi

- Which driver are you using ? the SAP one ?

- Have you tested a JDBC connector as recommended with the portal connector testing tool ? (see it on Support Platform)

- Are you sure the user/password for this connector is correct ? means can you reach the database by this port with this user /password ?

HTH,

Luis

[former_member185086]

Hi Saravanan

As i told u My Problum is not solved thats why i Cant give u the Concrete solution

asper my knowledge whatever i have that i Provide u lets something is useful for u

first change it and see the result

Driver name = com.sap.portals.jdbc.sqlserver.SQLServerDriver

conn url = jdbc:sap:sqlserver://<Namemachine>:1433;DatabaseName=<NewTable>

and check it to

jdbc:sap:sqlserver://<ip address of machine>:1433;DatabaseName=NewTable

and let me know

regards

````Satish````

[raju_saravanan]

Hi Luis

We have used the following driver file and driver class name

1) Driver file: sqljdbc.jar

Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

2) Kindly Explain how to proceed with point number 2 as you mentioned. From where we can get the portal connector testing tool ..? Kindly send me the link.

3) We deployed the Driver file sqljdbc.jar and connection was successful when tested by creating a data source in Visual Administrator.

(We got the result of sample table which created in SQL Server 2005 through visual administrator)

Regards

Saravanan.r

Message was edited by:

Saravanan Raju

[former_member185086]

Hi Raju

1.: i want to say in place of <b>machin name</b> give the <u><b>Ip address</b></u> of that machin in which your database server is .

2: Check Your user id and password which u assign at user management i.e user id and password which u have given at the time of <b><u>database instlattion</u></b>

3: Driver file = com.sap.portals.jdbc.sqlserver.SQLServerDriver

4:Connection URL = jdbc:sap:sqlserver://<J2EESERVER>:1433;DatabaseName=<NewTable>

as i think small thing in which we stucked

Let me know

````Satish````

[raju_saravanan]

Hi Sathish

All the stated steps by you are over. But no luck for us.

Regards

Saravanan.r

[ankur_garg5]

Hi Saravan,

Let me go one by one according to my doc (<a href="https://www.sdn.sap.comhttp://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/library/webservices/how%20to%20create%20a%20web%20service%20using%20enterprise%20javabeans.pdf">How to create Web Services.....</a>):

1)Have you configured the JDBC driver?By JDBC driver, i mean all the three JAR files: msbase.jar, mssqlserver.jar, and msutil.jar

2)Have you configured the datasource in the same way as done in the document?

3)You must have created the tables in your SQL Server as I have explained. Have you tested your datasource on these tables as shown on page 12?

Bye

Ankur

[raju_saravanan]

Hi Ankur

As we have told earlier, we have followed all the steps & we are trying to work with SQL Server 2005 and for that sqljdbc.jar driver file enough.

We have already created tables in SQL Server 2005 and those tables are tested through Visual administrator by creating a datasource.

The Problem is only with system creation in portal.

Regards

Saravanan.r

[former_member185086]

Hi Ankur

I have done according to your document (How to create)

But at the time of testing of database it gives error that is

java.rmi.RemoteException: com.sap.engine.services.dbpool

.exceptions.BaseRemoteException: SQL statement(s)

cannot be executed over DataSource "MSSQLSERVER".

If you are using an Open SQL DataSource, you must register your tables using JDDI

before you request them. Reason: com.sap.engine.services.dbpool.exceptions.BaseSQLException:

ResourceException in method ConnectionFactoryImpl.

relly i am unable to find where exectly i do mistake, please help me

Best Regards

```Satish````

Message was edited by: satish jhariya

Here i am using MSSQL 2000

satish jhariya

[ankur_garg5]

Hi Satish,

Did you select Vendor SQL as shown on page 11, because according to the error I guess you have selected Open SQL.

Bye

Ankur

[former_member185086]

Hi Ankur

Thanks for quick reply

Ya i have selected Vender sql.and i did this excersize 4 times but no luck

ould u please identify more point, places where i can do mistake.

best regards

```Satish````

Message was edited by: satish

should databaseserver and j2eeserver are in same place

satish jhariya

[ankur_garg5]

Hi Saravan,

I don't have any server at hand, so i can't try making a system.

Have a look at this doc:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/6209b52e-0401-0010-6a9f-d40ec3a0...

Instead of selecting BI JDBC system, select JDBC system.

Can you quickly list out the steps you performed for creating the system in portal?

Bye

Ankur

[raju_saravanan]

Hi Ankur

We have already followed all the steps in that document for JDBC connection

The following are the URL and driver class name:

URL: jdbc:sqlserver://<host_name>:1433;DatabaseName=”db_ name”

Driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver

Regards

Saravanan.r

[former_member185086]

Hi ankur

Step are as

1.i Create a Table and a User in MS SQL2000 Server Database that is in same system where J2ee server is

2. In Visual admin i uploaded jar files of JDBC

3.In Connector container tab i load reference as library:<same name as step 2>

4 I create a new datasource Here i chose

<b> .</b> Driver name = name of driver which i gave in step 2

<b> .</b> JDBC version = 1.x(No xa support)

b] .</b> In Jdbc complient

Driver name=com.sap.portals.jdbc.sqlserver.SQLServerDriver

Connection URL=jdbc:sap:sqlserver://J2EESERVER:1433;DatabaseName=NewTable

5. In additional tab at SQL engine i choose vender sql

When in DB initialization when i fired a query it give eror as sent to u

Please inform me where i did mistake

Best Regards

````Satish````

[ankur_garg5]

Hi satish,

Can you go through the following thread, maybe you find something...

/thread/402668 [original link is broken]

Bye

Ankur

[ankur_garg5]

Hi Saravanan,

Can you try the following string for the Driver class:

<b>com.microsoft.jdbc.sqlserver.SQLServerDriver</b>

Bye

Ankur

[raju_saravanan]

Hi Sathish

Did you get any soutions for this...? Til now we are not able to get any solution.

Regards

Saravanan.r

[Former Member]

Hi Saravanan and Sathish,

not sure how far you got with this. I had the same problem or at least a very similar one with access to MS SQL Server 2005 and solved it. I followed essentially very similar steps with a couple of minor twists:

1) copied the sqljdbc.jar file to the storyboard server and set the classpath environment variable to point to it.

2) I did not create a data source, just created a new driver "SQLServer".

3) Under Connector Container I cloned SDK_JDBC as "SDK_SSQL".

4) The Visual Composer property vc.bi.sqlEditor Enabled should be set to true for you to be able the SQL Editor in the Data Service in VC.

In the portal I created a system of type BI_JDBC from a template.

The connection url and driver class were as in the thread:

URL: jdbc:sqlserver:ipaddress:1433;DatabaseName=name

Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

ConnectionFActoryClass as above under 3.

User rights: admin,user

The user mapping to the system alias should be as described.

This should allow you to use the data service in VC to access SQL Server tables. If access to stored procedures (sometimes quite useful) is required, this would need to utilise the p9sqlserver.jar file already on the server.

Best Regards

Felix Logemann