26

I've grown to love SQL server management studio (ssms), I realise that it is not naturually compatible, however is there a way of connecting to MySQL? Perhaps using an ODBC connection?

I realise there are plenty of MySql admin tools, but it would be great to use SSMS.

Thanks for the help

23

Does this help? :

Creating a Linked Server in SSMS for a MySQL database

  1. Download the MySQL ODBC driver from mysql.com
  2. Install MySQL ODBC driver on Server where SQL Server resides -Double Click Windows Installer file and follow directions.

  3. Create a DSN using the MySQL ODBC driver Start-> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)

    • Click on the System DSN tab
    • Click Add
    • Select the MySQL ODBC Driver
    • Click Finish On the Login Tab:
    • Type a descriptive name for your DSN.
    • Type the server name or IP Address into the Server text box.
    • Type the username needed to connect to the MySQL database into the user text box.
    • Type the password needed to connect to the MySQL database into the password text box.
    • Select the database you'd like to start in. On the Advance Tab: Under Flags 1:
    • Check Don't Optimize column width.
    • Check Return Matching Rows
    • Check Allow Big Results
    • Check Use Compressed protocol
    • Check BIGINT columns to INT
    • Check Safe Under Flags 2:
    • Check Don't Prompt Upon Connect
    • Check Ignore # in Table Name Under Flags 3:
    • Check Return Table Names for SQLDescribeCol
    • Check Disable Transactions Now Test your DSN by Clicking the Test button
  4. Create a Linked Server in SSMS for the MySQL database SSMS (SQL Server Management Studio -> Expand Server Objects

    • Right Click Linked Servers -> Select New Linked Server On the General Page:
    • Linked Server: Type the Name for your Linked Server
    • Server Type: Select Other Data Source
    • Provider: Select Microsoft OLE DB Provider for ODBC Drivers
    • Product name: Type MySQLDatabase
    • Data Source: Type the name of the DSN you created On The Security Page
    • Map a login to the Remote User and provide the Remote Users Password
    • Click Add under Local server login to remote server login mappings:
    • Select a Local Login From the drop down box
    • Type the name of the Remote User
    • Type the password for the Remote User
  5. Change the Properties of the Provider MSDASQL Expand Providers -> Right Click MSDASQL -> Select Properties

    • Enable Nested queries
    • Enable Level zero only (this one's the kicker)
    • Enable Allow inprocess
    • Enable Supports 'Like' operator
  6. Change settings in SQL Server Surface Area Configuration for Features

    • Enable OPENROWSET and OPENDATASOURCE support.
  7. Change settings in SQL Server Surface Area Configuration for Services and Connections

    • Enable Local and Remote connections via TCP/IP and named pipes
  8. Stop SQL Server and SQL Server Agent

  9. Start SQL Server and SQL Server Agent
| improve this answer | |
  • This answer seems good however I think it could do with a refresh for later versions of SQL Server – Chris Nevill Mar 16 '15 at 12:42
  • 1
    Beware when joining linked server objects with local DB objects. The performance may suffer. – Faiz Apr 4 '16 at 6:35
  • MSDASQL is a deprecated technology: docs.microsoft.com/en-us/previous-versions/… – Francesco Mantovani Apr 27 at 20:19
  • Sorry I'm in a different world now, I totally forgot about this question. I'll mark this as the answer from yesteryear! Thanks! ! But +1 the new answer as well as it appears like it may have some new details in. – Alex KeySmith Apr 28 at 8:23
1

Yes, you can.

  1. Download MySQL ODBC driver: https://dev.mysql.com/downloads/connector/odbc/ and install
  2. Go to ODBC Data Source Administrator > User DNS > Add..., and fill all the fields wit the right informations. Then test if the connections works enter image description here
  3. Go to ODBC Data Source Administrator > System DNS > Add..., and fill all the fields wit the right informations. Then test if the connections works enter image description here
  4. In SSMS go to Server Objects > Linked Servers > New Linked Server... enter image description here
  5. Under General set
    • Linked server: (Put what you want, I put the name of the server for simplicity)
    • Provider: Microsoft OLE DB Provider for ODBC Drivers
    • Product name: (Put what you want, I put the name of the server for simplicity, you might want to put MySQL)
    • Data source: Must have the same name as the System Data Source's Name enter image description here
  6. Under Security select "Be made using this security context" and enter you user and password for remote login to MySQL enter image description here
  7. Under Server Options set RPC and RPC Out as True enter image description here
  8. Go to Server Objects > Linked Servers > Providers > MSDASQL, and enable: Nested queries, Level zero only, Allow inprocess, Supports 'Like' operator enter image description here
  9. Restart SQL Server and SQL Server Agent
  10. You can now query MySQL from SSMS using this structure:

    select * from OPENQUERY(linked_server_name, 'SELECT * FROM database_name.table_name')

or simply:

SELECT * FROM linked_server_name...table_name

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.