| Bug #20674 | 'No Suitable Driver' | ||
|---|---|---|---|
| Submitted: | 24 Jun 2006 0:11 | Modified: | 28 Jun 2006 1:20 |
| Reporter: | Paul Breimyer | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Migration Toolkit | Severity: | S3 (Non-critical) |
| Version: | 1.0.25 | OS: | Windows (XP Professional) |
| Assigned to: | CPU Architecture: | Any | |
[24 Jun 2006 0:11]
Paul Breimyer
[25 Jun 2006 14:44]
Valeriy Kravchuk
Thank you for a problem report. Please, check, is it possible to connect to your MS SQL Server 2005 with any other Java-based tool, using the same JDBC driver. You may use DbVisualizer (http://www.minq.se/products/dbvis/), for example.
[25 Jun 2006 17:54]
Paul Breimyer
thanks for getting back to me... i used DbVisualizer and was able to connect to the MSSQL db, but only after i located the jdbc jar file driver by browsing - it was not automatically able to find it. i'm guessing this may be the reason why the migration toolkit can't find the driver. the driver jar file is already on the classpath - how can i get the migration tool to find it? thanks, Paul
[26 Jun 2006 10:09]
Valeriy Kravchuk
The fact that JDBC driver for MS SQL Server was not found automatically (within classpath), and manual search for it was not requested, looks like a bug for me. Please, copy your JDBC driver .jar to java\lib subdirectory of MT installation directory (usually it is in Program Files\) and try again. If it will not help, add appropriate entry (like one for mysql-connector-*) into java\.classpath file and check again. Inform about the results.
[26 Jun 2006 13:36]
Paul Breimyer
it still doesn't find the driver... i did the following: - added the jar file to C:\Program Files\MySQL\MySQL Migration Toolkit 1.0\java\lib - added the following lines to the getDriverMssqlJdbc method in C:\Program Files\MySQL\MySQL Migration Toolkit 1.0\lua\RdbmsInfoMssql.lua: driver.filesTarget= "./java/lib/" grtV.insert(driver.files, "jtds-1.2.jar") - added <classpathentry exported="true" kind="lib" path="lib/jtds-1.2.jar"/> to C:\Program Files\MySQL\MySQL Migration Toolkit 1.0\java\.classpath (although this just looks like an eclipse classpath file, but i figured it couldn't hurt).
[27 Jun 2006 0:24]
MySQL Verification Team
Thank you for the feedback. For to able to connect with MySQL Migration Toolkit you need to have with SQL Server the mixed authentication, provide the username and its password and also to enable the TCP/IP protocol and provide the port which is listening. The listening port you can get with: use master go Xp_readerrorlog Thanks in advance.
[27 Jun 2006 0:56]
Paul Breimyer
yes, i did all of that when i was connecting with DbVisualizer. the problem i'm having is that the migration tool can't find a driver to connect to the db, it's not the db connection itself. thanks for help - please let me know what you think... Paul
[27 Jun 2006 1:17]
MySQL Verification Team
Well I installed the Migration Toolkit with the option complete and the JDK:jdk-1_5_0_06-windows-i586-p.exe (you only need the Java Runtime for that) and I was able to connect see below (I edited my username and its password) and compare the connection string. Are you sure you did that: I mean: mixed authentication, enable TCP/IP, the username, password and port listening? Connecting to source database and retrieve schemata names. Initializing JDBC driver ... Driver class MS SQL JDBC Driver Opening connection ... Connection jdbc:jtds:sqlserver://LIGHT:1509/test;user=XX;password=XXXXXX Fetching schemata list. SELECT TABLE_SCHEMA AS SCHEMANAME, max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA Return schemata list. Schemata names retrieved successfully. Initializing JDBC driver ... Driver class MySQL JDBC Driver 3.1
[27 Jun 2006 1:54]
Paul Breimyer
yeah, i'm sure the mssql settings are set - the dbvisualizer utilizes a jdbc driver and required mixed mode authentication, port listening, etc. i'm using jdk1.5.0_06. in your setup, how is your jdbc driver configured? is it a jar file, and if so, what's it called, where's it located, and how do you tell the migration tool where to look for it (using a classpath variable, adding a line to a .lua file, etc)? thanks again.
[27 Jun 2006 14:33]
MySQL Verification Team
Hi, I didn't configured any special settings, I don't have even the CLASSPATH on my environment. I just installed the Toolkit with complete option and installed the JDK stuff. Then using sqlcmd I get the port which SQL Server is listening: 2006-06-27 08:08:04.120 Server Server is listening on [ 'any' <ipv4> 1509]. Started the mysqld-nt.exe for to be ready. Started the Migration toolkit. In Source Database screen: Database System: MS SQL Server Stored Connection: blank Hostname: LIGHT (It is my computer name, notice I don't use the SQL Server instance SQLEXPRESS): Port: 1509 Username and password: the one I configured for MS SQL Server. Database: I click the right button and select one database. Are you able for to perform the above steps? My guess is you aren't able for to connect and the error message is misleading about the not suitable driver. Thanks in advance.
[27 Jun 2006 17:28]
Paul Breimyer
i ran sqlcmd and input the parameters into the migration toolkit, but when i click the '...' button to get the database names, i get the following dropdown list: Fetching of list failed. ReverseEngineeringMssql.getCatalogs :No suitable driver shouldn't the tool allow me to browse for the driver, similar to the lookup available for an oracle db?
[27 Jun 2006 18:19]
MySQL Verification Team
Hmm, your last comment when clicking the '...' button you should see a drop list with the database names and what you are reporting it was a bug already fixed with version 1.0.25. Then I afraid to ask you if actually you are using 1.0.25 and its installation was an upgrade install over an older one (maybe wrong/lack of some files)? Anyway, try to remove the current Migration Toolkit using control panel and re-install the version 1.0.25.
[27 Jun 2006 19:04]
Paul Breimyer
good thought, but unfortunately it wasn't an upgrade. i uninstalled MT from the control panel, downloaded a new copy from http://dev.mysql.com/downloads/migration-toolkit/1.0.html, and installed mysql-migration-toolkit-1.0.25-win32.msi, and the same problem occurs when i click the '...' button.
[28 Jun 2006 0:30]
MySQL Verification Team
What happens in the source database screen instead of to fill the text boxes you press advanced and fill the Connection string with: jdbc:jtds:sqlserver://LIGHT:1509/test;user=XX;password=XXXXXX where LIGHT == your computer name 1509 the port which SQL Server is listening test == the database name you want to migrate and then click the '...' button ?
[28 Jun 2006 1:20]
Paul Breimyer
finally... thanks for your help. i had tried to use my own connection string, but i didn't use 'jtds' at the time. when i added it to the string i got a new error after hitting the '...' button: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property. so i copied ntlmauth.dll into the system32 directory, and now it works. thanks again for your help. Paul PS - i still wish the Migration Tool would allow you to locate an appropriate driver if it has errors.
[27 Feb 2007 14:19]
andi b
hey folks, i spent several hours to find the solution: if you install the sql server 2005 express edition, anytime during the installation appears a checkbox that says 'hide advanched configuration options'; this is checked by default. turn this checkbox off and continue till the installer page where you are asked for the sql-instance name. at this point there is 'SQLEXPRESS' suggested and preselected. you should use the first option instad (anything with default). later you are asked for the possible connection options. again you have to change to option to allow winnt *and* network access. if you select the second option, you will be asked to enter the admins (user name is 'sa') password; do so twice. after the installation you have to run Start > Programms > MS SQL Server 2005 > Configuration Tools > Configuration Manager activate TCP/IP in the Protocols section for the new SQL server and restart the server. now you have finished the server installation itself, all should be fine and you could already connect to it with the migration toolkit. probably you want to import an mssql database dump before, this should be something like that (in windows command shell): osql -E -Q "RESTORE DATABASE MyMsDatabase FROM DISK = 'D:\SQL_Data\ms_db_dump.bak' WITH REPLACE, MOVE 'MyMsDatabase_Data' TO 'D:\SQL_Data\MyMsDatabase.MDF', MOVE 'MyMsDatabase_Log' TO 'D:\SQL_Data\MyMsDatabase.LDF'" now start the migration toolkit, enter your hostname, user: sa, password: <as you entered during install> should be fine ;) best, andi
[4 Nov 2007 19:18]
Eval User
As the post origitator 'Octavian IDITA' ultimately concluded, the issue is not a bug. I too experienced the same error with reverse engineering of MS-SQL but was able to connect the MS-SQL server via the 'Connecting to Servers' screen. The only way that I could establish a successful connection was using the MS-SQL configured listen port setting (127.0.0.1 [localhost] on port 1434) as was created during the MS-SQL Server installation. The problem however is that the network and client configuration TCP/IP protocol must be enabled to allow the MySQL Migration Toolkit actual access to the database. I'm not sure exactly why the initial test connection to the listen port works but based upon actual experience, the MySQL Migration Toolkit won't perform the reverse engineering unless the TCP/IP protocol is enabled *and* mixed mode authentication is enabled. --- Content posted to: http://forums.mysql.com/read.php?104,181246,181246#msg-181246 Follows are some tips that finally resoved an issue for which I could successfully connect to the MS-SQL server via MySQL Migration Toolkit during the connection test but for which the subsequent reverse engineering phase failed. Initially, the only way I could connect to the MS-SQL server was using 'localhost' [127.0.0.1] as the hostname and port 1434 which was configured by MS-SQL Server during installation as the 'listen' hostname and port. If I set the client computer name and port 1433, the connection test failed. The resolution is to configure MS-SQL Server to enable the network and client configuration TCP/IP protocols via the 'SQL Server Configuration Manager' with the 'SQL Server 2005 Network Configuration' and 'SQL Native Client Configuration' child objects. The TCP/IP protocol must be enabled for both and each respective property configured (right-click TCP/IP for properties). Note the 'Protocol' and 'IP' tabs and configurations. After enabling the network and client TCP/IP protocols a test connection could be established using the computer name as the hostname and port 1433 *and* the reverse engineering phase successfully completed. I'm not sure if both network and client TCP/IP protocols must be enabled but as configured all worked for me. SQL Server Configuration Manager configuration settings screen capture image http://69.65.105.60/temp/mssql_config.gif Additional Notes: MS-SQL Server Mixed Mode (Windows Authentication or SQL Server Authentication) must be enabled to allow MySQL Migration Toolkit to establish a connection with a connection string. SQL Server Management Studio > Object Explorer > [Server instance] > right-click > properties > Security > Server Authentication > Windows Authentication or SQL Server Authentication Microsoft Access: note chapter 13 for the MySQL Migration Toolkit compiled HTML help file. Preparing a Microsoft Access Database for Migration 1. Tools > Options > View [tab] > Show [section] Check checkboxes: 'System objects' and 'Hidden objects' 2. Tools > Security > User and Group Permissions Control-click Select MSysObjects, MSysQueries, and MSysRelationships (highlight all) or perform individually and sequentially Check the 'Administer' checkbox to configure each selected item Click 'Apply' button
[25 Jun 2011 1:06]
Andy Wang
I just spent a whole day to figure out how to connect to MS SQL Server Express 2005 via the MySQL Migration Toolkit. The parameter that blocked me was the Hostname. I kept on entering the instance name in the field, i.e. CHRIS-T6400\SQLEXPRESS. Big mistake! It was only looking for the server name, CHRIS-T6400. I had port number 1433, plus the user name and password as well as the database name. It finally took off after I removed the instance name, SQLEXPRESS. You should get the following messages as indicated by others. Connecting to source database and retrieve schemata names. Initializing JDBC driver ... Driver class MS SQL JDBC Driver Opening connection ... Connection jdbc:jtds:sqlserver://CHRIS-T6400:1433/OMS;user=XX;password=XXXXXXXX;charset=utf-8;domain= Fetching schemata list. SELECT TABLE_SCHEMA AS SCHEMANAME, max(TABLE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.TABLES GROUP BY TABLE_SCHEMA UNION SELECT ROUTINE_SCHEMA AS SCHEMANAME, max(ROUTINE_CATALOG) AS CATALOGNAME FROM INFORMATION_SCHEMA.ROUTINES GROUP BY ROUTINE_SCHEMA Return schemata list. Schemata names retrieved successfully. Initializing JDBC driver ... Driver class MySQL JDBC Driver 5.0 Opening connection ... Connection jdbc:mysql://localhost:3306/?user=YYYY&password=YYYYYYYY&useServerPrepStmts=false&characterEncoding=UTF-8 Getting version information ... Initializing JDBC driver ... Driver class MySQL JDBC Driver 5.0
[25 Jun 2011 1:27]
Andy Wang
By the way, I am running Windows 7, sqlexpress (SQL Server 9.0.5000), MySQL Migration Toolkit 1.1.17, mySQL Server 5.1, as well as the TOAD for mySQL 5.0.0.345 plus TOAD foe SQL Server 5.5.0.1073. I also enabled TCP/IP, named pipes in remote connections. Last but not least, enable SQL Server Browser. Good luck.
