Mysql – How to connect to a network MySQL server using ODBC

I'd say you miss a post for your instance in /etc/freetds.conf. The post should look something like this

[my_alias]
    host = some_db_host_address
    port = 1433
    tds version = 8.0

Then in your connection string you should use that alias. The host and port part shouldn't be needed as they are picked up from the configuration file. You may have to say which driver to use. This works for me

$conn = odbc_connect("Driver=FreeTDS;DSN=my_alias","user","pass");

It is true that linked servers are dependent on the OLEDB interface but the ODBC driver can be safely used between SQL Server servers. (There's a misconception in the wild that such a configuration is unsupported by Microsoft; that is a myth.)

To use ODBC Driver 11, 13, or 13.1 using Linked Servers.

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER={ODBC Driver 13 for SQL Server};MultiSubnetFailover=Yes;ApplicationIntent=READONLY;Trusted_Connection=Yes;SERVER=FqnServerName;'

The ODBC 13.1 driver is an update and still uses the "ODBC Driver 13 for SQL Server" driver name, not "ODBC Driver 13.1 for SQL Server".

I have found that using the fully-qualified server name seems, in my testing, to be more reliable, especially when connecting to AGs, but you can always try just the server name.

You can test inter-server Kerberos connectivity but using an OPENROWSET. One way I test Kerberos is using a simple query run from the source server:

SELECT * FROM OPENROWSET(N'MSDASQL', N'Driver={ODBC Driver 13 for SQL Server};Server=FqnServerName;Database=master;Trusted_Connection=yes;MultiSubNetFailover=yes;', N'SELECT * FROM [sys].[databases]') AS [Source]

If you see this error, you need to configure Kerberos between the servers:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

The 2012 Native Client is now (over) five years old, has been officially deprecated by Microsoft, and, if you want to align to Microsoft's roadmap, you will want to use ODBC. Consider that in 2014, the 2012 Native Client was only two years old. Now consider all the features added to SQL Server 2016 and SQL Server 2017 that are not supported by the 2012 Native Client, and you have a tremendous use case for using the ODBC Driver 13.1 for SQL Server.

Since middle of last year, my organization has been migrating to SQL Server 2016 and we are testing SQL Server 2017 (CTP 2.1). I have been migrating all of our linked servers (at least those pointing to SQL Server 2016 or SQL Server 2017) to use ODBC Driver 13 (or, more accurately, 13.1) and have yet to see any serious issues in over a year of testing against SQL Server 2016.

(In the interest of full disclosure, there is an issue with Graph Tables using linked servers; I reported the issue to Microsoft based on a linked server using ODBC 13.1, and Microsoft confirmed Graph Tables are not supported over linked servers (of any kind) in SQL Server 2017.)

Let me know if you run into any issues and I'll help you along.