加载中…SQLServer 远程链接MySql数据库详解
(2014-11-19 18:21:47)
标签：
连接sqlserver远程mysql数据库 |
分类： SQLServer |
SQLServer 远程链接MySql数据库详解
by:授客 QQ：1033553122
测试环境：Microsoft Windows XP Professional 版本2000 Service Pack 3
SQL_2005_x86简体中文企业版
MySql数据库远程服务器
第一步：安装MySQL odbc driver
在SQL SERVER所在主机上安装MYSQL ODBC Driver;
第二步：配置ODBC数据源
安装好后,在管理工具-ODBC数据源-系统DSN-添加-选择安装的MYSQL ODBC Driver，比如 MYSQL ODBC 3.51 DRIVER-完成
http://sucimg.itc.cn/sblog/o29ec55a1bb8c8ab6e9584e1114050246远程链接MySql数据库详解" TITLE="SQLServer
http://sucimg.itc.cn/sblog/o4d1b1eaf612bd7ed9fd3fef91514f902远程链接MySql数据库详解" TITLE="SQLServer
点击完后跳出如下界面，填写MYSQL连接参数
说明：
Data Source Name:数据源名称，自定义
Decription:描述,自定义
Server:MYSQL服务器的IP
User:连接账号(在MYSQL上授权的账号，同时给予相应权限)
Password:密码
Database:选择链接的数据库
Port:MYSQL的端口,默认的是3306.可以根据实际的MYSQL服务器端口，在如下界面中更改
填好之后,点下test.成功了则会如图跳出提示框.
http://sucimg.itc.cn/sblog/ocd8c8e0b3af84c24383af145efbf75e0远程链接MySql数据库详解" TITLE="SQLServer
填好后，点击OK，查看如下。
http://sucimg.itc.cn/sblog/o2161f87645bfcc65271592815cc06c2c远程链接MySql数据库详解" TITLE="SQLServer
点击配置的系统DNS（test为例）,点击配置
http://sucimg.itc.cn/sblog/o743a22dbdbcf0dfe32acd371c7351823远程链接MySql数据库详解" TITLE="SQLServer
注意：必须根据实际要远程连接的MYSQL数据库服务器的ip，端口，授权用户名，密码，要访问的数据库来填写。
第三步：连接SQL Server数据库
开始-所有程序-Microfsoft SQL Server 2005-SQL Server Management Studio
登陆方式1-图1
http://sucimg.itc.cn/sblog/oc1c78c4cc6147e143a0ec6fbc90f6537远程链接MySql数据库详解" TITLE="SQLServer
登陆方式2-图2
http://sucimg.itc.cn/sblog/o288aa0ec6ab400eb13b686a1a40cd195远程链接MySql数据库详解" TITLE="SQLServer
注：登陆方式有两种
服务器名：计算机名\实例名，如下图，这里实例名是安装时，，选择“命名实例”输入的名称
http://sucimg.itc.cn/sblog/o3d1f31e3611588850acc60a158a4692e远程链接MySql数据库详解" TITLE="SQLServer
如果选择“登录方式2-图2”中的登录方式，Server身份验证方式登录，如以sa用户登录，则需要输入对应的密码，该密码默认也是在如下图示安装的时候输入的密码。
http://sucimg.itc.cn/sblog/of51361c520b92070eeca107f93e335a5远程链接MySql数据库详解" TITLE="SQLServer
据说，EXPRESS版第一次登陆默认只能为Windows身份验证登陆，我没注意这个问题，如果真遇到这个问题，参考文档“SQLServer 2005Windows验证如何改为混合模式验证”。
第四步：创建链接服务器
这里我们以sa用户，登陆本地SQL Server服务器,找到链接服务器,创建链接服务器
http://sucimg.itc.cn/sblog/oc32bf0732a928c85db477bed5a68456b远程链接MySql数据库详解" TITLE="SQLServer
点击绿色的那个实例->服务器对象->链接服务器->右键->新建链接服务器->弹出界面
注意
1访问接口：Microsoft OLE DB Provider for ODBC Drivers”；
2数据源，就是我们之前配置的odbc数据源test ,必须存在；
其它可不填，点击“确定”
第五步：测试
语法：OPENQUERY(链接服务器名, '查询语句')
如：
SELECT * FROM OPENQUERY (test, 'select * from tobj_operate')
执行能成功则可以。
UPDATE OPENQUERY (test, 'SELECT id FROM tobj_operate WHERE id = 101')
SET name = 'hello';
INSERT OPENQUERY (test, 'SELECT id FROM tobj_operate')
VALUES ('hello');
DELETE OPENQUERY (test, 'SELECT id FROM tobj_operate WHERE name = ''hello''');
1，防止执行上述查询时出错，要进行如下设置
如图，点击"SQL Server外围应用配置器"->”功能的外围应用配置”->Database Engine->即席远程查询->勾取“启用OPENROWSET和OPENDATASOURCE 支持”
http://sucimg.itc.cn/sblog/o161fbcdc38bac18058c6b493ec3e5311远程链接MySql数据库详解" TITLE="SQLServer
2，可能还会遇到Ad Hoc Distributed Queries组件被禁用的问题
参见 排错-Ad Hoc Distributed Queries组件被禁用的解决办法
SELECT * FROM 链接服务器名.远程数据库名.dbo.远程数据库中的表;
SELECT * FROM test.mydatabase.dbo.tobj_operate;
注：这里用的是可视化的方式来创建远程连接。如果用命令方式呢？
答案如下：
1.新建查询
2.语句代码：
--创建链接服务器
exec sp_addlinkedserver 'test', '', 'MSDASQL ','10.4.120.11';
exec sp_addlinkedsrvlogin 'test', 'false', null, 'rock', '123456';
或者
exec sp_addlinkedserver
EXEC sp_addlinkedsrvlogin
'123456' --密码
注意：这里的rock为授权用户。
授权方法：
先要在远程MYSQL服务器中进行授权，让主机能访问MYSQL数据库，如下:
在mysql数据库执行语句如下：
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.4.120.11' IDENTIFIED BY '123456' WITH GRANT OPTION;
注：执行上述字符界面的查询，遇到如下问题：
链接服务器"test"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "登录超时已过期"。
链接服务器"mylink"的 OLE DB 访问接口 "SQLNCLI" 返回了消息 "建立到服务器的连接时发生错误。连接到 SQL Server 2005 时，默认设置 SQL Server 不允许远程连接这个事实可能会导致失败。"。
消息 53，级别 16，状态 1，第 0 行
命名管道提供程序: 无法打开与 SQL Server 的连接 [53].
解决方案：
1.cmd命令行，键入 netstat –an –p tcp 没找到0.0.0.0：1433的信息，
2.键入telnet
localhost 1433
测试，连接失败
参数说明：
-an 显示所有连接的端口并用数字表示
-p proto 显示 proto 指定的协议的连接；proto可以是: TCP、UDP、TCPv6或UDPv6之一
SqlServer查询分析器中键入:print@@version
显示如下：
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
3.下载sp4补丁并安装，
再次执行语句：print @@version
Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86)
4.把服务端和客户端的tcp/ip协议端口都设置成1433，然后cmd执行netstat –an –p tcp
C:\Documents and Settings\Administrator>netstat -an -p tcp
http://sucimg.itc.cn/sblog/o0bd195d275d26d20871726e26c18def4远程链接MySql数据库详解" TITLE="SQLServer能找到端口了，可是执行还是不行，采用'MSDASQL'时，执行下述的查询出现
[MySQL][ODBC 3.51 Driver]Access denied for user: 'root@...(Using password: YES)……错误提醒
EXECSP_DROPSERVER 'mylink','droplogins'
EXECSP_ADDLINKEDSERVER
EXECSP_ADDLINKEDSRVLOGIN
SELECT* FROM OPENQUERY(mylink, 'select * from tobj_operate')
成功了。
删除登陆
exec sp_droplinkedsrvlogin 'test', 'rock'
删除链接,同时删除登陆
execsp_dropserver 'test','droplogins'
说明：
@rmtuser，@rmtpassword的值不为NULL，则表示“使用此安全上下文建立连接”，如把这两个设置为NULL，则不使用安全上下文建立链接，如下图>>
右键“链接服务器名称”->属性http://sucimg.itc.cn/sblog/of54e28edaf2c0af452ee122e937413d5远程链接MySql数据库详解" TITLE="SQLServer
sp_addlinkedserver 语法参考：
http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_8gqa.htm
sp_ addlinkedsrvlogin语法参考
http://www.yesky.com/imagesnew/software/tsql/ts_sp_adda_6e26.htm
sp_ droplinkedsrvlogin 语法参考：
http://www.yesky.com/imagesnew/software/tsql/ts_sp_drop_59v2.htm
sp_ dropserver 语法参考：
http://www.yesky.com/imagesnew/software/tsql/ts_sp_drop_9c1e.htm
上述过程中可能会遇到的其它问题
问题1，使用SQLOLEDB provider
链接服务器"xxx"（这里是test）的OLE DB 访问接口"SQLNCLI" 返回了消息"登录超时已过期"。
链接服务器"xxx"的OLE DB 访问接口"SQLNCLI" 返回了消息"建立到服务器的连接时发生错误。连接到SQL Server 2005 时，默认设置SQL Server 不允许远程连接这个事实可能会导致失败。"。
消息53，级别16，状态1，第0 行
命名管道提供程序: 无法打开与SQL Server 的连接[53].
1，连接到SQL Server 2005 时，默认设置SQL Server 不允许远程连接这个事实可能会导致失败。
常规检查：
1.1 Microsoft SQL Server 2005 -> 配置工具 -> SQL Server外围应用配置器 -> 服务和连接的外围应用配置器 -> 远程连接->把"允许TCP/IP和命名管道"勾上,当然"仅TCP/IP"也可以
http://sucimg.itc.cn/sblog/o9bb6e5d91fcfb8ef72754f544c94eb09远程链接MySql数据库详解" TITLE="SQLServer
1.2 如图，Microsoft SQL Server 2005 -> 配置工具 ->SQL Server Configuration Manager，启动协议，并设置网络端口。
点击“SQL2005的协议”，把Shared Memory，NamedPipes，TCP/IP的状态改为“已启用“（特别是TCP/IP），客户端协议貌似默认开启。
http://sucimg.itc.cn/sblog/o0778d1b62c1bd4b0229dd97f15c3763c远程链接MySql数据库详解" TITLE="SQLServer
http://sucimg.itc.cn/sblog/oeff70920780a7d12f0ee460b7f14dc16远程链接MySql数据库详解" TITLE="SQLServer
说明：
Shared Memory:
避免了运行在同一台计算机上的客户端和服务器之间的进程间封送(跨越进程边界传送信息之前包装信息的方式).客户端直接访问服务器存储数据的内存映射文件.Shared Memory去除了大量的管理开销,速度非常快.只有在本地连接服务器时,Shared Memory才是有用的(例如,web服务器与数据库安装在同一台服务器上),但是它能极大地提高性能.
Named Pipes：
在TCP/IP不可用时,或者没有提供域名服务(DNS)的服务器从而无法把服务器名解析成TCP/IP下的地址时,Named Pipes可能会非常有用.Named Pipes的使用正在减少.既然无论如何都要启用TCP/IP协议,为什么还要加入一条协议呢?(多打开一个协议,就多一份风险)
TCP/IP
TCP/IP已经成为事实上的网络标准协议，从SQLserver2000开始，就在Sqlserver中的默认提供，如果你想通过Internet直接连接到Sqlserver上，则它是惟一的选择。
VIA：
右击TCP/IP-> IP 地址，设置服务器监听监听IP和端口
关于IP地址设置：
IP 地址从IP1到IP2，IP3……IPn,还有个IPALL
IP1中IP地址，设置为localhost，即回环地址127.0.0.1
IP1~IPn中的IP地址，为本机上其它IP地址
关于端口设置：
如下图，如果TCP/IP -> 协议，对话框中设置“全部侦听“为是，那么监听端口为IPALL下指定的“TCP 端口”,否则监听端口为IPn中指定的监听“TCP 端口”
注：TCP 动态端口，如果设置0，表示监听动态端口，一般保留为空
http://sucimg.itc.cn/sblog/o0fe32a589c28c97e518cc274570f3963远程链接MySql数据库详解" TITLE="SQLServer
http://sucimg.itc.cn/sblog/oe1e93678f19ddfde4dbd98dcb6c650fe远程链接MySql数据库详解" TITLE="SQLServer
接着，设置客户端协议的TCP端口，设置为服务端的TCP修改后的端口3533，操作同上
http://sucimg.itc.cn/sblog/od52fe3833d4b220154f59be3dcd7eccf远程链接MySql数据库详解" TITLE="SQLServer
http://sucimg.itc.cn/sblog/o151d95efa8d2f725bb7daac5a8caaa13远程链接MySql数据库详解" TITLE="SQLServer
按照以上设置完成客户端端口更改，在程序中的数据库连接字符串也应该做相应的更改，否则还是无法连接打数据库。
Provider=SQLNCLI.1;Persist Security Info=True;User ID=****;Password=**;Initial Catalog=tablename;Data Source=202.000.000.000,3533.
这里所有的设置完成之后，重新启动截图中的”SQL Server2005服务”
http://sucimg.itc.cn/sblog/of9d943b2b4030a26adf5ba2505624dac远程链接MySql数据库详解" TITLE="SQLServer
注意：对于客户端端口的修改，若直接修改“客户端协议”的话，会造成连接其他默认端口的数据库出错，可以利用新建别名的方式来解决此问题，如下图所示
http://sucimg.itc.cn/sblog/o17455ee9e546e89f469e277efd123f81远程链接MySql数据库详解" TITLE="SQLServer
客户端连接：
http://sucimg.itc.cn/sblog/o9a146000e4bb562075583de18a5cfefd远程链接MySql数据库详解" TITLE="SQLServer
1.3在相应的防火墙新增个例外,比如默认的1433或者刚才修改的服务端的TCP端口(如有修改过默认端口)，这样就可以远程连接了
http://sucimg.itc.cn/sblog/o3ddbb3e7c945d8ebb4b38f9d147deeb7远程链接MySql数据库详解" TITLE="SQLServer
问题2 ODBC驱动不匹配。
这次遇到的“消息53……”问题就是驱动问题，换个版本的MySQL ODBC驱动解决。
问题3 登录问题。
常规性检查，
3.1. Microsoft SQL Server 2005 -> 配置工具 -> SQL Server外围应用配置器，启动服务，点击Database Engine->服务，把服务类型改为自动，并启动服务，
http://sucimg.itc.cn/sblog/ob427ebd3f80fab1bf33b93503e5bd256远程链接MySql数据库详解" TITLE="SQLServer
对SQL Server Browser服务，也做同样的操作。
http://sucimg.itc.cn/sblog/o2316c5565c16ec8121c8174c5c12d455远程链接MySql数据库详解" TITLE="SQLServer
喜欢
0
赠金笔