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
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: 18.104.22.168]
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
Are you running java 7 or java 8?
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.
Regarding which jTDS version to use, read the following taken from
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.
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)).
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.
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.
CONNECT WITH ADMIN OPTION
RESOURCE WITH ADMIN OPTION
ALTER ANY ROLE
ALTER ANY SEQUENCE
ALTER ANY TABLE
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
DROP ANY SEQUENCE
DROP ANY TABLE
DROP ANY TRIGGER
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
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.