Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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
share|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 at 6:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .