Re: odbc db link to MSSQL

Ok success! Thanks for your help. I think I had a mix up with some different tests I was trying.

On Mon, Jul 30, 2018 at 2:50 PM, Jeff Chirco <backseatdba_at_gmail.com> wrote:

> Darn still having trouble. I can connect from the Linux OS using isql
> [odbc_dbname] [user] [password] - v But just can't query over dblink from
> Oracle.
>
> On Mon, Jul 30, 2018 at 12:51 PM, Scott Canaan <srcdco_at_rit.edu> wrote:
>
>> INBOUND_CONNECT_TIMEOUT_LISTENER = 0
>>
>>
>>
>> SID_LIST_LISTENER =
>>
>> (SID_LIST =
>>
>> (SID_DESC =
>>
>> (GLOBAL_DBNAME = dbname2.rit.edu)
>>
>> (SID_NAME = DDNAME2)
>>
>> (ORACLE_HOME = /oracle/app/product/12.1.0.2)
>>
>> )
>>
>> (SID_DESC =
>>
>> (SID_NAME = PLSExtProc)
>>
>> (ORACLE_HOME = /oracle/app/product/12.1.0.2)
>>
>> (PROGRAM = extproc)
>>
>> (ENVS = "EXTPROC_DLLS=ONLY:/oracle/data/scripts/lib/syslog.so")
>>
>> )
>>
>> #
>>
>> # SQL Server
>>
>> #
>>
>> (SID_DESC =
>>
>> (GLOBAL_DBNAME = dbname.ad.rit.edu)
>>
>> (PROGRAM = dg4odbc)
>>
>> (SID_NAME = DBNAME)
>>
>> (ORACLE_HOME = /oracle/app/product/12.1.0.2)
>>
>> (ENV = "LD_LIBRARY_PATH=/usr/lib64:/oracle/app/product/12.1.0.2/lib
>> :/oracle/app/product/12.1.0.2/hs")
>>
>> )
>>
>> )
>>
>>
>>
>> DYNAMIC_REGISTRATION_LISTENER = OFF
>>
>>
>>
>> ADMIN_RESTRICTIONS_LISTENER = ON
>>
>>
>>
>> LISTENER =
>>
>> (DESCRIPTION_LIST =
>>
>> (DESCRIPTION =
>>
>> (ADDRESS_LIST =
>>
>> (ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.99.999)(PORT = 1521))
>>
>> (ADDRESS = (PROTOCOL = IPC)(KEY=EXTPROC))
>>
>> )
>>
>> (ADDRESS_LIST =
>>
>> (ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.99.999)(PORT = 1524))
>>
>> )
>>
>> )
>>
>> )
>>
>>
>>
>> ADR_BASE_LISTENER = /oracle/app
>>
>>
>>
>> SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER = OFF
>>
>>
>>
>> *Scott Canaan ‘88*
>>
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> srcdco_at_rit.edu | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>> *From:* Jeff Chirco [mailto:backseatdba_at_gmail.com]
>> *Sent:* Monday, July 30, 2018 3:48 PM
>> *To:* Scott Canaan; oracle-l-freelist
>>
>> *Subject:* Re: odbc db link to MSSQL
>>
>>
>>
>> Yeah I have entries in the the tnsnames and listener. What do your
>> listener.ora file look like?
>>
>> Thank you for your help.
>>
>>
>>
>> On Mon, Jul 30, 2018 at 12:40 PM, Scott Canaan <srcdco_at_rit.edu> wrote:
>>
>> It doesn’t matter where it is and what it’s called since the path to it
>> is in the initDBNAME.ora file.
>>
>>
>>
>> Of course you have an entry in the tnsnames.ora file and the port is open
>> between the servers.
>>
>>
>>
>> Can you tnsping the SQL Server?
>>
>>
>>
>> *Scott Canaan ‘88*
>>
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> srcdco_at_rit.edu | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>> *From:* Jeff Chirco [mailto:backseatdba_at_gmail.com]
>> *Sent:* Monday, July 30, 2018 3:31 PM
>> *To:* Scott Canaan
>> *Subject:* Re: odbc db link to MSSQL
>>
>>
>>
>> Yep I have all that. And I duplicated what you have except my file is
>> name "odbc.ini" and in a different location. I don't have it named
>> ".odbc.ini"
>>
>>
>>
>> On Mon, Jul 30, 2018 at 10:02 AM, Scott Canaan <srcdco_at_rit.edu> wrote:
>>
>> We are doing this and have it working to both SQL Server and MariaDB.
>>
>>
>>
>> The entries in .odbc.ini (location specified in
>> $ORACLE_HOME/hs/admin/initDBNAME.ora) look like:
>>
>>
>>
>> [DBNAME]
>>
>> Driver = ODBC Driver 17 for SQL Server
>>
>> Trace = No
>>
>> Server = 999.99.99.99,1433
>>
>>
>>
>> The entry in /etc/odbcinst.ini is:
>>
>>
>>
>> [ODBC Driver 17 for SQL Server]
>>
>> Description=Microsoft ODBC Driver 17 for SQL Server
>>
>> Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
>>
>> Threading = 1
>>
>> UsageCount = 1
>>
>>
>>
>> Did you create an init.ora file for the instance in
>> $ORACLE_HOME/hs/admin? It should be named initDBNAME.ora and look like:
>>
>>
>>
>> #
>>
>> # HS init parameters
>>
>> #
>>
>> HS_FDS_CONNECT_INFO = DBNAME
>>
>> HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
>>
>> HS_NLS_NCHAR=UCS2
>>
>> HS_TRANSACTION_MODEL=READ_ONLY
>>
>> #
>>
>> # ODBC specific environment variables
>>
>> #
>>
>> set ODBCINI=/home/oracle/.odbc.ini
>>
>>
>>
>> #
>>
>> # Environment variables required for the non-Oracle system
>>
>> #
>>
>> #set <envvar>=<value>
>>
>> *Scott Canaan ‘88*
>>
>> *Sr Database Administrator *Information & Technology Services
>> Finance & Administration
>>
>>
>> *Rochester Institute of Technology *o: (585) 475-7886 | f: (585) 475-7520
>>
>> srcdco_at_rit.edu | c: (585) 339-8659
>>
>> *CONFIDENTIALITY NOTE*: The information transmitted, including
>> attachments, is intended only for the person(s) or entity to which it is
>> addressed and may contain confidential and/or privileged material. Any
>> review, retransmission, dissemination or other use of, or taking of any
>> action in reliance upon this information by persons or entities other than
>> the intended recipient is prohibited. If you received this in error, please
>> contact the sender and destroy any copies of this information.
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freeli
>> sts.org] *On Behalf Of *Jeff Chirco
>> *Sent:* Monday, July 30, 2018 12:51 PM
>> *To:* oracle-l-freelist
>> *Subject:* odbc db link to MSSQL
>>
>>
>>
>> Anybody have success with getting ODBC driver for SQL Server installed on
>> Oracle Linux 7 and getting a database link to work from 12.2
>>
>> When I run a select over the db link I get this error.
>>
>> ORA-28500: connection from ORACLE to a non-Oracle system returned this
>> message:
>>
>> ORA-02063: preceding line from TESTMSSQL
>>
>> I didn't try the easysoft driver cause didn't want to have to buy a
>> driver for this. But if it is needed I will explore that option.
>>
>>
>>
>> I installed unixODBC 2.3.6 from http://www.unixodbc.org
>>
>>
>>
>> # remove any existing unixODBC drivers - be very careful with 'sudo rm'!
>>
>> sudo rm /usr/lib64/libodbc*
>>
>>
>>
>> # install the unixODBC driver
>>
>> # note, adding "--enable-stats=no" here is not specified by Microsoft
>>
>> export CPPFLAGS="-DSIZEOF_LONG_INT=8"
>>
>> ./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 --enable-stats=no
>> 1> configure_std.log 2> configure_err.log
>>
>> make 1> make_std.log 2> make_err.log
>>
>> sudo make install 1> makeinstall_std.log 2> makeinstall_err.log
>>
>>
>>
>> # the Microsoft driver expects unixODBC to be here
>> /usr/lib64/libodbc.so.1,
>>
>> # so add soft links to the '.so.2' files
>>
>> cd /usr/lib64
>>
>> sudo ln -s libodbccr.so.2 libodbccr.so.1
>>
>> sudo ln -s libodbcinst.so.2 libodbcinst.so.1
>>
>> sudo ln -s libodbc.so.2 libodbc.so.1
>>
>>
>>
>> Then I downloaded Microsoft ODBC Driver 17 from
>> https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/
>> installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
>>
>> sudo su
>>
>> #Download appropriate package for the OS version
>>
>> #Choose only ONE of the following, corresponding to your OS version
>>
>> #RedHat Enterprise Server 7
>>
>> curl https://packages.microsoft.com/config/rhel/7/prod.repo >
>> /etc/yum.repos.d/mssql-release.repo
>>
>>
>>
>> exit
>>
>> sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
>>
>> sudo ACCEPT_EULA=Y yum install msodbcsql17
>>
>>
>>
>>
>>
>> My /etc/odbc.ini looks like this
>>
>> [testmssql]
>>
>> Driver=ODBC Driver 17 for SQL Server
>>
>> Server=server_foo
>>
>> User=test
>>
>> Password=password
>>
>> Database=mydb
>>
>> Port=1433
>>
>>
>>
>> /etc/odbcinst.ini
>>
>> [ODBC Driver 17 for SQL Server]
>>
>> Description=Microsoft ODBC Driver 17 for SQL Server
>>
>> Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
>>
>> UsageCount=1
>>
>>
>>
>> From Oracle I create a database link as
>>
>> create database link testmssql connect to test identified by password
>> using ‘testmssql’;
>>
>>
>>
>>
>>
>> I am very confused how to troubleshoot this.
>>
>>
>>
>> Any help or if you have your own notes would be helpful.
>>
>>
>>
>>
>>
>>
>>
>
>

--
http://www.freelists.org/webpage/oracle-l