Connecting to MySQL Server from a Microsoft SQL Server

This seams to me that connecting MySQL and Microsoft SQL databases is something that should be simple to do. Unfortunately looking around online there was no real clear directions or even indications that this was possible without buying some additional drivers.  While fear not because this does work out of the box without needing to buy any additional software on pretty much any versions of the two databases.

In short what we will do is add a MySQL ODBC driver, configure a System DSN using that driver, Link MsSQL to that DSN connection, and to do a few test queries and updates.  This will allow full insert, update, and delete commands.



You will need administrative access to the Microsoft SQL Server along with access to the database. In addition you will need to know the connection information for the MySQL database, it's IP Address, a list of the databases you want to connect, and any MySQL login's and passwords you will be using.

Setup

First off you will need to identify if you have the 32-bit or 64-bit version of Microsoft SQL Server.  You can determine this by connecting to the server in Management Studio, Right Clicking on the connected server, selecting Properties, and checking the Product field.

Once you have determined the proper edition go here and download the matching format of the MySQL ODBC driver. You do not need to create and account as there is a link to skip that step. Be sure to install this package.

ODBC Configuration

We need to create one or more ODBC connections using this new driver called DSN's. Navigate to the Administrative Tools folder in your start menu or control panel and launch the matching 32-bit or 64-bit version of ODBC Data Source.

As for how to set up the connections you have a few options.  You can create individual DSN's for each database using separate user logins for each, you can create one DSN with permission to all the tables, or you can create one DSN and map MsSQL users to MySQL users and control what database's each can see based on that mapping. Decide on this and then adjust the Server Name and Database Name fields below as you configure things.

If you are using one login for everything or mapping users across then do the following.

• Open the ODBC Data Source Administration tool.
• Go to the System DSN tab.
• Click Add to create a new ODBC connection.
• Select MySQL ODBC 5.3 ANSI Driver or  MySQL ODBC 5.3 Unicode Driver as your needs dictate. Unicode supports a larger character set.
• Click Finish.
• On the new screen fill in the following fields:
• Data Source Name: ServerName
• TCP/IP: MySQL Server IP Address
• User/Password: MySQL Admin username/password.
• Database: Leave this blank.
• Click Test to make sure it is connection and then press Ok to save.

Or if you are creating separate ODBC connections for each database or only have one database but use one login per database then do this instead for each.

• Open the ODBC Data Source Administration tool.
• Go to the System DSN tab.
• Click Add to create a new ODBC connection.
• Select MySQL ODBC 5.3 ANSI Driver or  MySQL ODBC 5.3 Unicode Driver as your needs dictate. Unicode supports a larger character set.
• Click Finish.
• On the new screen fill in the following fields:
• Data Source Name: ServerName/DatabaseName
• TCP/IP: MySQL Server IP Address
• User/Password: MySQL login/password with access to the specific database.
• Database: Select the proper database.
• Click Test to make sure it is connection and then press Ok to save.

Microsoft SQL Server Configuration

Now for the final step open up the Microsoft SQL Server Management Studio and connect to the database server.  Agains he steps will vary depending if you have one single connection are are making connections for each database in question. If you are using one login for everything to grand full access to any MsSQL users to all MySQL data do the following.

• Navigate to Server Objects -> Linked Servers then Right Click -> New Linked Server.
• Fill in the following on the General Tab:
• Linked Server: ServerName
• Provider: Microsoft OLE DB Provider for ODBC Drivers
• Data Source: ServerName
• On the Security Tab:
• Select Be made using the security context.
• Reneter the same MySQL Admin username/password used above.
• Click Ok. 
• If there are any issues select No and make adjustments.  Once you click Yes you can't edit the General settings and have to remove and re-add the connection.

On the other hand if you want to map MsSQL Users to MySQL users so they have access to different tables based on the login then so the following.

• Navigate to Server Objects -> Linked Servers then Right Click -> New Linked Server.
• Fill in the following on the General Tab:
• Linked Server: ServerName
• Provider: Microsoft OLE DB Provider for ODBC Drivers
• Data Source: ServerName
• On the Security Tab:
• Add a row for each user that should be mapped and select the MySQL Local Login Name.
• Enter the matching MySQL usernames and passwords or shared account details that each login should map to.
• Leave Impersonate unchecked.
• You can change the radio to Not be made so that unmapped users don't see anything or specify a general login for unmapped users.
• Click Ok. 
• If there are any issues select No and make adjustments.  Once you click Yes you can't edit the General settings and have to remove and re-add the connection.

Or if you create per database DSN's then to the this for each.

• Navigate to Server Objects -> Linked Servers then Right Click -> New Linked Server.
• Fill in the following on the General Tab:
• Linked Server: ServerName/DatabaseName
• Provider: Microsoft OLE DB Provider for ODBC Drivers
• Data Source: ServerName/DatabaseName
• On the Security Tab:
• Select Be made using the security context.
• Reneter the table specific MySQL username/password for this table.
• Click Ok. 
• If there are any issues select No and make adjustments.  Once you click Yes you can't edit the General settings and have to remove and re-add the connection.

Querying and Updating Data

Now that this is connected you can browse the structure in Management Studio by going back to Server Objects -> Linked Servers -> Server Name.  To test some sample operations lets assume we have the following table in MySQL.

• test01
• id, int, pk, auto_increment
• Name, nvarchar(20) not null
• Items, smallint, not null

Then we can do all of the following:

SELECT count(*) as 'Count'
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01')
GO

INSERT OPENQUERY([ServerName/DatabaseName], 'SELECT Name, Items FROM test01 ')
VALUES
('Shirts', 47),
('Pants', 55),
('Shorts', 2),
('Shoes', 43)
GO

SELECT *
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01')
GO

UPDATE OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01 WHERE Name = ''Shoes'' ')
SET Items = 55
GO

SELECT *
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01  WHERE Name = ''Shoes''')
GO

DELETE OPENQUERY ([ServerName/DatabaseName], 'SELECT id FROM test01 WHERE Items > 50')
GO

SELECT *
FROM OPENQUERY([ServerName/DatabaseName], 'SELECT * FROM test01')
GO