Skip to Content

Creating remote connection to MSSQL Server

On Linux, for creating remote connection to MSSQL Server, you will need to install unixodbc driver and MSSQL odbc driver.

1. download unixODBC-2.3.0.tar.gz from:

http://www.unixodbc.org/download.html

2.as root gunzip unixODBC-2.3.2.tar.gz

3. tar -xvf unixODBC-2.3.0.tar

4. It will create unixODBC-2.3.0 directory with all source files inside.

5. As root install unixODBC drivers

  sudo ./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no
--enable-drivers=no --enable -iconv --with-
iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE

checking for a BSD-compatible install...
/usr/bin/install -c

checking whether build environment is sane...
yes

  checking for a thread-safe mkdir -p...
/bin/mkdir -p

...

...

...

   config.status: creating Makefile

config.status: creating config.h

config.status: executing depfiles commands

config.status: executing libtool commands

      

[unixODBC-2.3.0]$ sudo make

make
all-recursive

make[1]: Entering directory
`/download/unixODBC-2.3.0'

  Making all in extras

...

...

...

  make[2]: Leaving directory
`/download/unixODBC-2.3.0'

  make[1]: Leaving directory
`/download/unixODBC-2.3.0'

  [unixODBC-2.3.0]$ sudo make install

Making install in extras

  make[1]: Entering directory
`/download/unixODBC-2.3.0/extras'

...

...

...

make[2]: Entering directory
`/download/unixODBC-2.3.0'

  make[2]: Nothing to be done for
`install-exec-am'.

  touch /etc/odbcinst.ini

   touch /etc/odbc.ini

   mkdir -p /etc/ODBCDataSources

  /usr/bin/odbc_config --header >
/usr/include/unixodbc_conf.h

   make[2]: Leaving directory
`/download/unixODBC-2.3.0'

   make[1]: Leaving directory
`/download/unixODBC-2.3.0'

6. download Microsoft ODBC driver and install odbc driver as root:

http://www.microsoft.com/en-us/download/details.aspx?id=36437

7.tar xvzf msodbcsql-11.0.2270.0.tar.gz

msodbcsql-11.0.2270.0/

msodbcsql-11.0.2270.0/include/

msodbcsql-11.0.2270.0/include/msodbcsql.h

msodbcsql-11.0.2270.0/bin/

msodbcsql-11.0.2270.0/bin/SQLCMD.rll

msodbcsql-11.0.2270.0/bin/BatchParserGrammar.dfa

msodbcsql-11.0.2270.0/bin/BatchParserGrammar.llr

...

...

...

msodbcsql-11.0.2270.0/LICENSE

msodbcsql-11.0.2270.0/README

msodbcsql-11.0.2270.0/docs/

msodbcsql-11.0.2270.0/docs/en_US.tar.gz

8. To discover location of odbcinst.ini, execute

odbc_config --odbcinstini

  /etc/odbcinst.ini

9. sudo ./install.sh verify

   Microsoft ODBC Driver 11 for SQL Server Installation Script

  Copyright Microsoft Corp.

  Starting install for Microsoft ODBC Driver 11 for SQL Server

  Checking for 64 bit Linux compatible OS
..................................... OK

  Checking required libs are installed
........................................ OK

  unixODBC utilities (odbc_config and odbcinst)
installed ..................... OK

  unixODBC Driver Manager version 2.3.0
installed ............................. OK

...

...

...

10. sudo ./install.sh install

 

Microsoft ODBC Driver 11 for SQL Server Installation Script

  Copyright Microsoft Corp.

Starting install for Microsoft ODBC Driver 11 for SQL Server

...

...

...


Microsoft ODBC Driver 11 for SQL Server registered ................... INSTALLED

Install log created at/tmp/msodbcsql.24576.20698.22397/install.log.

One or more steps may have an *. See README for more information regarding these steps.

11. Edit /etc/odbc.ini and add mssqlserver

e.g

  [SQLEXPRESS]

     Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

     Server=myhost,9856

     Database=master

     Threading=1

     UsageCount=1

12. set LD_LIBRARY_PATH  environment variable  to MSSQL server libraries in IQ-16_0.csh

         

    setenv LD_LIBRARY_PATH "/opt/microsoft/msodbcsql/lib64:$LD_LIBRARY_PATH"

13. set ODBCINI and ODBCHOME Environment variables

  setenv ODBCINI "/etc/odbc.ini"

    setenv ODBCHOME "/etc/"

14. Restart IQ Server

15. dbisql -c 'dsn=redhead_iq1603smx' -nogui

 

(DBA)> create server SQLEXPRESS class 'MSSODBC' using 'dsn=SQLEXPRESS'

  Execution time: 0.043 seconds

(DBA)> create externlogin DBA to SQLEXPRESS remote login "sa" identified by "mssqlpass"

Execution time: 0.008 seconds

(DBA)> forward to SQLEXPRESS

  0 row(s) affected

  Execution time: 0.006 seconds

(DBA)> select @@version

  expr_1

 

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

 

Microsoft SQL Server 2008 R2 (SP2) -
10.50.4000.0 (X64)

      
Jun 28 2012 08:36:30

      
Copyright (c) Microsoft Corporation

     
Express Edition with Advanced Services (64-bit) on Windows NT 6.1
<X64>

  (Build 7601: Service Pack 1)

   (1 rows)

Execution time: 1.902 seconds

(DBA)> create existing table t1 at 'SQLEXPRESS.master..t1'

Execution time: 7.31 seconds

 

(DBA)> select * from t1

       
col1

  -----------

      
1

        
2

(2 rows)

Execution time: 0.159 seconds

Tags:

No comments