MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL Server ODBC DSN’ tag

SQL Server ODBC DSN

with 4 comments

You must install and then configure a Windows Data Source Name (DSN) for SQL Server’s ODBC before you can connect MySQL Workbench to a SQL Server and migrate data. If you fail to set it up, you can’t complete the first step of the MySQL Workbench migration wizard, as shown in the image to the right.

For MySQL readers, this was posted as part of a replicateable test case for Alfredo’s MySQL Workbench team. A Windows OS version of Bug 66516.

You configure a Windows Data Source Name (DSN) for Microsoft SQL Server 2012 after a successful installation (shown in this related blog post), by performing the following steps.

  1. You need to create a data source in the operating system. You should open the Control Panel and click on the Administrative Tools menu item to begin the installation of a new data source.

  1. Click the Data Sources (ODBC) menu item to start the process.

  1. This is where you add a new User Data Source. Click the Add button on the right of the dialog box.

  1. The Create New Data Source dialog box should show the SQL Server Native Client 11.0 that was installed when you installed the database. Click on it in the selection box, and then click the Finish button.

  1. The first dialog of the Create a New Data Source to SQL Server process presents the following dialog, which prompts you for a Data Source Name (DSN), a description, a target server. I’ve entered SQL Server ODBC as the DSN and description, and chosen the local database server. The local database server is the server running on the localhost machine. Click the Next button to continue the process.

  1. The second dialog of the Create a New Data Source to SQL Server process prompts with the following dialog, which lets you opt for integrated Windows authentication or user credential authentication. The easiest set up when you’re running a test case on a single instance selects integrated windows authentication. Click the Next button to continue the process.

  1. The third dialog of the Create a New Data Source to SQL Server process prompts with the following dialog, which lets you change the default database, attach a database filename, set ANSI rules, the application intent, and multi-subnet failover. The default options are selected in the dialog. Click the Next button to continue the process.

  1. The fourth dialog of the Create a New Data Source to SQL Server process prompts with the following dialog, which lets you change the language, encryption, character translation, regional settings, and long query execution. The default options are selected in the dialog. Click the Next button to continue the process.

  1. The next dialog displays the settings for the ODBC SQL Server Setup. You should always confirm that things work by clicking the Test Data Source button.

  1. The next dialog displays success when you made working choices and failure when something is misconfigured. Click the OK button to continue the process.

  1. The OK button concludes the Create a New Data Source to SQL Server process, and returns you to the list of valid ODBC Data Sources. Click the OK button to conclude the process.

Hopefully, this helps those learning how to set up Microsoft SQL Server to work with MySQL Workbench’s migration tool.

Written by maclochlainn

September 12th, 2012 at 1:24 am