INTERNAL: Steps used to connect to an MS SQL database from Linux

Zachary Norman Monday, January 21, 2013

ON THE WINDOWS MACHINE:

0) The SQL server was already installed on our Windows XP Virtual Machine. However, some additional configuration was needed in order to get it to work.

1) The first thing I needed to confirm was that the SQL server was allowing for remote connection. To do this, I used the "SQL Server Surface Area Configuration" tool. Inside this tool, I clicked "Surface Area Configuration for Services and Connections". In this dialog, I selected "Remote Connections" under the "Data Engine" branch of the database tree. I made sure "Local and remote connections" and "Using both TCP/IP and named pipes" were selected.

2) By default, the MS SQL server listens on port 1433. To test the connection, I was trying to use the SQLCMD command line utility. However, the connection was failing. Therefore, I used the "SQL Server Configuration Manager" to check the TCP/IP configuration of the server. To do this, I clicked on the "Protocols for SQLEXPRESS". I then right clicked on TCP/IP and selected "properties". I clicked on the IP Addresses tab and set TCP Port value to 1433 for every IP option (by default it was only set for IP1). After I made this change, I was able to connect to the database using the SQLCMD utility.

3) I then used the "SQL Server Management Studio" program to change the Server Authentication to "SQL Server and Windows Authentication mode". To do this, I right-clicked on the SQLEXPRESS server and selected "Properties". I then clicked on "Security" and selected the button next "SQL Server and Windows Authentication mode". When the SQL server was in "Windows Authentication mode" only, I wasn't able to connect using the "tech_support" user name; there was an error that said, "...tech_support is not a trusted user". After making this change, I was able to connect to the server using the tech_support user name.

ON THE LINUX MACHINE:

1) I altered the odbc.ini file to have the SQL Server Wire Protocol data source to point to our server which was located at 10.17.1.48 port:1433. The "odbc.ini" is attached to this email.

2) I then setup the IDL environment using the following commands in the terminal:

. /usr/local/itt/idl/idl80/bin/idl_setup.bash

export ODBCINI=/home/tech_support/dataminer/odbc.ini

3) I then launched idl and entered the following commands:

IDL> odb=idldbdatabase()

IDL> odb.connect,datasource='JIMSQLSERVER',USER_ID='tech_support', $

PASSWORD='password!'

IDL> help, odb

ODB OBJREF = <ObjHeapVar1(IDLDBDATABASE)>

When I tried to connect without explicitly stating the USER_ID and PASSWORD, the following error occurred (even though I tried to specify these in the odbc.ini file):

IDL> odb=idldbdatabase()

% Loaded DLM: DATAMINER.

IDL> odb.connect,datasource='JIMSQLSERVER'

% IDLDBDATABASE::CONNECT: ODBC [ITT VIS][ODBC SQL Server Driver]Insufficient

information to connect to the data source

% Error occurred at: $MAIN$

% Execution halted at: $MAIN$

ODBC.INI

[ODBC Data Sources]
DB2 Wire Protocol=DataDirect 5.3 DB2 Wire Protocol
Sybase Wire Protocol=DataDirect 5.3 Sybase Wire Protocol
Oracle=DataDirect 5.3 Oracle
Oracle Wire Protocol=DataDirect 5.3 Oracle Wire Protocol
MySQL Wire Protocol=DataDirect 5.3 MySQL Wire Protocol
Informix Wire Protocol=DataDirect 5.3 Informix Wire Protocol
JIMSQLSERVER=DataDirect 5.3 SQL Server Wire Protocol

[JIMSQLSERVER]
Driver=/usr/local/itt/idl/idl80/bin/bin.linux.x86_64/dm/lib/DMmsss23.so
Description=DataDirect 5.3 SQL Server Wire Protocol
Address=10.17.1.48, 1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=master
LoadBalancing=0
LogonID=tech_support
Password=password!
QuotedId=No
SnapshotSerializable=0

[ODBC]
TRACE=1
TraceFile=odbctrace.out
TraceDll=/usr/local/itt/idl/idl80/bin/bin.linux.x86_64/dm/lib/odbctrac.so
InstallDir=/usr/local/itt/idl/idl80/bin/bin.linux.x86_64/dm

Reviewed by DS 9/17/2014