Connect to MS SQL

[Former Member]

Hi,

Can anybody tell me the detailed procedure to connect MS SQL server from VC.

Which system I have to create in EP and what are parameters to that system.

Thanks in Advance.

Srinivas Sandaka

[Former Member · Accepted solution]

Hi,

you will need to:

1. create a JDBC system in the portal (EP). One of the parameters to that connector is the connection string you're using to connect to that system (it includes the host, port, username and password, and default DB).

2. Make sure the system works (right click on the system in the portal -> test connection).

3. Map the portal user you want to use in VC to a user you can use in that system. (whatever user you use to connect to the JDBC system).

4. Go to VC and log on to the portal. In the discovery pane you should the system you defined.

5. Choose that system and search for queries.

Hope this helped,

Lior

[Former Member]

Hi,

I did whatever you sugest, but the same error remains.

But I created a direct JDBC system, it is working fine and successfully tested.

It does't work with BIUDI JDBC system.

I found new parameters in BIUDI system:

<b>Fixed Catalog</b> and <b>Fixed Schema</b>

Do we need to specify anything into them.

[Former Member]

Go back to your original connection settings. In the system, check and make sure the User Management property is set to admin,user.

Make sure you have defined an System Alias.

Make sure you have inputted User Mapping for that Alias. If you do not have user mapping set it will not work. I also set the Distributed Query Engine UserID and Password to the Database UserID and Password.

Regards,

Peter

[Former Member]

Hi Peter

But the problem is that if you make the connection only based on JDBC you won’t have access from VC BI or SQL editor in the VC.

Regards,

Werner

[Former Member]

I had the same problem. I solved it by configuring JDBC Connector in the Admin Console.

Check the configuration and checking the BI Java Connectors ( see Post Installation Activities in SAP Business Warehouse 3.5, which can be accessed from SAP Service Marketplace at

service.sap.com/instguides -> SAP NetWeaver -> Release 04 )

You have to configure jdbc drivers and JDBC Connector to use these drivers.

Follow the guide...

Bye, Roberto

[Former Member]

Werner,

Yes, you are correct. You will not have access to the VC BI Wizard or SQL Editor. If this is what is desired, go back and create another system (with the same properties) but instead of using the standard JDBC system, create it this way:

System from Par -> com.sap.portal.systems.BIUDI -> SAP_BI_JDBC.

Then in the VC you should be able to run some SQL against that system and use the SQL Editor also. I suggest using the BI Query Wizard when you get into VC.

Regards,

Peter

[Former Member]

Hi.

Did you solve this problem ?

I got same problem in EP 6.0 SP 11.

If you solve this problem, let me know.

Thanks in advance.

Regards, Arnold.

[Former Member]

Dear Peter

Exactliy this ist the problem. When i definie a system with SAP_BI_JDBC I can't define "Distributed Qeuery Engine" and without that I can't connect. I guess that there is somewhere an access problem.

Regards

Werner

[Former Member]

Werner,

We are not using DQE, I was mistaken. You only need to set that property if you have DQE set up and are running queries against multiple systems and combining the data.

If you only want to create a system to a SQL DB and then run SQL against it, instead of just using stored procedures, then follow the steps in my previous post, setting your user mapping as the a DB user with full control.

Regards,

Peter

[Former Member]

Dear Peter

I had setup up first a sql System using the jdbc system. Without defining the DQE user a can not test or use the connection. The defined user has administration rights on the DB. When I define additional the DQE user the SQL connection works and I can use the system in the portal. Via VC a can access the stored procedures.

When I define now a similar (same definition) connection using the BI IDBC I can’t test the connection. When I access the connection via VC I receive the error “Error 21003: Cannot retrieve catalogs and shemas. Failed to connect to backend system. ……”

There is an other hint with the configuration in the backend system according to SP BW. I tried to make this configuration as descript in the BW installation guide. But there a have another problem finding the right class. Is this mandatory for using BI JDBC?

Regards

Werner

[Former Member]

Hi,

I got the connection with EP6 SP4 through VC.

<b>Conntection URL:</b> jdbc:sap:sqlserver://srinivas:1433;DatabaseName=Northwind

<b>Driver Class Name:</b> com.sap.portals.jdbc.sqlserver.SQLServerDriver

But in SP9 it is giving error.

Thanks

Srinivas S

[Former Member]

Hi Srinivas,

Try specifying the IP address instead of "srinivas" in the connection URL. As well check ur database name(for case sensitivity)

<b>Connection URL</b> = jdbc:sap:sqlserver://198.XXX.XX.XX:1433;DatabaseName =Northwind

<b>Driver Class Name</b> = com.sap.portals.jdbc.sqlserver.SQLServerDriver

<b>User Mapping Type</b> = user

• Create a system alias for this newly created JDBC system

• specify the database userid and password in personalization properties of the portal

• see that the server you are trying to login is SQL Authenticated or Windows Authenticated.

Its should be <b>SQL Authenticated</b> with the userid and password.

• Check whether you can access the tables from SQL Utitlity Manager with the same userid and password you have specified.

Hope this helps,

Thanks,

Praveen

PS. Dont forget to award points if found useful or solved.

[Former Member]

Hi,

I tried as you sugest but no luck.

Thanks in advance

Srinivas Sandaka

[Former Member]

Can you get a connection with this connection string in another place (e.g. any other program using JDBC ?)

Lior

[Former Member]

Hi,

1. Created a system from com.sap.portal.systems.BUDI-> SAP_BI_JDBC.

2. Fill the below parameters

<b>Conntection URL:</b> jdbc:sap:sqlserver://srinivas:1433;DatabaseName=Northwind

<b>Driver Class Name:</b> com.sap.portals.jdbc.sqlserver.SQLServerDriver

3. Done the usermapping.

When I test this system, it returns an error:

<i>Retrieval of default alias successful

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

Can you please guide me on this

Thanks in advance

Srinivas Sandaka

[Former Member]

try removing the '//' from the connection string:

jdbc:sap:sqlserver:srinivas:1433;DatabaseName=Northwind

and check again