When deploying Lync Server 2010 in a large organization, you are likely to encounter custom Microsoft SQL Server network configurations, such as running the SQL Server back-end instance on a non-standard static SQL port or the requirement to run with the SQL Server Browser service disabled. This article describes how you can set up a SQL Server instance to listen on custom ports, and how to configure Microsoft Lync Server 2010 to operate in such a configuration.
Author: Peter Schmatz
Publication date: April 2011
Product version: Microsoft Lync Server 2010
When Microsoft SQL Server is running in an Enterprise, you frequently find a number of security policies or other SQL Server best practices in place, for example:
- Unable to implement SQL Server instances that listen on the default SQL Server port of 1433.
- Running SQL Server Browser service for SQL instance discovery is not permitted.
- Specific SQL Server firewall exception rules must be implemented.
There are several options available that will enable you to successfully deploy Microsoft Lync Server 2010 communications software in such a SQL Server environment. This article describes how Lync Server 2010 components can access the SQL Server back-end databases, and what can be done to work around restrictions. This article will give an overview of the following topics:
- Descriptions of which Lync Server components require access to the SQL Server back-end databases.
- How to configure SQL Server firewall rules for a distributed Lync Server 2010 deployment.
- How to configure a SQL Server instance to listen on a custom TCP Port.
How to run Lync Server 2010 if the SQL Server Browser service is not permitted.
How Lync Server Uses SQL Server Databases
In previous releases, the setup was easier: The front-end servers connected to the SQL Server back-end databases, and the Monitoring and Archiving Servers connected to their respective SQL Server stores. Configuration data was stored in Active Directory Domain Services, SQL Server, and local Windows Management Instrumentation (WMI) and was accessed through a WMI provider that was installed on each server and administrator station.
With Lync Server, the SQL Server access is more complex: Configuration is now in the Central Management store and every setting must have access to the Central Management store. Other cmdlets, such as Set-CsClientPin, set user-specific runtime data. This user-specific data is stored in the SQL Server back-end databases of the Front End pool that homes the user.
Table 1 gives an overview of the tasks that require access to the SQL Server back-end database or Central Management store.
Table 1. Overview of configuration tasks and the database access required
Most Get -Cs/Set-Cs cmdlets
Central Management store
Move-CsUser and move conference IDs
Back-end database of source and destination pool of the moved object
Client personal identification number (PIN) cmdlets
Back-end database of the pool that homes the user
Lync Server 2010 Control Panel cmdlet Runspace
Central Management store and multiple back-end databases in the Front End pool
Cmdlets for local setup
Cmdlets for handling certificates
Local database only
Quality of Experience (QoE), call detail recording (CDR), and Archiving Server
SQL Server back-end stores for the Monitoring database or Archiving database
Note. While running the Lync Server Control Panel, the Web Services component (running on all Front End pools) creates a PowerShell Runspace that runs cmdlets. The cmdlets require database access. The client computer that is running the Web Browsers does not require database access.
Locked-Down SQL Server Environment
When you are running Lync Server in a locked-down SQL Server environment, the access to the SQL Server back-end databases and Central Management store is restricted to the least possible number of servers. The following list outlines the server options and the limitations of each option. To implement the access restrictions, use custom Firewall rules for SQL Server access.
- Access to the Central Management store only for the first Front End pool in the deployment (hosting the Central Management Services). This option has the following limitations:
- Only one of the Front End Servers in this pool can author and publish the topology document.
- All configuration tasks (either by using Lync Server Control Panel or Windows PowerShell cmdlets) must be run on one of the Front End Servers in this pool.
- Each Front End pool can access only its own SQL Server back-end. This option has the following limitation:
- Cmdlets handling a user PIN can only be run on the home pool for the effected user. The administrator has to determine from the user information which home pool the user is on.
- Initial deployment of Lync Server 2010 roles. This option has the following limitations:
- The option “Retrieve directly from Central Management Store” as part of the “Install Local Configuration Store” will not have access to the Central Management store. The workaround is to use the Edge deployment method where the initial configuration is done manually (first time only).
Subsequent configuration updates are replicated to each server and do not require access to the Central Management store.
SQL Server Configuration Scenarios
There are several ways to set up SQL Server networking, such as dynamic ports or named pipes. This article does not go into detail about all options, but focuses on two typical SQL Server network configuration scenarios. The SQL Server example FQDN used in this article is sql-ps1.cs.contoso.com, the named instance is RTCINST.
- Named SQL instance RTCINST is on custom port TCP 1414, and SQL Browser service is running.
- Named SQL instance RTCINST on custom port TCP 1414, and SQL Browser service is NOT running.
This article does not cover the scenario where the SQL default instance is not running on the default port 1433, because the SQL default instance does not use SQL Browser for discovery.
Scenario 1: SQL Browser Service Is Running
This is the default configuration. In this configuration the SQL Browser service is listening on port UDP 1434. Any SQL Server client (such as the Lync Server components) that connects to the SQL Server is using the connection string sql-ps1.cs.contoso.com\RTCINST when connecting to the SQL Server. The SQL Browser service returns the port 1414 to the client, and the client will connect successfully. No further settings on the client and Lync Server side are required.
Scenario 2: SQL Browser Service Is NOT Running
In this configuration the SQL Browser service is disabled, and the client does not know which port it must connect to get access to RTCINST. This setup reduces the attack surface of the SQL Server because the attacker would have to scan and probe all ports versus having the port number resolved by the SQL Browser service.
Note. Lync Server 2010 does not support setting up a custom port number when connecting to the SQL back-end; the connection strings contain only the SQL Server FQDN, and (in the case of a named instance) the instance identifier.
To enable Lync Server 2010 to run without the SQL Browser service, configure each SQL Server client individually by using the SQL Server Client Network Utility. Create a server alias that contains the port configuration as described later in this article.
SQL Firewall Exception Rules
By default, SQL Server does not create any firewall exceptions, and the administrator who sets up SQL Server must create these firewall exceptions manually.
There are the following options for creating these exceptions:
- Program-based exception rule: All inbound ports are open, but limited to specific programs.
- Port- or protocol-based exception rule: Specific single TCP or UDP ports are open, but are not program specific.
A best practice for SQL Server is to listen on a static port and use a port- or protocol-based firewall exception rule.
Note. If you are running SQL Server with dynamic ports, you must use the program-based exception rule. If you are running a static port, both exception rule types can be used.
To create a custom firewall rule for the SQL instance RTCINST listening on port TCP 1414, do the following:
1. On your SQL Server, run Windows Firewall with Advanced Security, select Inbound Rules, and then select New Rules.
2. Under Rule Type, select Custom, and then click Next.
3. In Program, select All Programs, and then click Next.
4. In Protocols and Ports, configure the following:
a. Protocol type: Select TCP
b. Local Port: Select Specific Port
c. Specify 1414
d. Remote ports: All Ports
5. Click Next.
6. In Select Scope, configure the following:
a. Local IP address: Any IP
b. Remote IP address: These IP Addresses
c. Specify a remote IP addressed individual, a specific subnet, or an IP address range
Note. This page lets you to select which remote computers can access the SQL Server back-end, for setting up each Lync Server components. To see what access is required, see Table 1.
7. Select Allow connection, and then click Next.
8. In Specify the Profile, select Domain, and then click Next.
9. Select a name for the rule you created, such as “SQL RTCINST CMS 1414.”
Repeat this procedure for SQL Browser service (if applicable). Change the settings for Protocol Type to UCP and Local Port to 1434.
Configuring SQL to Listen on a Specific TCP Port
To configure SQL Server to listen to a specific TCP port, do the following:
1. On your SQL Server, run SQL Configuration Manager.
2. In the pane, select Protocols for RTCINST and TCP/IP. (See Figure 1.)
Figure 1: SQL Server Configuration Manager Main dialog
3. Right-Click TCP/IP Properties and select Tab “IP Addresses“.
4. To disable dynamic ports, make sure the field “TCP Dynamic Ports” is blank for all available IP addresses. (See Figure 2.)
Figure 2: TCP/IP Properties TCP Dynamic Ports settings
5. To configure the TCP listening port globally for all configured IP Addresses, scroll to the bottom of the dialog. Enter the desired port 1414. (See Figure 3.)
Figure 3: TCP/IP Properties setting the TCP listening port
Note. Depending on your configuration, you may want to scope the access to a specific IP addresses.
6. To close the dialog box, click OK. A pop-up box will remind you that the SQL instance must be restarted for the changes to take effect.
7. Restart the SQL instance using SQL Server Management Studio or Service manager.
To validate the settings use the following check.
- To check which process is listening on 1414, start a Command Prompt and enter:
netstat -ano | findstr 1414
- Note the process/service name from the process ID:
taskslist /SVC | findstr <process ID>
- In this example the process/service name is:
Figure 4. Validating that SQL Instance RTCINST port change took effect.
Configuring a SQL Client Alias
To enable Lync Server 2010 to connect to a SQL Server back-end running on a SQL Server without SQL Browser service, create a SQL Client alias as described below. To create a SQL Client alias, the SQL instance must be listening on a static port. Support of dynamic ports requires the SQL Browser service.
To get the connection string by running Topology Builder, see the example below:
Figure 5. Topology Builder SQL Connection string
It is necessary to set up a client alias on each Lync Server system that requires access to this SQL back-end and any Lync remote admin stations running the Core Lync PowerShell provider. For the scenarios and tasks see Table 1.
Run “cliconfg.exe” to start the SQL Server Client Network Utility. The SQL Server Client is installed on all Lync Server systems, as well as Lync admin stations.
- On the main dialog select the “Alias” tab and select “Add”
Figure 6. SQL Server Client Network Utility main dialog
- Create a Server alias sql-ps1.cs.contoso.com\rtcinst with the Network type “TCP/IP“, and the connection parameters
- Server name: sql-ps1.cs.contoso.com
- Dynamically determine port (unchecked)
- Port Number: 1414
Figure 7. SQL Server Client Network Utility Server alias
- Click OK twice to close and apply
Manageability concerns of setting a SQL Server Alias individually
The obvious downside of creating an alias on each server is that the alias has to be applied to many servers, and the alias can get out of date. As a solution, it is possible to configure SQL Client aliases through GPO.
For details see: “How to deploy SQL Server Client Aliases using Active Directory GPO/ADM”.
This article discusses options to run Lync Server 2010 in a SQL Server deployment with custom requirements or constraints.
Keep in mind when setting up custom configuration to understand first which server or admin workstation has to have access possibly multiple SQL Server back-ends. Consider special cases:
- CMS database is moved from one SQL Backend to another.
- Moving users and conferences from one pool to the other.
Setting up a Help Desk remote admin station with a limited user role to reset User PINs.
Lync Server Resources
- Lync Server 2010 documentation in the TechNet Library
- DrRez blog
- Lync Server and Communications Server resources
We Want to Hear from You
Keywords: Lync Server 2010 SQL; custom SQL Port; SQL Browser; no SQL Browser, SQL Client; locked down SQL; SQL Firewall settings