Prepare the SQL Server to host the FIM CM database

  • Article

Applies To: Forefront Identity Manager Certificate Management

In order for the SQL Server to host the FIM CM database, it must be enabled to receive RPC and TCP/IP communications. In this procedure you ensure that the SQL Server is configured to allow those necessary communications.

Note

The procedures in this section would not be necessary if the SQL server and FIM CM server were located on the same computer. Since collocating the SQL Server and FIM CM server is not a typical configuration for most companies, this guide prepares you for configuring your FIM CM environment using separate servers.

There are two procedures to complete in this section:

  1. Enable Named Pipes and TCP/IP

  2. Allow TCP Port 1433 in the Windows Firewall

  3. Optional: Run the SQL Server service using a domain user account and register the appropriate SPN

Enable Named Pipes and TCP/IP

To allow communications to flow between the FIM CM server and the SQL Server, you must ensure both Named Pipes and TCP/IP are enabled on the SQL Server.

To enable Named Pipes and TCP/IP on the SQL Server

  1. Ensure you are using a user account that has SQL Server administrative permissions on the SQL Server and click Start.

  2. Start typing SQL Server Configuration Manager and then click SQL Server Configuration Manager when it appears on the Start menu. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Yes.

  3. In the console tree, double-click to expand SQL Server Configuration Manager (Local).

  4. Expand SQL Server Network Configuration and then select Protocols for MSSQLSERVER.

  5. In the details pane, you should see four protocols listed. Ensure that the status for Named Pipes and TCP/IP is Enabled. Named Pipes should be set to Disabled by default and TCP/IP is Enabled. To enable Named Pipes, complete the following steps:

    1. Double-click Named Pipes, which opens the Named Pipes Properties dialog box.

    2. Under General, set Enabled to Yes and then click OK.

    3. A Warning dialog box appears telling you that you must restart the SQL Server service in order for the changes to take effect. Click OK.

      Important

      If TCP/IP is not enabled, then enable it using the same technique that you just used to enable Named Pipes.

  6. Close the SQL Server Configuration Manager console.

Allow TCP Port 1433 in the Windows Firewall

By default, the Windows Firewall prevents access over the default network communication port for SQL. In this procedure, you will enable communications over TCP 1433, to support communications among the servers that comprise your FIM CM environment.

To allow TCP port 1433

  1. Ensure you are using a user account that has SQL Server administrative permissions on the SQL Server and click Start.

  2. Start typing Windows Firewall with Advanced Security and then click Windows Firewall with Advanced Security when it appears on the Start menu.

  3. In the console tree, click Inbound Rules.

  4. In the Actions pane, click New Rule.

  5. In New Inbound Rule Wizard, on the Rule Type page, select Port and then click Next.

  6. On the Protocol and Ports page, ensure TCP is selected. In Specific local ports, type 1433 and then click Next.

  7. On the Action page, ensure that Allow the connection is selected and then click Next.

  8. On the Profile page, ensure that Domain remains selected, and then clear the Private and Public checkboxes, as they do not apply in the for this lab scenario.

  9. On the Name page, in Name, type SQL Server Connection Port. In Description type Allows servers in the domain to communicate with the SQL Server using TCP port 1433, which is the default port for communicating with a SQL Server.

  10. Close the Windows Firewall with Advanced Security console.

  11. Open an elevated Command Prompt window. To do so, click Start and then type Command Prompt. Right-click Command Prompt when it appears on the Start menu and then click Run as administrator. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Yes. Click Start, type Command Prompt. Right-click Command Prompt and the click Run as administrator when it appears on the Start menu.

  12. Restart the SQL Server service. To do so, in the command prompt, type net stop mssqlserver && net start mssqlserver and then press ENTER.

  13. Ensure the SQL Service stops and starts and then close the elevated command prompt window.

Optional: Run the SQL Server service using a domain user account and register the appropriate SPN

Running the SQL Server service using the local system account of the SQL Server computer is not a SQL Server best practice. For the most secure operation of SQL Server site database servers, a low rights domain user account should be configured to run the SQL Server service.

A Service Principal Name (SPN) must be registered for the SQL Server service account (when the local system account will not be used) to allow clients to identify and authenticate the service using Kerberos authentication. The SetSPN utility can be used to register an SPN for the site database server SQL Server service account. The SetSPN utility must be run on a computer that resides in the SQL Server's domain and it must be run using Domain Administrator credentials. To properly configure an SPN for the SQL Server service account using the SetSPN utility, follow the steps in these procedures. You must first create and designate an account in AD DS for running the SQL Server service.

Note

For additional information on configuring a user account to run the SQL Server service, see Managing Services How-to Topics (SQL Server Configuration Manager) (https://go.microsoft.com/fwlink/?LinkId=205879)

To manually create a domain user Service Principle Name (SPN) for the SQL Server service account

  1. Open an elevated Command Prompt window. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Yes.

  2. Create the SPN, using the Setspn command. The command format is setspn –A MSSQLSvc/<SQL Server computer name>:1433 <domain\user>. For example, if you were configuring a domain user account from corp.contoso.com with the user name sqlacct and the SQL Server computer name was SQL1, then you would type setspn -U -A MSSQLSvc/SQL1:1433 corp\sqlacct and then press ENTER.

  3. The command will verify the change. You can also use setspn to query the SPNs configured for an account. The command format is setspn -U -L <domain\user>. From the previous example, you could type setspn -U -L corp\sqlacct and then press ENTER. This command output shows the SPNs registered for that user account.

  4. You should also ensure that your SQL Server account is configured to start using the domain user account. For detailed steps on configuring SQL Server 2008 startup account, see How to: Change the Service Startup Account for SQL Server (SQL Server Configuration Manager).

Previous topic

Preparing for FIM CM Server Installation

Next topic

Prepare the FIM CM Agent Certificate Templates

See Also

Concepts

Installing and Configuring FIM CM Infrastructure

Other Resources

FIM CM 2010 FAQ
FIM User Forum