>

Server连接中多个周边的失实分析,无法访谈SQL

- 编辑:www.bifa688.com -

Server连接中多个周边的失实分析,无法访谈SQL

背景:

6.连接的建立和问题排查

会话的建立分成2个部分:

1.连接,即找到这个实例

2.认证,告诉sql server谁要连接

目录

6.连接的建立和问题排查... 1

6.1协议选择和别名... 1

6.1.1 服务器网络配置... 1

6.1.2 SQL Server Browser的作用... 1

6.1.3 客户端网络配置... 2

6.1.4 客户端网络连接选择机制... 2

6.2 连接失败检测步骤——命名管道... 2

6.3连接失败检测步骤——TCP/IP. 2

6.3.1 SQL Server监听TCP/IP端口... 2

6.3.2 客户端TCP/IP协议配置... 2

6.3.3 TCP/IP连接keepalive机制... 3

6.3.4 配置SQL Server的keepalive. 3

6.3.5 配置客户端的keepalive. 3

6.3.6 TCP/IP连接问题的解决步骤... 3

6.4一般性网络错误... 4

6.5 利用Ring Buffer排查连接问题... 4

 

 

图片 1 一."SQL Server 不存在或访问被拒绝"
  这个是最复杂的,错误发生的原因比较多,需要检查的方面也比较多.

  朋友的环境第二天突然访问不了SQL Server,远程SQL Server用户无法登陆,但是本地SQL Server用户登录正常。

6.1协议选择和别名

常用协议有3种:

1.Shard Memory:本地访问会使用的,一般用不到

2.TCP/IP

3.Named Pipes:命名管道不是基于网络协议的,而是基于Server Message Block套件的一种协议,使用IPC$共享来无缝和透明的传输数据和用户认证上下文,在访问IPC$共享的时候先要通过Windows认证,这也是命名管道的好处之一。

  一般说来,有以下几种可能性:

报错:

6.1.1 服务器网络配置

SQL Server的配置管理器可以设置各个协议的开启和关闭。配置好协议之后重启服务,会在errorlog中看到服务是否正常启动。

  1.SQL Server名称或IP地址拼写有误
  2.服务器端网络配置有误
  3.客户端网络配置有误

  用户XX登录失败(MicroSoft SQL Server,错误18456)

6.1.2 SQL Server Browser的作用

对于命名实例,每次启动绑定的端口不一样。所以SQL Server开发了一套SQL Server解析协议(SSRP)用来监听UDP1434端口。当一个客户端要访问这台服务器上的SQL Server实例,都会先询问UDP1434端口,然后由SSRP协议告诉客户端本台服务器上所安装的SQL Server实例的端口号及命名管道。

SQL Server Browser最小权限如下:

1.拒绝通过网络访问该计算机

2.拒绝本地登录

3.拒绝以批处理作业登录

4.拒绝通过“终端服务”登录

5.作为服务登录

6.读写与网络通信相关的SQL Server注册项

SQL Server Browser读取注册表信息,识别计算机上的所有实例,并注明他们使用的端口和命名管道。

  要解决这个问题,我们一般要遵循以下的步骤来一步步找出导致错误的原因.

排查:

6.1.3 客户端网络配置

客户端有4中驱动:

1.MDAC或者WDAC,基于windows,不需要安装,可以通过cliconfg.exe配置

2.SQL Server Native Client,SQL Server带的安装了才有,随SQL Server版本变化而变化。在SQL Server配置管理器也可以对Native Client进行配置。

3.SQLClient,客户端应用程序使用的是托管编程代码,目前一般使用ADO.NET来连接SQL Server。SQLClient没有固定配置工具,是按照Shared Memory->TCP/IP->Named Pipes顺序来连接的。

4.JDBC,用来给Java程序连接SQL Server。

  首先,检查网络物理连接
  ping <服务器IP地址/服务器名称>
  如果 ping <服务器IP地址> 不成功,说明物理连接有问题,这时候要检查硬件设备,如网卡,HUB,路由器等.
  还有一种可能是由于客户端和服务器之间安装有防火墙软件造成的,比如 ISA Server.防火墙软件可能会屏蔽对 ping,telnet 等的响应,因此在检查连接问题的时候,我们要先把防火墙软件暂时关闭,或者打开所有被封闭的端口.

  对与无法连接服务器的,一般的排查手段,也是最常用的手段。

6.1.4 客户端网络连接选择机制

SQL Server网络连接机制:

1.SQL Server有自己的网络协议,配置选项,决定SQL Server侦听哪些协议

2.一台服务器上可以有多个SQL Server实例,每个实例使用不同的端口和管道。SQL Server Browser通过读取注册表知道所有实例的网络配置信息。

3.客户端的数据库连接组件上可以配置候选的网络协议。

客户端开启了多个网络协议,一般最后决定使用什么协议的顺序如下:

1.连接字符串中指定协议

2.客户端别名

3.寻找相应数据驱动的LastConnect注册记录

4.通过SQL Server Browser得知端口号或者管道名称

  如果ping <服务器IP地址> 成功而,ping <服务器名称> 失败,则说明名字解析有问题,这时候要检查 DNS 服务是否正常.
  有时候客户端和服务器不在同一个局域网里面,这时候很可能无法直接使用服务器名称来标识该服务器,这时候我们可以使用HOSTS文件来进行名字解析,具体的方法是:

  1.因为本地登录正常,那么查看1433端口是否监听。

6.2 连接失败检测步骤——命名管道

  1.使用记事本打开HOSTS文件(一般情况下位于C:WINNTsystem32driversetc).
添加一条IP地址与服务器名称的对应记录,如:
172.168.10.24 myserver

  2.远程 telnet 1433 端口是不是通的。

6.3连接失败检测步骤——TCP/IP

  2.或在 SQL Server 的客户端网络实用工具里面进行配置,后面会有详细说明.

  经过排查发现,监听正常,telnet 也是通的。

6.3.1 SQL Server监听TCP/IP端口

SQL Server监听端口,可以为机器上的每个IP地址都设置独立的端口号(无法配置出来),也可以为所有的IP设置统一的端口号。

  其次,使用 telnet 命令检查SQL Server服务器工作状态
telnet <服务器IP地址> 1433

  这个时候就开始犯愁了,telnet 通的按理没理由无法连接到远程服务器上。于是我使用SQLCMD登录仔细看看 18456的错误的state 到底是多少,发现State是1。

6.3.2 客户端TCP/IP协议配置

  如果命令执行成功,可以看到屏幕一闪之后光标在左上角不停闪动,这说明 SQL Server 服务器工作正常,并且正在监听1433端口的 TCP/IP 连接,如果命令返回"无法打开连接"的错误信息,则说明服务器端没有启动 SQL Server 服务,也可能服务器端没启用 TCP/IP 协议,或者服务器端没有在 SQL Server 默认的端口1433上监听.

  到这里就无解了,从来没见过这样的情况。

6.3.3 TCP/IP连接keepalive机制

客户端和服务器之间的tcp连接是长连接,当客户端连接到服务器的时候指定了keepaliveinterval和keepalivetime参数,在连接空闲时间超过keepalivetime,tcp就会以keepaliveinterval为间隔自动发出keepalive包测试连接是否存活。如果keepalive检测次数超过注册表的TcpMaxDataRetransmissions的定义,对方还是没有反应,就会关闭这个有问题的连接。

Sql server的keepalivetime为30s,keepaliveinterval为1s,windows tcp配置默认TcpMaxDataRetransmissions为5s。

服务端的管服务端的,客户端的管客户端的,但是任何一个超过阀值都会关闭连接。

  接着,我们要到服务器上检查服务器端的网络配置,检查是否启用了命名管道.是否启用了 TCP/IP 协议等等,可以利用 SQL Server 自带的服务器网络使用工具来进行检查.

处理:

6.3.4 配置SQL Server的keepalive

可以在SQL Server配置管理器中配置tcp的keepalive时间。当然也可以在注册表上修改。

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.MSSQLSERVER MSSQLServerSuperSocketNetLibTcp

TcpMaxDataRetransmissions也可以在注册表上修改:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesTcpipParameters

  点击:程序 Microsoft SQL Server 服务器网络使用工具

  重点来了,第二天,我朋友和我说,是因为加了网闸设备导致的。

6.3.5 配置客户端的keepalive

任何客户端都有keepalive机制,keepalivetime为30s,keepaliveinterval为1s。其中只有native client可以在SQL Server配置管理器上修改。

图片 2

当然可以在注册表上修改

  打开该工具后,在"常规"中可以看到服务器启用了哪些协议.
  一般而言,我们启用命名管道以及 TCP/IP 协议.
  点中 TCP/IP 协议,选择"属性",我们可以来检查 SQK Server 服务默认端口的设置
  一般而言,我们使用 SQL Server 默认的1433端口.如果选中"隐藏服务器",则意味着客户端无法通过枚举服务器来看到这台服务器,起到了保护的作用,但不影响连接.

 

6.3.6 TCP/IP连接问题的解决步骤

解决问题思路:

1.验证SQL Server是否监听端口,可以查看错误日志

2.验证SQL Server监听的端口和配置的值是否一致

3.检查网络是否正常

4.telnet查看是否可以连接到某个端口

5.检查登录用户权限

  接下来我们要到客户端检查客户端的网络配置
  我们同样可以利用 SQL Server 自带的客户端网络使用工具来进行检查,
  所不同的是这次是在客户端来运行这个工具.

6.3.6.1 监听多个端口

多个端口的监听,可以在设置端口是用逗号隔开

图片 3

  点击:程序 Microsoft SQL Server 客户端网络使用工具

6.3.6.2 端口绑定失败

端口绑定失败,在启动是时候会报错。

可以查看端口是否被占用

  打开该工具后,在"常规"项中,可以看到客户端启用了哪些协议.
  一般而言,我们同样需要启用命名管道以及 TCP/IP 协议.
  点击 TCP/IP 协议,选择"属性",可以检查客户端默认连接端口的设置,该端口必须与服务器一致.

6.3.6.3 检查连接使用的协议

SELECT*FROMsys.dm_exec_connections

  单击"别名"选项卡,还可以为服务器配置别名.服务器的别名是用来连接的名称,连接参数中的服务器是真正的服务器名称,两者可以相同或不同.别名的设置与使用HOSTS文件有相似之处.

6.3.6.4 访问防火墙后的SQL Server

  通过以上几个方面的检查,基本上可以排除第一种错误.

6.4一般性网络错误

  二."无法连接到服务器,用户xxx登陆失败"

6.5 利用Ring Buffer排查连接问题

Ring Buffer,可以捕捉每个由服务器发起的关闭连接记录,包含会话异常中断或者登陆失败,Ring Buffer最多1000条数据。

Connectivity Ring Buffer有3种记录:ConnectionClose,Error,LoginTimers。

Connectivity Ring Buffer可以让你在不能使用NetWork Monitor情况下解决棘手的问题。

Connectivity Ring Buffer中的LoginTimers记录了整个登陆过程所话的时间。

SELECTCAST(record ASXML) record

       ,CAST( record ASXML). value('(//Record/ConnectivityTraceRecord/RecordTime)[1]','datetime')  recordtime

       ,CAST( record ASXML). value('(//Record/ConnectivityTraceRecord/RecordType)[1]','varchar(20)')  Recordtype

FROMsys.dm_os_ring_buffers

WHERE ring_buffer_type ='RING_BUFFER_CONNECTIVITY'

 

  该错误产生的原因是由于SQL Server使用了"仅 Windows"的身份验证方式,因此用户无法使用SQL Server的登录帐户(如 sa )进行连接.解决方法如下所示:

  1.在服务器端使用企业管理器,并且选择"使用 Windows 身份验证"连接上 SQL Server
  2.展开"SQL Server组",鼠标右键点击SQL Server服务器的名称,选择"属性",再选择"安全性"选项卡
  3.在"身份验证"下,选择"SQL Server和 Windows ".
  4.重新启动SQL Server服务.

  在以上解决方法中,如果在第 1 步中使用"使用 Windows 身份验证"连接 SQL Server 失败,那就通过修改注册表来解决此问题:

  1.点击"开始" "运行",输入regedit,回车进入注册表编辑器
  2.依次展开注册表项,浏览到以下注册表键:
[HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer
MSSQLServer]
  3.在屏幕右方找到名称"LoginMode",双击编辑双字节值
  4.将原值从1改为2,点击"确定"
  5.关闭注册表编辑器
  6.重新启动SQL Server服务.

 此时,用户可以成功地使用sa在企业管理器中新建SQL Server注册,
  但是仍然无法使用Windows身份验证模式来连接SQL Server.
  这是因为在 SQL Server 中有两个缺省的登录帐户:
  BUILTINAdministrators
  <机器名>Administrator 被删除.
  要恢复这两个帐户,可以使用以下的方法:

  1.打开企业管理器,展开服务器组,然后展开服务器
  2.展开"安全性",右击"登录",然后单击"新建登录"
  3.在"名称"框中,输入 BUILTINAdministrators
  4.在"服务器角色"选项卡中,选择"System Administrators"
  5.点击"确定"退出
  6.使用同样方法添加 <机器名>Administrator 登录.

  说明:

  以下注册表键:
HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServer
MSSQLServerLoginMode
  的值决定了SQL Server将采取何种身份验证模式.
  1.表示使用"Windows 身份验证"模式
  2.表示使用混合模式(Windows 身份验证和 SQL Server 身份验证).

  三.提示连接超时

  如果遇到第三个错误,一般而言表示客户端已经找到了这台服务器,并且可以进行连接,不过是由于连接的时间大于允许的时间而导致出错.
  这种情况一般会发生在当用户在Internet上运行企业管理器来注册另外一台同样在Internet上的服务器,并且是慢速连接时,有可能会导致以上的超时错误.有些情况下,由于局域网的网络问题,也会导致这样的错误.

  要解决这样的错误,可以修改客户端的连接超时设置.
  默认情况下,通过企业管理器注册另外一台SQL Server的超时设置是 4 秒,而查询分析器是 15 秒(这也是为什么在企业管理器里发生错误的可能性比较大的原因).

  具体步骤为:
  企业管理器中的设置:
  1.在企业管理器中,选择菜单上的"工具",再选择"选项"
  2.在弹出的"SQL Server企业管理器属性"窗口中,点击"高级"选项卡
  3.在"连接设置"下的"登录超时(秒)"右边的框中输入一个比较大的数字,如

  查询分析器中的设置:
  工具 选项 连接 将登录超时设置为一个较大的数字

  连接超时改为0

  1、先保证ping通
  2、在dos下写入telnet ip 1433不会报错
  3、用ip连如企业管理器:
  企业管理器>右键SQlserver组>新建sqlserver注册>下一步>写入远程实例名(IP,机器名)>下一步>选Sqlserver登陆>下一步>写入登陆名与密码(sa,pass)>下一步>下一步>完成
  4、如果还不行:
sqlserver服务器>开始菜单>SQLserver>服务器网络实用工具>启用 WinSock代理>代理地址:(sqlserver服务器IP)>代理端口>1433>OK了
  5、如果还不行:
sqlserver客户端>开始菜单>SQLserver>客户端网络实用工具>别名>添加>写入别名如"大力">"网络库"选tcp/ip>服务器名称写入远程ip或实例名>OK了 

本文由88bifa必发唯一官网发布,转载请注明来源:Server连接中多个周边的失实分析,无法访谈SQL