8 Replies Latest reply on Mar 24, 2015 7:41 PM by thatJeffSmith-Oracle

    Status : Failure -I/O Error: DB server closed connection.

    Patrick J. Boyd

      I am trying to use SQL Developer to migrate several SQL server database's into Oracle.  Some of them are hosted in a SQL Server 2000 instance and the rest are on SQL Server 2005.  I am able to connect to the SQL Server 2000 databases without issue, but not the 2005 databases.  I have followed every step in several different forum posts all to no avail.  Why is SQL Developer fine with the 2000 instances, but flatly refuses to connect to the 2005's?  I have zero problems connecting to the databases using TOAD or Management studio.

       

      1. I am having this same issue with SQL Developer versions 2.11, 3.1.0.7  and also 4.0.3.16

      2. TC/IP is enabled

      2. I am using the 1.2 jar file

      3. I have put the SSO folder in my PATH

      4. I have copied the ntlmauth.dll file to every bin directory I can find

      5. I've also copied the JtdsXA.dll to every bin directory I can find.

      6. I know exactly what my port numbers are, and they are NOT 1433.

      7. I have tried every combination of connection name, and port with and without database and instance name added. 

      8. I have a valid account and password set up using SQL Server authentication, and I also have a valid account using windows authentication that have both been verified by using TOAD and Management Studio.

      9. I am also receiving the following error trying to use windows authentication through SQL Developer and have followed all instructions I could find to fix it to no avail:  Status : Failure -I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

        • 1. Re: Status : Failure -I/O Error: DB server closed connection.
          thatJeffSmith-Oracle

          can you get in with db user authentication like 'sa' versus a domain authentication?

           

          have you checked the sql server logs - maybe something is there

           

          for version 4 you should probably be using the 1.3.1 jTDS jar

           

          what bit level is your JDK? If it's 64 bit, make sure you copied the 64 bit version of your ntlmauth.dll vs the 32 bit one - my WIN7 machine had BOTH

          • 2. Re: Status : Failure -I/O Error: DB server closed connection.
            Patrick J. Boyd

            Hi Jeff,

            I have verified that my SA account works using 2 other tools, but SQL Developer won't work with it.  I would prefer to use the SA account vs. domain authentication but I am willing to try anything to get my project moving forward.

             

            For some reason I overlooked the SQL Server Log (getting too frustrated to think straight).  I have received the following errors which I will begin Googling, but wanted to get them documented here in case you've seen it before.

            Error: 17835, Severity: 20, State: 1.

            Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: 144.112.17.79]

             

            I will try 1.3.1 jTDS, but everything I've been able to find online specifically says to use 1.2 to connect to SQL Server databases.

             

            I am running SQL Developer from my 64 bit Win 7 machine, and I'll verify that I used the correct ntlmauth.dll and re-copy as needed.  I can also run SQL Developer from the 64 bit Linux RedHat server my Oracle DB is hosted on, but I run into a lot more issues with permissions on the box and the different firewall settings.  (Segregation of Duties etc ...)

             

            Full disclosure, I am very new to SQL Server and really don't know the ins and outs of it.  I do have full administrative access to the instance I need to pull the data from, but I do not have administrative access to the Virtual Win 2003 server it is hosted on. 

             

            Thanks for any help you can provide

            Patrick Boyd

            • 4. Re: Status : Failure -I/O Error: DB server closed connection.
              Patrick J. Boyd

              I'm running the Java 7 that came with SQL Developer 4.0.  I never had 8 on my laptop, and may have been running 6 something previous to this issue.  I'm on a corporate load and have only a small amount of control over what is loaded. 

              • 5. Re: Status : Failure -I/O Error: DB server closed connection.
                Gary Graham-Oracle

                Regarding which jTDS version to use, read the following taken from

                jTDS - SQL Server and Sybase JDBC driver / News: jTDS JDBC Driver 1.2.7 and 1.3.0 released

                 

                Version 1.3.0 is the first Java 7 compatible version of the driver and, beside the fix for bug #672, improves performance compared to version 1.2.7. The future development will be focused on the 1.3.x line of the driver, so further enhancements and bug fixes will not necessarily become available it the Java 1.3 compatible jTDS 1.2.x. You should only stick to jTDS 1.2 if you require to use a Java version prior to Java 7.

                • 6. Re: Status : Failure -I/O Error: DB server closed connection.
                  Turloch O'Tierney-Oracle

                  Hi Patrick

                   

                  I have not tried changing the url parameters for ssl, I do have a few clues for you:

                   

                  jTDS JDBC Driver gives the url format. ssl=

                  Using Oracle SQL Developer with MS SQL Server : Total Business Intelligence gives an (old) example on how to type more than a number into the port field.

                  (Previously used for a different purpose (i.e. switching to different non default instance)).

                   

                  -Turloch

                  SQLDeveloper Team

                  • 7. Re: Status : Failure -I/O Error: DB server closed connection.
                    Patrick J. Boyd

                    I've been able to make some progress.

                     

                    1. I have upgraded to the 1.3 jar file, but that alone did not fix any of my issues.

                    2. I was unable to solve the encryption issue, and due to separation of duties and high workload I am unable to troubleshoot this to a satisfactory conclusion.  Instead I disabled the forced encryption on the SQL Server side and I am now able to log into the 2005 databases.

                    3.  The migration was still not getting anywhere, nor was it producing logs at all so it was difficult to trouble shoot.  Following the generic instruction on the migration website for creating the migration repo user and repo were not producing the required results.  The repo user was defaulted to the system tablespace (major no no) so I changed the default tablespace to our normal user tablespace.  Giving it only connect and resource didn't seem to give it enough access to do the job either.  Instead of going at it piece meal, I went full steam and gave the repo owner DBA and unlimited tablespace.  Finally the repository has been properly created and I have completed one dry run creating all the scripts without performing the online data move. 

                     

                    Since I'm crunched for time and really need the project to move forward, I'm not going to take the time to figure out exactly what grants would have gotten the job done correctly without jumping all the way to DBA.  Since I am the only one with access to the database and ID, and I will be dropping the user later, I'm not overly concerned.  It would be nice though if it could be properly looked at and have the online materials changed to reflect the necessary items.  At this point I've wasted days troubleshooting something that really shouldn't have been an issue at all.

                     

                    I'll post again when I start running the full migration with data and let you know how it goes. 

                    • 8. Re: Status : Failure -I/O Error: DB server closed connection.
                      thatJeffSmith-Oracle

                      Did you check the Help?

                       

                       

                      Before You Start Migrating: General Information

                       

                      You may need to perform certain tasks before you start migrating a third-party database to an Oracle database. See the following for more information:

                       

                       

                      See also any information specific to the source database that you will be migrating, as explained in Before You Start Migrating: Source-Specific Information.

                       

                      Note: SQL Developer does not migrate grant information from the source database. The Oracle DBA must adjust (as appropriate) user, login, and grant specifications after the migration.

                       

                      Note: Oracle recommends that you make a complete backup of the source database before starting the migration. For more information about backing up the source database, see the documentation for that type of database.

                       

                      If possible, begin the migration using a development or test environment, not a production database.

                       

                      Creating a Database User for the Migration Repository

                       

                      SQL Developer requires a migration repository to migrate a third-party database to an Oracle database. To use an Oracle database for the migration repository, you must have access to that database using a database user account. Oracle recommends that you use a specific user account for migrations, For example, you may want to create a user named MIGRATIONS, create a database connection to that user, and use that connection for the migration repository; and if you wish, you can later delete the MIGRATIONS user to remove all traces of the migration from the database.

                       

                      When you create a user for migrations, specify the tablespace information as in the following example, instead of using the defaults for tablespaces:

                      CREATE USER migrations IDENTIFIED BY <password>

                        DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp,

                       

                      Do not use a standard account (for example, SYSTEM) for migration.

                       

                      When SQL Developer creates a migration repository, it creates many schema objects that are intended only for its own use. For example, it creates tables, views, indexes, packages, and triggers, many with names starting with MD_ and MIGR. You should not directly modify these objects or any data stored in them.

                       

                      Requirements for Creating the Destination Oracle Objects

                       

                      The user associated with the Oracle database connection used to perform the migration (that is, to run the script containing the generated DDL statements) must have the following roles and privileges:

                       

                      Note: You must grant these privileges directly to a user account. Granting the privileges to a role, which is subsequently granted to a user account, does not suffice. You cannot migrate a database as the user SYS.

                       

                      Roles

                      CONNECT WITH ADMIN OPTION

                      RESOURCE WITH ADMIN OPTION

                       

                      Privileges

                      ALTER ANY ROLE

                      ALTER ANY SEQUENCE

                      ALTER ANY TABLE

                      ALTER TABLESPACE

                      ALTER ANY TRIGGER

                      COMMENT ANY TABLE

                      CREATE ANY SEQUENCE

                      CREATE ANY TABLE

                      CREATE ANY TRIGGER

                      CREATE VIEW WITH ADMIN OPTION

                      CREATE MATERIALIZED VIEW WITH ADMIN OPTION

                      CREATE PUBLIC SYNONYM WITH ADMIN OPTION

                      CREATE ROLE

                      CREATE USER

                      DROP ANY SEQUENCE

                      DROP ANY TABLE

                      DROP ANY TRIGGER

                      DROP USER

                      DROP ANY ROLE

                      GRANT ANY ROLE

                      INSERT ANY TABLE

                      SELECT ANY TABLE

                      UPDATE ANY TABLE

                       

                      For example, you can create a user called migrations with the minimum required privileges required to migrate a database by using the following commands:

                      CREATE USER migrations IDENTIFIED BY password

                        DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

                       

                      GRANT CONNECT, RESOURCE, CREATE VIEW, CREATE MATERIALIZED VIEW,

                         CREATE PUBLIC SYNONYM TO migrations WITH ADMIN OPTION;

                       

                      GRANT  ALTER ANY ROLE, ALTER ANY SEQUENCE, ALTER ANY TABLE, ALTER TABLESPACE,

                      ALTER ANY TRIGGER, COMMENT ANY TABLE, CREATE ANY SEQUENCE, CREATE ANY TABLE,

                      CREATE ANY TRIGGER, CREATE ROLE, CREATE TABLESPACE, CREATE USER, DROP ANY

                      SEQUENCE, DROP ANY TABLE, DROP ANY TRIGGER, DROP TABLESPACE, DROP USER, DROP ANY

                      ROLE, GRANT ANY ROLE, INSERT ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE TO

                      migrations;

                       

                      After you have created the converted model and done first DDL generation done for the new database, it will be clear from the scripts which privileges will be required for your situation.