none
sqlserver 2005 express远程连接的问题 RRS feed

  • 问题

  • 为了解决与另一台机器的sqlserver 2005 express连接的问题,我查找了一些资料,但无论如果也不能连接,最后,当我关闭服务器上的防火墙的时候,发现终于能够连接,并且,在再次打开防火墙时,客户端也是可以连接到服务器上的。

    请问,这一情况是如何产生的,应当怎么样解决呢?

    在关闭防火墙之前,我主要参考了以下做法:

    默认情况下,SQLServerExpressEdition 2005和sql server 2005开发版不允许远程连接。

    要允许远程连接sql server 2005 ,需要完成以下步骤:

    在SQLServer 实例上启用远程连接。

    打开服务器 SQLBrowser 服务。

    配置防火墙以允许SQLBrowser 服务和SQLServer服务网络通信。

    在SQLServer 实例上启用远程连接

    1.指向“开始->程序->Microsoft sql server 2005->配置工具->SQL Server 外围应用配置器”

    2.在“sql server 2005 外围应用配置器”页, 单击“服务和连接的外围应用配置器”

    3.然后单击展开“数据库引擎”, 选中“远程连接”,在右边选中“本地连接和远程连接”,

    再选择要使用的协议,单击“应用”,您会看到下消息:

    “直到重新启动数据库引擎服务后,对连接设置所做的更改才会生效。”,单击“确定”按钮返回

    4.展开“数据库引擎”, 选中“服务”,在右边单击“停止”,等到 MSSQLSERVER 服务停止,

    然后单击“启动”,重新启动MSSQLSERVER 服务。

    启用 SQLServer 浏览器服务

    1.指向“开始->程序->Microsoft sql server 2005->配置工具->SQL Server 外围应用配置器”

    2.在“sql server 2005 外围应用配置器”页, 单击“服务和连接的外围应用配置器”

    3.然后单击展开“sql server Browser”, 选中“服务”,在右边“启动类型”选择“自动”,

    再单击“启动”,单击“确定”按钮返回

    在Windows 防火墙中为“sql server 2005”创建例外

    1.在 Windows 防火墙设置界面中,选择“ 例外” 选项卡,然后单击“添加程序”

    2.在“添加程序窗口”中单击“浏览”

    3.然后找到“C:ProgramFilesMicrosoft FilesMicrosoft sql server MSSQL.1 MSSQLBinnsqlservr.exe”,

    单击“确定”返回

    注意 : 路径可能会根据 sql server 2005 安装不同而不同。 MSSQL.1 是占位符,对应数据库实例ID。

    4.对每个需要打开远程访问的sql server 2005 实例,重复步骤 1 至 3。

    在Windows 防火墙中为“SQLBrowser”创建例外

    1.在 Windows 防火墙设置界面中,选择“ 例外” 选项卡,然后单击“添加程序”

    2.在“添加程序窗口”中单击“浏览”

    3.然后找到“C:ProgramFilesMicrosoft FilesMicrosoft sql server90Sharedsqlbrowser.exe”,

    单击“确定”返回

    注意 : 路径可能会根据 sql server 2005 安装不同而不同。

    2010年4月7日 12:51

答案

  • Answer is quoted from: http://technet.microsoft.com/en-us/library/cc739645(WS.10).aspx

    A Windows Firewall Setting Does Not Take Effect

    Updated: March 28, 2005

    Applies To: Windows Server 2003, Windows Server 2003 R2, Windows Server 2003 with SP1, Windows Server 2003 with SP2

    This problem most commonly occurs when you add a program or port to the exceptions list, but the program or port does not behave as though it's in the exceptions list. This problem can also occur when you configure other Windows Firewall settings, such as log file settings and notification settings, and the settings do not seem to take effect.

    Cause

    Usually this problem occurs because the exceptions and settings were configured in one profile and the computer is using another profile. Windows Firewall settings can be configured in two profiles: the domain profile, which is used when a computer is connected to a network in which the computer's domain account resides; and the standard profile, which is used when a computer is connected to a network in which the computer's domain account does not reside, such as a public network.

    This problem can also occur if you add a program to the exceptions list, run the program, and then start Windows Firewall. In this case, Windows Firewall cannot track the ports that the program is using because they were assigned before Windows Firewall was started.

    This problem can also occur when there is a Group Policy hierarchy problem. For example, if you configure Windows Firewall settings in a Group Policy Object (GPO) that inherits policy settings from another GPO, then your Windows Firewall settings might never get applied.

    Solution

    To see if you configured the Windows Firewall setting in the wrong profile, you must first determine which profile your computer is currently using.

    To determine which profile your computer is currently using
    • At the command prompt, type netsh firewall show currentprofile, and press ENTER.

    Next, you need to verify your Windows Firewall settings in both the domain and standard profiles.

    To verify Windows Firewall settings in the domain and standard profiles
    • At the command line type netsh firewall show config, and then press ENTER.

      Windows Firewall configuration settings are displayed for both the standard and domain profiles.

    If you configured a setting in a profile different from the one your computer is currently using, try configuring the setting in the appropriate profile.

    If you still have a problem with the setting, try restarting your computer. Be sure to start Windows Firewall before you start any applications that are in the Windows Firewall exceptions list.

    If you still have a problem with the setting, and your organization uses Group Policy to manage Windows Firewall settings, use the Resultant Set of Policy snap-in to determine how policy settings are being applied to the computer.

    To determine which policies are being applied to a computer
    1. At the command prompt, type rsop.msc, and press ENTER.

    2. In the console tree, open Computer Configuration, open Administrative Templates, open Network, and then click Network Connections.

    3. In the console tree, click Windows Firewall, and double-click any policies that appear in the details pane to learn more about the policy.

    4. In console tree, click Domain Profile, and double-click any policies that appear in the details pane to learn more about the policy.

    5. In the console tree, click Standard Profile, and double-click any policies that appear in the details pane to learn more about the policy.


    Mark as Answer if it helps. This posting is provided "AS IS" with no warranties, confers no rights.
    2010年4月9日 8:23