MySQL Workbench Migration from MSSQL: Windows Authentication with AzureAD-CodePudding

TL;DR: Having trouble identifying the correct server, username, and password combination to connect MySQL to an existing MSSQL database using Windows Authentication. Will I need to create a separate user for the migration, or is it possible to connect to MySQL in the Migration Wizard using MSSQL Windows Authentication?

Background and Debugging Steps

I received a BAK file which I would like to import into my existing MySQL installation. I have successfully imported the BAK file into Microsoft SQL Server Management Studio 18 (MSSQL). I am now trying to migrate the database to MySQL using the Database Migration Wizard in MySQL Workbench 8.0.27.

I believe the issue I am running into relates to authentication. In MSSQL I am connecting to the server using Windows Authentication. My User name is "AzureAD\ZachLonghofer" and MSSQL takes care of the password. In the MySQL migration wizard, using the default connection method, I receive this message log:

Starting...
Connect to source DBMS...
- Connecting to source...
Connecting to Mssql@MS Access Database...
Opening ODBC connection to DSN=MS Access Database;DATABASE=;UID=AzureAD\ZachLonghofer;PWD=XXXX...
ERROR: Connection failed: No open connection to Mssql@MS Access Database
Connecting to Mssql@MS Access Database...
Opening ODBC connection to DSN=MS Access Database;DATABASE=;UID=AzureAD\ZachLonghofer;PWD=XXXX...
ERROR: Connection failed: No open connection to Mssql@MS Access Database

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\wizard_progress_page_widget.py", line 71, in run
    self.func()
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 455, in task_connect
    raise e
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 439, in task_connect
    if not self.main.plan.migrationSource.connect():
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration.py", line 199, in connect
    self._rev_eng_module.connect(self.connection, self.password or "")
SystemError: NotConnectedError("No open connection to Mssql@MS Access Database"): error calling Python module function DbMssqlRE.connect
ERROR: Error during Connect to source DBMS: NotConnectedError("No open connection to Mssql@MS Access Database"): error calling Python module function DbMssqlRE.connect
ERROR: Exception in task 'Connect to source DBMS': SystemError('NotConnectedError("No open connection to Mssql@MS Access Database"): error calling Python module function DbMssqlRE.connect')

Failed

I also tried using ODBC (native) and specifying the Server: ZLONGHOFER\SQLEXPRESS01 -- which is what shows as the server in MSSQL. With this configuration, I receive this message log, which is identical until after the traceback:

Starting...
Connect to source DBMS...
- Connecting to source...
Connecting to Mssql@DRIVER=SQL Server;SERVER=ZLONGHOFER\SQLEXPRESS01...
Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;...
Connecting to Mssql@DRIVER=SQL Server;SERVER=ZLONGHOFER\SQLEXPRESS01...
Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;...
Connecting to Mssql@DRIVER=SQL Server;SERVER=ZLONGHOFER\SQLEXPRESS01...
Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;...

Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\workbench\wizard_progress_page_widget.py", line 71, in run
    self.func()
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 455, in task_connect
    raise e
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration_source_selection.py", line 439, in task_connect
    if not self.main.plan.migrationSource.connect():
  File "C:\Program Files\MySQL\MySQL Workbench 8.0\modules\migration.py", line 199, in connect
    self._rev_eng_module.connect(self.connection, self.password or "")
grt.DBLoginError: [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456)
ERROR: Error during Connect to source DBMS: [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456) (SQLDriverConnect);
 [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\ZachLonghofer'. (18456)
ERROR: Exception in task 'Connect to source DBMS': DBLoginError("[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\\ZachLonghofer'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'AzureAD\\ZachLonghofer'. (18456)")

Failed

Windows Authentication

The password I am using is my windows user password. I'm fairly certain I've never set a password for MSSQL: I've only ever used Windows Authentication and I thought the username and password would parallel my Windows Login and Password, but it seems not.

It also occurs to me that I may need to create a user in MSSQL if it may not be possible to import to MySQL using Windows Authentication connection to MSSQL. If so, I will need to [figure out how to] create a separate user in MSSQL to do the migration.

Will I need to create a separate user for the migration, or is it possible to connect to MySQL in the Migration Wizard using MSSQL Windows Authentication?

CodePudding user response：

TL;DR;

When you log in on a Windows machine you use Windows Authentication. The user logged in is the same one used by the SSMS, this one doesn't take care of your password simply because it doesn't need you are already authenticated to Windows, and this "mechanism" is the same for every program you use.

Your question omitted the most important "piece": the connection string or at least the code/library you use to connect to MsSQL Server.

Microsoft SQL Server (on premises) can run in two mode:

Integrated authentication only
Mixed mode -> Integrated authentication AND Sql Server authentication

On mode 1 you can only login to SQL Server using Windows Authentication only whenever on mode 1 you can use Windows Auth and users created directly on SQL Server.

From the log I see

Opening ODBC connection to DRIVER={SQL Server};SERVER=ZLONGHOFER\SQLEXPRESS01;DATABASE={};UID=AzureAD\ZachLonghofer;PWD=XXXX;

UID and PWD are symptoms using a Sql Server Authentication NOT Windows Authentication.

TODO

If you want to use Windows Authentication you must remove user and password from your connection string and add Integrated Security=true; this specify to use the Win Auth see here for connection strings
If you want to use a SQL Server User, you must create a user and assign at least the reader of the DB and then use the username and password, here a tutorial on how to