Связанный сервер mysql

 

Пример скрипта для Mysql.

EXEC master.dbo.sp_addlinkedserver @server = N'OTRS', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'OTRS', @provstr=N'ODBC;DSN=OTRS;', @catalog=N'otrs'
 /* For security reasons the linked server remote logins password is changed with ###### */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'OTRS',@useself=N'False',@locallogin=NULL,@rmtuser=N'root',@rmtpassword='########'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'collation compatible', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'rpc', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'rpc out', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'OTRS', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

 

Необходимо еще добавить в ODBC запись от имени администратора.

Если от имени пользователя прилинкованный сервер открывается нормально, а при запуске в SQL агенте ошибка, то смотрим этот ответ

 

The problem comes because the Temp folder of the User under which the SQL server service is running isn’t accessible under the credentials which the query is running. Try to to set the security of this temp folder with minimal restrictions. The dsn that gets created every time you run an openrowset query then can be recreated without any credentials conflict. This worked for me without any restart requirements.

2	Where would I find this Temp folder? – Slider345 Jan 7 ’13 at 17:37
3	For our specific configuration — Windows Server 2008 R2, SQL Server 2008 R2 — the specific TEMP folder we needed to share with domain users was: C:\Users\<SQL Server Service Account Name>\AppData\Local\Temp – fresh Oct 15 ’13 at 20:50
1	I’ve spent all day on this — I can’t believe this is the solution!!! But it worked so I am happy. – Warren Mar 5 ’14 at 0:59
	Without the permission fresh shared, you will only be able to run the script locally by open SSMS on the SQL Server. Another way is to add your own domain account to the local (SQL Server) Administrators group, which does the same trick. – Chjquest Feb 18 ’16 at 15:48
	Thank you so much, this answer has saved us so much time! – MartynJones87 Mar 17 ’16 at 8:34

(Нужно добавить права для папки Temp)