ODBC driver and MS SQL Server Report Builder

[marcelorsc]

Hi all,

We have a request to build a connection with an internal group using the ODBC driver and MS SQL Server Report Builder.   I configured and successfully tested the latest ODBC driver (V 1.0.9) with Interactive SQL and also SQL Server Management Studio on my Windows 7 laptop.   However, when configuring a data source to connect via "SQL Server Report Builder", although I can configure DSN = ServiceNow (which is available from the choices on the UI as I selected ODBC), when I test the connection I get the following error:

ERROR [HY000] [SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot connect to the instance. [dataSource=ServiceNow] [user=odbc_user]You are not authorized to perform this operation. Please verify your credentials and try again.[1001]

ERROR [HY000] [SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot connect to the instance. [dataSource=ServiceNow] [user=odbc_user]You are not authorized to perform this operation. Please verify your credentials and try again.[1001]

The same credentials I'm using above work perfectly fine when testing the connection with the ODBC driver (via ODBC Administrator UI).

Has anyone come across a similar issue?

Thanks,

Marcelo Correia

[marcelorsc · Accepted solution]

The issue was related to the 64-bit ODBC driver I was using.   The SQL Server Report Builder I have installed required a 32-bit driver.   I removed the 64-bit ODBC driver (via Windows Control Panel), rebooted my laptop and made a clean installation of the ODBC 32-bit driver. After that I was able to connect and run queries with no problems.  

[pavelmuller]

Hi,

I'd like to discuss the idea of connecting your SQL Server Report Builder directly to your production ServiceNow instance. In my experience, this might put an unnecessary load to the instance. Imagine a large incident or task table performing aggregated SQL queries during your business hours. This is a way how to affect your end-users.

The option would be to have a copy of the data and use it for reporting. Either having a separate reporting ServiceNow instance with the same data or an on-premise database containing the ServiceNow data you need for reporting. In your case, data having in a standard SQL Server database. Either build such a replication tool yourself or use existing tools such as SnowMirror.

Kind Regards,

Pavel