How to: Change Server Authentication Mode zz

How to: Change Server Authentication Mode

SQL Server 2008 R2

During installation, SQL ServerDatabase Engine is set to either Windows Authentication mode or SQL Server andWindows Authentication mode. This topic describes how to change the securitymode after installation.

If Windows Authentication mode isselected during installation, the sa login is disabled and a password isassigned by setup. If you later change authentication mode to SQL Server andWindows Authentication mode, the sa login remains disabled. To use the salogin, use the ALTER LOGIN statement to enable the sa login and assign a newpassword.

Security Note

The sa account is a well-known SQL Server account and it is often targeted by malicious users. Do not enable the sa account unless your application requires it. It is very important that you use a strong password for the sa login.

The sa login can only connect to theserver by using SQL ServerAuthentication.

Tochange security authentication mode

1. In SQL Server Management StudioObject Explorer, right-click the server, and then click Properties.
2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK.
3. In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

Torestart SQL Server from SQL Server Management Studio

• In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

Toenable the sa login by using Transact-SQL

• Execute the following statements to enable the sa password and assign a password.

other

ALTER LOGIN sa ENABLE ;

GO

ALTER LOGIN sa WITHPASSWORD = '<enterStrongPasswordHere>' ;

GO

Toenable the sa login by using Management Studio

1. In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.
2. On the General page, you might have to create and confirm a password for the sa login.
3. On the Status page, in the Login section, click Enabled, and then click OK.

Configure Windows Firewall for SQLServer 2008 Access

Topic Last Modified: 2009-07-20

In an Enterprise pool, if youinstall SQL Server 2008 on the computer that you designate as the back end andthe Windows Server 2008 operating system is installed, you must configure theWindows Firewall to allow any computer that can identify itself as a member ofeither the Domain network or Private network to access SQL Server usingTransmission Control Protocol (TCP) over port 1433. For details about ports towhich SQL Server 2008 may require access, see Configuring the Windows Firewallto Allow SQL Server Access at http://go.microsoft.com/fwlink/?LinkId=131093.For details about Network Location Profiles in Windows Server 2008, see NetworkLocation Types at http://go.microsoft.com/fwlink/?LinkId=131211.

Configure Windows Firewall on the Back-End DatabaseServer

1. Log on to the computer where you installed SQL Server for Office Communications Server 2007 R2.
2. Click Start, click Administrative Tools, and then click Windows Firewall with Advanced Security.
3. Right-click Inbound Rules, and then click New Rule.
4. In the New Inbound Rule Wizard dialog box, on the Rule Type page, click Port, and then click Next.
5. On the Protocol and Ports page, click TCP, click Specific local ports, type 1433, and then click Next.
6. On the Action page, click Allow the connection, and then click Next.
7. On the Profile page, do all of the following:
   • Select the Domain check box.
   • Select the Private check box.
   • Clear the Public check box.
8. Click Next.
9. On the Name page, click Name, and then type a meaningful name for the new inbound rule.
10. (Optional) Click Description (optional), and then type a description for the inbound rule.
11. Click Finish.

How to: Configure a Windows Firewall for DatabaseEngine Access

SQL Server 2008 R2

OtherVersions

Firewall systems help preventunauthorized access to computer resources. To access an instance of the SQLServer Database Engine through a firewall, you must configure the firewall onthe computer running SQL Server to allow access.

For more information about thedefault Windows firewall settings, and a description of the TCP ports that affectthe Database Engine, Analysis Services, Reporting Services, and IntegrationServices, see Configuringthe Windows Firewall to Allow SQL Server Access, and Connecting to SQLServer over the Internet. There are many firewall systems available. Forinformation specific to your system, see the firewall documentation.

Important

Opening ports in your firewall can leave your server exposed to malicious attacks. Make sure that you understand firewall systems before you open ports. For more information, see Security Considerations for a SQL Server Installation.

The principal steps to allow accessare:

1. Configure the Database Engine to use a specific TCP/IP port. The default instance of the Database Engine uses port 1433, but that can be changed. The port used by the Database Engine is listed in the SQL Server error log. Instances of SQL Server Express, SQL Server Compact 3.5 SP2, and named instances of the Database Engine use dynamic ports. To configure these instances to use a specific port, see How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager).
2. Configure the firewall to allow access to that port for authorized users or computers.

Note

The SQL Server Browser service lets users connect to instances of the Database Engine that are not listening on port 1433, without knowing the port number. To use SQL Server Browser, you must open UDP port 1434. To promote the most secure environment, leave the SQL Server Browser service stopped, and configure clients to connect using the port number.

Note

By default, Microsoft Windows XP Service Pack 2 enables the Windows Firewall, which closes port 1433 to prevent Internet computers from connecting to a default instance of SQL Server on your computer. Connections to the default instance using TCP/IP are not possible unless you reopen port 1433. The basic steps to configure the Windows XP firewall are provided in the following procedures. For more information, see the Windows documentation.

As an alternative to configuring SQLServer to listen on a fixed port and opening the port, you can list the SQLServer executable (Sqlservr.exe) as an exception to the blocked programs. Usethis method when you want to continue to use dynamic ports. Only one instanceof SQL Server can be accessed in this way.

Windows Vista and WindowsServer 2008 and later

---

The following procedures configurethe Windows Firewall by using the Windows Firewall with Advanced SecurityMicrosoft Management Console (MMC) snap-in. This snap-in is available beginningwith Windows Vista and Windows Server 2003. The Windows Firewall with AdvancedSecurity only configures the current profile. For more information about theWindows Firewall with Advanced Security, see Configuring theWindows Firewall to Allow SQL Server Access

Toopen a port in the Windows firewall for TCP access

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Toopen access to SQL Server when using dynamic ports

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Program, and then click Next.
4. In the Program dialog box, select This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default,SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Windows XP and WindowsServer 2003

---

The following procedures configurethe Windows Firewall by using the Windows Firewall item in the Windows XP andWindows Server 2003 Control Panel which only configures the firewall for thecurrent network location profile. The Windows Firewall can also be configuredby using the Windows Firewall with Advanced Security Microsoft ManagementConsole (MMC) snap-in and the netsh command line tool. For more informationabout these tools, see Configuring theWindows Firewall to Allow SQL Server Access.

Toopen a port in the Windows firewall for TCP access

1. On the Start menu, click Run, type firewall.cpl and then click OK.
2. In the Windows Firewall dialog box, click the Exceptions tab, and then click Add Port.
3. In the Add a Port dialog box, in the Name text box, type SQL Server <instance name>.
4. In the Port number text box, type the port number of the instance of the Database Engine, such as 1433 for the default instance.
5. Verify that TCP is selected, and then click OK.
6. To open the port to expose the SQL Server Browser service, click AddPort, type SQL Server Browser in the Name text box, type 1434 in the Port Number text box, select UDP, and then click OK.

Note

To allow named pipes access through the firewall, you must also enable File and Printer Sharing through the firewall.

1. Close the Windows Firewall and the Properties dialog boxes.

Note

Click Add Program in the Windows Firewall dialog box for additional options, such as granting access to specific programs and restricting access to certain IP addresses or network subnets. For more information, see the Windows documentation.

Toaccess a program through the Windows firewall

1. In the Windows Firewall dialog box, on the Exceptions tab, click Add Program.
2. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default,SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe.
3. To close the Windows firewall program, click OK twice.

For a brief tutorial about how toconfigure a static port, open the firewall, and connect to the Database Engineby using SQL Server Management Studio, see Tutorial: GettingStarted with the Database Engine.

Howto open up the Windows firewall for SQL Server 2008 on Windows Server 2008 R2

November 1, 2009

After you have installed SQL Server, you might have noticed that thefirewall will block access to it.

In order to remedy this, you will have to open 2 ports in the firewall.

TCP port 1433 for the actual SQL Server engine, and UDP port 1434 for theSQL Server Browser.

This could be done from the GUI, but I have found that it’s more convenientto do it from an elevated command prompt.

?

1 2 3 4

netsh firewall set portopening protocol=TCP port=1433 name = SQLPortEngine mode=ENABLE scope=SUBNET profile=CURRENT netsh firewall set portopening protocol=UDP port=1434 name = SQLPortBrowser mode=ENABLE scope=SUBNET profile=CURRENT

Or follow the manual GUI steps in this MSDN article:

为 SQL Server 2005 Express Edition 或 SQL Server 2005 Developer Edition 启用远程连接

必须为要从远程计算机连接到的每个 SQL Server 2005 实例启用远程连接。为此，请按照下列步骤操作：

1. 单击“开始”，依次指向“程序”、“Microsoft SQL Server 2005”和“配置工具”，然后单击“SQL Server 外围应用配置器”。
2. 在“SQL Server 2005 外围应用配置器”页上，单击“服务和连接的外围应用配置器”。
3. 在“服务和连接的外围应用配置器”页上，展开“数据库引擎”，依次单击“远程连接”和“本地连接和远程连接”，单击适用于您的环境的相应协议，然后单击“应用”。
   
   注意：请在接收到以下消息时单击“确定”：

直到重新启动数据库引擎服务后，对连接设置所做的更改才会生效。

1. 在“服务和连接的外围应用配置器”页上，展开“数据库引擎”，依次单击“服务”和“停止”，等待 MSSQLSERVER 服务停止，然后单击“启动”以重新启动 MSSQLSERVER 服务。

启用 SQL Server Browser 服务

如果您是通过使用实例名称来运行 SQL Server 2005 并且在连接字符串中没有使用特定的 TCP/IP 端口号，则必须启用SQL Server Browser 服务以允许远程连接。例如，使用<计算机名>\SQLEXPRESS 的默认实例名称安装的 SQL Server 2005 Express。不管您正在运行多少个 SQL Server 2005 实例，只需要启用一次 SQL Server Browser 服务。若要启用 SQL Server Browser 服务，请执行以下步骤。

重要说明：这些步骤可能会增加您的安全风险。这些步骤还 可能导致您的计算机或网络更易于受到恶意用户或恶意软件（如病毒）的攻击。我们之所以推荐本文介绍的这一过程，是为了使程序能够按照设计意图运行，或者为 了实现特定的程序功能。我们建议在进行这些更改之前，充分考虑在您的特定环境中实施这一过程可能带来的风险。如果您选择实施此过程，请采用任何适当的附加 步骤以保护您的系统。我们建议只有确实需要这一过程时才使用它。

1. 单击“开始”，依次指向“程序”、“Microsoft SQL Server 2005”和“配置工具”，然后单击“SQL Server 外围应用配置器”。
2. 在“SQL Server 2005 外围应用配置器”页上，单击“服务和连接的外围应用配置器”。
3. 在“服务和连接的外围应用配置器”页上，单击“SQL Server Browser”，在“启动类型”中单击“自动”选项，然后单击“应用”。
   
   注意：在单击“自动”选项后，每次启动 Microsoft Windows 时将自动启动 SQL Server Browser 服务。
4. 单击“启动”，然后单击“确定”。

注意：在计算机上运行 SQL Server Browser 服务时，计算机显示其上运行的每个 SQL Server 实例的实例名称和连接信息。如果不启用 SQL Server Browser 服务并且通过分配的 TCP 端口直接连接到SQL Server 实例，则可以降低此风险。本文不讨论如何通过TCP 端口直接到 SQL Server 实例。有关 SQL Server Browser 服务和连接到 SQL Server 实例的更多信息，请参见《SQL Server 联机丛书》中的以下主题：

• SQL Server Browser 服务
• 连接到 SQL Server 数据库引擎
• 客户端网络配置

在Windows 防火墙中创建例外

这些步骤适用于Windows XP Service Pack 2 (SP2) 和 Windows Server 2003 中包含的Windows 防火墙版本。如果您使用的是不同的防火墙系统，请参阅相应的防火墙文档以了解更多信息。

如果在运行SQL Server 2005 的计算机上运行防火墙，将会阻止访问SQL Server 2005 的外部连接，除非 SQL Server 2005 和 SQL Server Browser 服务可以通过防火墙进行通信。必须为每个要接受远程连接的SQL Server 2005 实例创建一个例外，并为SQL Server Browser 服务创建一个例外。

在安装SQL Server 2005 的程序文件时，SQLServer 2005 会使用一个实例ID 作为路径的一部分。若要为每个SQL Server 实例创建一个例外，必须确定正确的实例ID。若要获取实例ID，请执行以下步骤：

1. 单击“开始”，依次指向“程序”、“Microsoft SQL Server 2005”和“配置工具”，然后单击“SQL Server 配置管理器”。
2. 在“SQL Server 配置管理器”中，单击右窗格中的“SQL Server Browser 服务”，右键单击主窗口中的实例名称，然后单击“属性”。
3. 在“SQL Server Browser 属性”页上，单击“高级”选项卡，定位到属性列表中的实例 ID，然后单击“确定”。

若要打开Windows 防火墙，请单击“开始”，再单击“运行”，键入 firewall.cpl，然后单击“确定”。

在Windows 防火墙中为SQL Server 2005 创建例外

若要在Windows 防火墙中为SQL Server 2005 创建例外，请执行以下步骤：

1. 在 Windows 防火墙中，单击“例外”选项卡，然后单击“添加程序”。
2. 在“添加程序”窗口中，单击“浏览”。
3. 单击 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe 可执行程序，单击“打开”，然后单击“确定”。
   
   注意：上述路径可能会根据 SQL Server 2005 的安装位置而不同。MSSQL.1 这个占位符代表的是您在前面过程的步骤 3 中获得的实例 ID。
4. 对于每个需要为其创建例外的 SQL Server 2005 实例，重复步骤 1 到步骤 3。

在Windows 防火墙中为SQL Server Browser 服务创建例外

若要在Windows 防火墙中为SQL Server Browser 服务创建例外，请执行以下步骤：

1. 在 Windows 防火墙中，单击“例外”选项卡，然后单击“添加程序”。
2. 在“添加程序”窗口中，单击“浏览”。
3. 单击 C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe 可执行程序，单击“打开”，然后单击“确定”。
   
   注意：上述路径可能会根据 SQL Server 2005 的安装位置而不同。

When you try to connect to aninstance of Microsoft SQL Server 2005 from a remote computer, you may receive anerror message. This problem may occur when you use any program to connect toSQL Server. For example, you receive the following error message when you usethe SQLCMD utility to connect to SQL Server:

Sqlcmd: Error: Microsoft SQL NativeClient: An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by the fact thatunder the default settings SQL Server does not allow remote connections.

This problem may occur when SQLServer 2005 is not configured to accept remote connections. By default, SQLServer 2005 Express Edition and SQL Server 2005 Developer Edition do not allowremote connections. To configure SQL Server 2005 to allow remote connections,complete all the following steps:

• Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
• Turn on the SQL Server Browser service.
• Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Thisarticle describes how to complete each of these steps.

To enable remote connections on the instance of SQL Server2005 and to turn on the SQL Server Browser service, use the SQL Server 2005Surface Area Configuration tool. The Surface Area Configuration tool isinstalled when you install SQL Server 2005.

Back to the top

Enable remote connections for SQL Server 2005 Express or SQLServer 2005 Developer Edition

You must enable remote connections for each instance of SQLServer 2005 that you want to connect to from a remote computer. To do this,follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
   
   Note Click OK when you receive the following message:

Changes to Connection Settings will not take effect until yourestart the Database Engine service.

1. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.

Back to the top

Enable the SQL Server Browser service

If you are running SQL Server 2005 by using an instance nameand you are not using a specific TCP/IP port number in your connection string,you must enable the SQL Server Browser service to allow for remote connections.For example, SQL Server 2005 Express is installed with a default instance nameof Computer Name\SQLEXPRESS. You areonly required to enable the SQL Server Browser service one time, regardless ofhow many instances of SQL Server 2005 you are running. To enable the SQL ServerBrowser service, follow these steps.

Important These steps may increase your security risk. These steps mayalso make your computer or your network more vulnerable to attack by malicioususers or by malicious software such as viruses. We recommend the process thatthis article describes to enable programs to operate as they are designed to,or to implement specific program capabilities. Before you make these changes,we recommend that you evaluate the risks that are associated with implementingthis process in your particular environment. If you choose to implement thisprocess, take any appropriate additional steps to help protect your system. Werecommend that you use this process only if you really require this process.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic for Startup type, and then click Apply.
   
   Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

NoteWhen you run the SQL Server Browser service on a computer, the computerdisplays the instance names and the connection information for each instance ofSQL Server that is running on the computer. This risk can be reduced by notenabling the SQL Server Browser service and by connecting to the instance ofSQL Server directly through an assigned TCP port. Connecting directly to aninstance of SQL Server through a TCP port is beyond the scope of this article.For more information about the SQL Server Browser server and connecting to aninstance of SQL Server, see the following topics in SQL Server Books Online:

• SQL Server Browser Service
• Connecting to the SQL Server Database Engine
• Client Network Configuration

Back to the top

Create exceptions in Windows Firewall

These steps apply to the version of Windows Firewall that isincluded in Windows XP Service Pack 2 (SP2) and in Windows Server 2003. If youare using a different firewall system, see your firewall documentation for moreinformation.

If you are running a firewall on the computer that is running SQL Server 2005,external connections to SQL Server 2005 will be blocked unless SQL Server 2005and the SQL Server Browser service can communicate through the firewall. Youmust create an exception for each instance of SQL Server 2005 that you want toaccept remote connections and an exception for the SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install itsprogram files. To create an exception for each instance of SQL Server, you mustidentify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To openWindows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in WindowsFirewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe executable program, click Open, and then click OK.
   
   Note The path may be different depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service inWindows Firewall

To create an exception for the SQL Server Browser service inWindows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK.
   
   Note The path may be different depending on where SQL Server 2005 is installed.