How to connect BDC with Sybase Database

Using BDC Editor Tool we can generate metadata xml file for connecting BDC with Sybase database. For this we can either use ODBC or OLEDB connection type. To use ODBC we need to first create DSN. The DSN name along with login credentials needs to be used in the connection string. Once this is done the connection gets established with Sybase database and the tables get populated and subsequently create an instance out of it. But, when we try to execute any of the method instances it doesn't work. It fails saying "The LobSystemInstance Property 'AuthenticationMode' has a value that requires 'Trusted_Connection' in the connection string". The same error is shown even if we add Trusted_Connection in the connection string. The same application definition file when used in SharePoint also doesn't work.

 The issue is because of the XML file that is generated through the editor tool. As we are using ODBC connection, we need to manually make few changes in the XML file in order to make it work.

- First to connect BDC with Sybase Database using BDC editor tool, we need to use ODBC / OLEDB as connection type.

- The connection string should essentially look like this - Dsn=dsnname;uid=username;pwd=password;Trusted_Connection=yes;integrated security=true

- Create an instance of it and then export the file and save it locally as a XML file.

The following changes has to be done manually in the Application Definition file

1. As ODBC is used, do not put quotation marks on the SQL query. The quotation marks are generated by BDC Editor and we need to remove them.

For e.g.: Instead of Select "ProductId", "ProductName" from Products - use Select ProductId, ProductName from Products.

2. Do not put parameters like @pk. This is not accepted by ODBC Driver. It only accepts parameter using ?

For e.g.: Instead of Select "ProductName" from Products where ProductId=@pk use Select ProductName from Products where ProductId=?

3. Trusted connection should be in title case. It should be Trusted_Connection and not trusted_connection.

For e.g.: Instead of <Property Name="rdbconnection trusted_connection" Type="System.String">yes</Property> use

<Property Name="rdbconnection Trusted_Connection" Type="System.String">yes</Property>

4. If the Application definition file is to be used in BDC Data List web part then make sure that there is at least one method instance of type "Finder". By default, BDC editor tool doesn't generate method instance of finder. It generates method instance of type "IdEnumerator". So, change "IdEnumerator" to "Finder".

5. Possibilities are there when we map the Application definition file with BDC web part, it might throw an error saying "unable to connect to abc_instance". In this case change User DSN to System DSN as System DSN is globally available to all users.

Comments

• Anonymous
  January 16, 2009
  The comment has been removed

• Anonymous
  November 12, 2009
  Data Source=servername:portname;User ID=username;Initial Catalog=DBname;password= it will work

• Anonymous
  November 12, 2009
  I used the string as Jainraj suggests but it does not work. I am connecting to a SQL database and get the exact same errors as Gabor. Please help anyone.

• Anonymous
  November 12, 2009
  Hi Chester, If you tried connecting to SQL-server database instance then this will be helpful. Server=myServerNametheInstanceName;Database=myDataBase;Trusted_Connection=True; Please let me know the AuthenticationMode which you have passed while connecting to the database.

• Anonymous
  November 12, 2009
  Thanks for your prompt response. I am using SQL server authentication. I do not have an instance at all. COuld that be a problem

• Anonymous
  November 12, 2009
  I suspect the problem will be in passing the credentails. => Create .udl file from the machine and test if you are able to connect to the server. => If the apppool identity have the access to your Dabase server, please use RevertToSelf => If the Logged Client have the access, please use Passthrough Authentication mode =>and then test with BDC definition Editor tool, it comes with MOSS 2007 sdk and check you are able to connect to the server and get the tables list. =>You can also use Rdbcredential but you need to configure SSO to pass the credentails to the SQL-Server. I hope these steps will assist you to troubleshoot the issue.