ODBC Driver Issues

[rob_blakey]

Hello Community,

I have followed the instructions 100% on the wiki and am still having issues connecting via iSQL. Based off the below log from ISQL is anyone able to advise why this isn't working or what I am doing wrong?

Following on from this, when I attempt to connect via the Data Source Administration I get the following error:

---------------------------

Database Error: 1001

---------------------------

[SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot connect to the instance. [dataSource=ServiceNow] [user=adm_184106]java.net.SocketTimeoutException: connect timed out[1001]

---------------------------

OK     Cancel    

---------------------------

System:
Windows 7 - 64 Bit

Driver: ODBC Version

I have setup all of the users and am connecting via the following:

customconnect "DSN=<System DSN>;UID=<username>;PWD=<password>"

Logger for 'glide' has not been configured by the container, configuring now:
Configuring log handler: java.util.logging.ConsoleHandler
Setting useParentHandlers=false for Logger 'glide'
Overriding formatter to: com.glide.util.DefaultLogFormatter (for handler: java.u
til.logging.ConsoleHandler)
Product Version: 8.0.0.0017
OpenAccess IP Configuration file: <C:\Program Files (x86)\ServiceNow\ODBC\ip\bin
\C:\Program Files (x86)\ServiceNow\ODBC\cfg\oadm.ini>
Platform: IWINDOWS32
Architecture: 32-bit
Build: Unicode
Tracing Level:   Error Tracing


**********Environment Variables   *********
PATH = <C:\ProgramData\Oracle\Java\javapath;C:\Windows\system32;C:\Windows;C:\Wi
ndows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\>
LD_LIBRARY_PATH = <NOT_SET>
SHLIB_PATH = <NOT_SET>
CLASSPATH = <NOT_SET>
ODBCINI = <NOT_SET>
********************************************************
SQLDRV :   [TID: 17BC]:[Tue Jun 30 14:33:52.813 2015] java_drv.c:849: Error from
ipConnect()
SQLDRV :   [TID: 17BC]:[Tue Jun 30 14:33:52.822 2015] damdrv.c:637: sqldrv_connec
t(): IP connect() returned error while connecting to datasource:ServiceNow
SQL: Can't connect to database
The following error information describes the failure
ODBC Call         = SQLConnect()
SQL State         = HY000
Native error   = 1001(3E9)
Error Message = [SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot co
nnect to the instance. [dataSource=ServiceNow] [user=adm_184106]java.net.SocketT
imeoutException: connect timed out
SQL: isql_connect() failure
Elapsed time 40877 ms.

[bernyalvarado]

Hi Rob

By the wiki, are you referring to this page: http://wiki.servicenow.com/index.php?title=Using_Interactive_SQL_With_ODBC#gsc.tab=0 ?

You may have note that there's some important constraints to take into account. For instance, the password cannot contain special characters.

Also, you may want to validate that your firewall is not the one blocking the connection.

Thanks,

Berny

[rob_blakey]

Hi Berny,

I was advised that for passwords that contain special characters you just have to put them in "" hence I am using the method I am using.

customconnect "DSN=<System DSN>;UID=<username>;PWD=<password>"

From a firewall perspective how would I know what to add as an exception rule ?

Regards,
Rob

[tony_barratt · Accepted solution]

Hi Rob,

As you mention customconnect does allow the use of passwords with special characters.

ServiceNow KB: ODBC Troubleshooting Checklist (KB0538995)

..

4. Does your user name and/or password contain special characters?
In this case, customconnect should be used instead of connect:

customconnect "DSN=<System DSN>;UID=<username>;PWD=<password>"

..

You can check the ODBC driver in the following manner:

..

Installing the ODBC Driver - ServiceNow Wiki

6.1 Checking the ODBC Driver Version

To check the build date and time of the ODBC driver, use CheckVersion located in the Service-Now\ODBC\ip\tools folder. This is an executable Windows host script that reports the build date and time of the current ODBC driver. Use it to assist ServiceNow Technical Support to determine which build of the ODBC driver is running. If the CheckVersion tool is absent, the ODBC driver is out of date; upgrade to the current version. To check the version of an older ODBC driver, see the previous version information.

Note: The ODBC installation also has a Service-Now\ODBC\tools folder, which is not the correct path for the CheckVersion tool.

..

The latest version mentioned in the release notes is 1.0.9

ODBC Driver Release Notes - 1.0.9 - ServiceNow Wiki

Getting back to your original issue where you see an error ".. java.net.SocketTimeoutException: connect timed out" this could be due to the instance URL being incorrectly specified

ServiceNow KB: ODBC Error Messages: Verifying that the URL of your instance is correct (KB0538955)

You can check for connectivity from you ODBC host machine, from a cmd window with:

telnet instancenow.service-now.com 443

If you do not have connectivity you should check with the the Enterprise IT Department if non-browser traffic is allowed towards instancename.service-now.com   on port 443.

It could be a proxy needs to be defined.

There is a thread here which discusses defining a proxy for ODBC

I am not able to connect to servicenow instance through ODBC driver

In overview, if a proxy is required, properties need specifying, via the Management Console, like so:

proxy_host = say, proxy.company.com

proxy_port = say, 8080

plus proxy_user_name and proxy_password

So to answer your question, from a firewall perspective you need to facilitate access from your ODBC host to instancename.service-now.com   on port 443.

Please update with any progress of further questions.

Best Regards

Tony

[rob_blakey]

Hi Tony,

Thanks for that, in short, the information you provided was helpful as I wasn't aware it was using port 443.

Now that I have that information I have fixed up our firewall and it now works perfectly.

Thanks.