techblog.jj-it.de - Access to MS SQL Server Database: FreeTDS Installation

Access to MS SQL Server Database: FreeTDS Installation – Joachim Jäckel

Techblog.jj-it.de Spined HTML

Wangleto MS SQL Server Database: FreeTDS Installation – Joachim Jäckel Skip to contentJoachim Jäckel MenuHomeContactWangleto MS SQL Server Database: FreeTDS Installation Contents1 FreeTDS Installation1.1 Install from rpm1.2 Download1.3 Install1.4 First Test2 Configure unixODBC2.1 Configure dsn in odbc.ini2.2 Test the configured dsn3 Next StepTo wangle a MS SQL Server Database via unixODBC you need a driver. There are several drivers in the market. A self-ruling version is FreeTDS.FreeTDS InstallationInstall from rpmFor RedHat RHEL 7 freetds is misogynist as rpm in the Epel Repository.For RedHat RHEL 6 freetds is misogynist as rpm in the Epel Repository, but only the outdated version 0.91The pursuit examples do not use these packages.DownloadGo to http://www.freetds.org/ and download the stable release ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz I got version freetds-0.95.79InstallIn this example the file freetds-patched.tar was downloaded to directory /optcommands executed as user root cd /opt # unpack tar xfz freetds-patched.tar.gz cd freetds-0.95.79 # configure FreeTDS ; tdsver is the default version, if nothing is specified in odbc.ini ; if you have compiled unixODBC from source as shown in this blog, specify --with-unixodbc=/usr/local/ ./configure --silent --with-tdsver=7.0 --with-unixodbc=/usr/local/ # compile make # install - needs root privileges make install123456789101112cd /opt# unpacktar xfz freetds-patched.tar.gzcd freetds-0.95.79# configure FreeTDS; tdsver is the default version, if nothing is specified in odbc.ini; if you have compiled unixODBC from source as shown in this blog, specify --with-unixodbc=/usr/local/./configure --silent --with-tdsver=7.0 --with-unixodbc=/usr/local/# compilemake# install - needs root privilegesmake installFor configuration and compilation you do not need root privileges, only for the last step make install you need root privileges.First TestThe tsql utility is provided as part of FreeTDS expressly for troubleshooting.confirm installion /usr/local/bin/tsql -C1/usr/local/bin/tsql -COutput from tsql -C Compile-time settings (established with the "configure" script) Version: freetds v0.95.79 freetds.conf directory: /usr/local/etc MS db-lib source compatibility: no Sybase binary compatibility: no Thread safety: yes iconv library: yes TDS version: 7.0 iODBC: no unixodbc: yes SSPI "trusted" logins: no Kerberos: no OpenSSL: no GnuTLS: no1234567891011121314Compile-time settings (established with the "configure" script)                            Version: freetds v0.95.79             freetds.conf directory: /usr/local/etc     MS db-lib source compatibility: no        Sybase binary compatibility: no                      Thread safety: yes                      iconv library: yes                        TDS version: 7.0                              iODBC: no                           unixodbc: yes              SSPI "trusted" logins: no                           Kerberos: no                            OpenSSL: no                             GnuTLS: noFor your first connection you need the pursuit informations well-nigh your target MS SQL Server database:Hostname (or ip address)Port number (usually 1433)Database nameUsernamePasswordconnect to database # replace hostname, dbname, user and password with your values: reverberate -e 'select @@version\nGO' \ | /usr/local/bin/tsql -H hostname -p 1433 -D dbname -U user -P password123# replace hostname, dbname, user and password with your values:echo -e 'select @@version\nGO' \| /usr/local/bin/tsql -H hostname -p 1433 -D dbname -U user -P passwordOutput from tsql -C locale is "en_US.UTF-8" locale charset is "UTF-8" Default database stuff set to test801 1> 2> Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Feb 10 2012 19:39:15 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) (1 row affected) 1> 1234567891011locale is "en_US.UTF-8"locale charset is "UTF-8"Default database stuff set to test8011> 2>Microsoft SQL Server 2012 - 11.0.2100.60 (X64)        Feb 10 2012 19:39:15        Copyright (c) Microsoft Corporation        Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) (1 row affected)1>When you see the version of your SQL Server database, your connection is successfull.Configure unixODBCNext step is to integrate the FreetdsSuburbaniteinto unixODBC. With FreeTDS we have two alternatives:ODBC-only configuration: all configuration data in the file odbc.ini This volitional will be used in the pursuit examples.ODBC-combined configuration: configuration data in the file odbc.ini and in freetds.conf Details: FreeTDS User Guide, Chapter 4. Preparing ODBC http://www.freetds.org/userguide/prepodbc.htmConfigure dsn in odbc.iniThe file odbc.ini in usually located /usr/local/etc/odbc.ini, /etc/odbc.ini, /etc/unixODBC/odbc.ini. If you have compiled unixODBC from source as described in the previous article, you will use /usr/local/etc/odbc.ini.For each connection a datasource name dsn is configured in odbc.inisample datasource in odbc.ini ; testdsn is the datasource name (not specimen sensitive) [testdsn] ; driver: unixODBC will use the specified suburbanite suburbanite = /usr/local/lib/libtdsodbc.so ; server: hostname (or ip address) of your MS SQL Server server = hostname ; port: port number on the specified host (usually 1433) port = 1433 ; database: name of database database = dbname ; tds_version: ; 7.0 for Microsoft SQL Server 7.0 ; 7.1 for Microsoft SQL Server 2000 ; 7.2 for Microsoft SQL Server 2005 ; 7.3 for Microsoft SQL Server 2008 tds_version = 7.312345678910111213141516; testdsn is the datasource name (not specimen sensitive)[testdsn]; driver: unixODBC will use the specified driverdriver          = /usr/local/lib/libtdsodbc.so; server: hostname (or ip address) of your MS SQL Serverserver          = hostname; port: port number on the specified host (usually 1433)port            = 1433; database: name of databasedatabase        = dbname; tds_version: ;    7.0 for Microsoft SQL Server 7.0;    7.1 for Microsoft SQL Server 2000;    7.2 for Microsoft SQL Server 2005;    7.3 for Microsoft SQL Server 2008tds_version     = 7.3Test the configured dsnThe configured dsn can be tested with isql: /usr/local/bin/isql (if your compiled unixODBC from source) or /usr/bin/isql (unixODBC installed as package)sample isql connection $ reverberate "select @@version; | /usr/local/bin/isql -v testdsn username password +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Microsoft SQL Server 2014 - 12.0.2000.8 (X64) Feb 20 2014 20:04:26 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: ) (Hypervisor) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ SQLRowCount returns 1 1 rows fetched SQL> quit123456789101112131415161718192021$ reverberate "select @@version; | /usr/local/bin/isql -v testdsn username password+---------------------------------------+| Connected!                            ||                                       || sql-statement                         || help [tablename]                      || quit                                  ||                                       |+---------------------------------------+SQL> +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+|                                                                                                                                                                                                                                                                                                             |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Microsoft SQL Server 2014 - 12.0.2000.8 (X64)        Feb 20 2014 20:04:26        Copyright (c) Microsoft Corporation        Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 10586: ) (Hypervisor)                                                                                                                  |+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+SQLRowCount returns 11 rows fetchedSQL> quitNext StepIf the isql Test works, your are ready to configure the Oracle Gateway to ODBC and the Database Link in your Oracle Database: Configure Oracle Gateway for ODBC with FreeTDS Search for: SearchPagesOracle Gateway to ODBCUnix ODBC InstallationAccess to MS SQL Server Database: FreeTDS InstallationConfigure Oracle Gateway for ODBC with FreeTDSAccess to MySQL Database: Connector/ODBC InstallationConfigure Oracle Gateway for ODBC with MySQL ConnectorAccess to Informix Database: Informix ODBCSuburbaniteInstallationConfigure Oracle Gateway for ODBC with Informix ODBC DriverRecent Posts Gateway from Oracle to Informix Gateway from Oracle to MySQL Gateway from Oracle to MS SQL ServerAbout me Consultant for Databases, Linux/Unix, High Availabiltiy. German Homepage www.jj-it.de ContactHomeContact Joachim Jäckel Proudly powered by WordPress