Creating a Linked Server instance from MS SQL Server to a Google Cloud MySQL server

Question

I have been attempting to connect the databases of a MSSQL server to a Google Cloud MySQL server so I can do joins between them. I've set up the correct ODBC on my machine and tested the connection, which works fine. But when I go to set up the linked server in the MS Server Management Studio, I get error 7303

The linked server has been created but failed a connection test. Do you want to keep the linked server?

Additional information: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL". OLE DB provider "MSDASQL" for linked server "MYSQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303

What might I be missing here? I ensured the ODBC driver I have is 64 bits (same as the MS Sever Management I have) but configured the System DSN for 32 bits as well. Is there something I must configure on the Google Cloud Server to make this link work? Since the DSN connection test passes, I assume it must be some failure within the Server Management Studio itself. Thank you!

EDIT: Here is the script used to create the linked server:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SERVERNAME', @srvproduct=N'MSDASQL', @provider=N'MSDASQL', @datasrc=N'server-name'

GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SERVERNAME', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SERVERNAME', @locallogin = NULL , @useself = N'False'
GO

SSMS is the client tool, not the server. As for the error, it says there's no ODBC source with that name on the server. Is SQL Server installed on your machine? Did you create a User data source or Machine data source? — Panagiotis Kanavos
I do have SQL server installed on my machine (using SELECT @@ Version it says I have SQL Server 2012, 64-bit). I created a system DSN, whose connection works fine outside the Server Management Studio environment. — GappedState
Here is the link from Google, Connection Options for External Applications., cloud.google.com/sql/docs/mysql/external-connection-methods — DrHouseofSQL
I repeat, SSMS is the client tool. The error comes from the server. SSMS was able to execute the commands that create the linked server. When it tried to check the connection though, it got an error. — Panagiotis Kanavos
Please post the commands used to create the linked server. Almost all dialog boxes in SSMS have a script button at the top — Panagiotis Kanavos

Mike M Mike M · Accepted Answer · 2019-09-25T17:20:41

To be honest, in fact this is a client error.
You don't have the MSDASQL driver installed on your client machine, or possibly you only have a version that your client tool can't connect to -- such as 32-bit while you're running a 64-bit client.

It is normal that you can create the Linked Server without an error.
The driver is not checked by sp_addlinkedserver; it just creates the relevant table entries.

To easily see what drivers you do have, try the visual ODBC Data Sources tool.
You can start the process of creating a DNS and get see all the driver names. It may have only the long names, but those also work.

Creating a Linked Server instance from MS SQL Server to a Google Cloud MySQL server

1 Answers