Creating a remote server Data Source in Service Catalog for using in Report Query Builder

[Kerry Hamilton]

I first tried to add the connection as JDBC. I received error- "Test failed. [ Cannot create PoolableConnectionFactory  Login failed for user  Domain Username   ClientConnectionId 3894a73b 209e 42f0 9bbe f5a3c2e16e75 ]"

Then I created a system dns ODBC on the Service Catalog server. This connected successfully. I tried to use this dns in the Report Builder under ODBC and still got an error about JDBC.

Please advise where/how I need to create the JDBC connection to make this available in Report Builder.

[Gregory Chavez]

Hello, @Kerry Hamilton

You're encountering issues with setting up a JDBC connection in Report Builder. Here are some steps to help you resolve this:

Verify JDBC Driver: Ensure that the correct JDBC driver for your database is installed on the Service Catalog server. You can download the appropriate driver from the database vendor's website.

 Verify JDBC Driver
Ensure that the correct JDBC driver for your database is installed on the Service Catalog server. You can download the appropriate driver from the database vendor's website.

2. Configure JDBC Connection in Report Builder
Open Report Builder.
Create a New Data Source:
Go to the Report Data pane and click New, then Data Source.
In the Data Source Properties dialog box, enter a name for the data source.
Select Use a connection embedded in my report and choose Microsoft SQL Server as the connection type.
Build the Connection String:
Click Build to create the connection string. Enter the server name, database name, and authentication details.
Ensure the connection string is correctly formatted. For example:

jdbc:sqlserver://yourserver.database.windows.net:1433;database=yourdatabase;user=yourusername;password=yourpassword;

Test the Connection:
After configuring the connection string, click Test Connection to ensure it works correctly.
3. Check Permissions and Credentials
Ensure that the user account used for the connection has the necessary permissions to access the database. Double-check the login credentials and ensure they are correctly entered in the connection settings.

4. Using a Shared Data Source
Create a Shared Data Source: On your report server, create a shared data source that can be used by multiple reports. This can simplify management and ensure consistency.
Configure Report Builder: In Report Builder, select the shared data source instead of creating an embedded one. This can be done by navigating to the data source pane and selecting the shared data source from the report server.

5. ODBC Connection
Since you mentioned creating a system DSN ODBC on the Service Catalog server, ensure this DSN is correctly configured and accessible. In Report Builder, select the ODBC data source option and configure it to use the DSN you created

I hope this info is helpful to you.

Best Regard,
Gregory Chavez