2 Configure Microsoft SQL Server for Authentication

This chapter provides the instructions for configuring Microsoft SQL Server for authentication for access through Oracle Enterprise Manager Cloud Control. Starting first with enabling and finding TCP/IP port information, the chapter ends with a set of authentication configuration scenarios that you can modify for your own environment.

The following topics are provided:

• Enabling and Finding TCP/IP Port Information

• Modifying the Permissions for Database Authentication

• Enabling SQL Authentication or Mixed Authentication

• Authentication Configuration Scenarios

2.1 Enabling and Finding TCP/IP Port Information

The following sections provide information you require to enable the TCP/IP port and to find the TCP/IP port for a particular SQL server instance:

• Enabling TCP/IP Port

• Finding the TCP/IP Port

2.1.1 Enabling TCP/IP Port

1. From the SQL Server Configuration Manager, select your appropriate SQL Server Network Configuration in the left panel and navigate to the SQL Server instance.

   The right panel displays all protocols for the specified SQL Server instance and their status.

2. Ensure that TCP/IP is enabled.

3. If TCP/IP is disabled, right-click TCP/IP and select Properties. The TCP/IP Properties dialog box appears.

4. In the Protocol tab, select enabled, and click Apply.

5. Restart the SQL Server instance.

2.1.2 Finding the TCP/IP Port

After enabling the TCP/IP protocol, restart the SQL Server to apply the changes.

From the SQL Server Configuration Manager, select the appropriate SQL Server Network Configuration in the left panel and navigate to the SQL Server instance:

The right panel displays all protocols for the specified SQL Server instance and their status.

In the IP Addresses tab, TCP Dynamic Ports row of IP All will give the TCP/IP port of instance.

2.2 Modifying the Permissions for Database Authentication

Modify the permissions for database authentication so that you enable SQL authentication or Windows authentication, and set sysadmin role for the database user that you are going to use for discovering the target and running jobs.

On the SQL Server, for the user you are going to use for monitoring and running jobs, set the write permissions by following these steps:

Note:

If you do not have a user for Windows Authentication, then create one. To do so, from the task bar, go to Start, select Settings, and then Control Panel. In the Control Panel, double-click Users and Passwords and click Add in the Users tab.

1. Log in to the Microsoft SQL Server Management Studio with a predefined user account, or if one was not setup for SQL authentication, use Windows Authentication (Figure 2-1):

   Figure 2-1 Log In to Microsoft SQL Server

   
   

2. Right-click Logins and select New Login… (Figure 2-2):

   Figure 2-2 New Login Menu

   
   

3. Select either Windows authentication and select a predefined user, or select SQL Server authentication to specify a new user (Figure 2-3):

   Figure 2-3 Select User

   
   

4. Under the Server Roles page, click the check box for the sysadmin server role (Figure 2-4):

   Figure 2-4 Select sysadmin Server Role

   
   

5. Click OK.

2.3 Enabling SQL Authentication or Mixed Authentication

1. Log in to the Microsoft SQL Server Management Studio with a predefined user account, or if one was not set up for SQL authentication, use Windows Authentication.

2. Right-click the server you wish to modify and then click Properties.

3. Select the Security Page.

4. Under the Server authentication heading choose either the desired authentication: Windows Authentication or SQL Server and Windows Authentication mode.

5. Click OK.

6. At this point the SQL server must be restarted. To do so, right-click the server you have just modified and select Restart.

7. If SQL Server Agent is running, it must also be restarted.

2.4 Authentication Configuration Scenarios

The examples listed below describe supported configuration details for Microsoft SQL Server. Follow the examples and choose the configuration options best suited for your environment.

• Local monitoring with SQL Authentication

• Local monitoring with Windows Integrated Authentication (WIA)

• Remote monitoring with SQL Authentication

• Remote monitoring with Windows Integrated Authentication (WIA)

• Cluster remote monitoring with SQL Authentication

• Cluster remote monitoring with Windows Integrated Authentication

Note:

Before proceeding with target discovery, manually verify the authentication mode used by manually logging in to the target SQL Server's management tool or request WIA/SQL Authentication credentials from the SQL Server administrator.

Example 2-1 Local monitoring with SQL Authentication

EM Agent                                    : MACHINE_1
JDBC URL                                    : jdbc:sqlserver://MACHINE_1:<PORT>
Database Username                           : Database_Username
Password of Database User                   : Database_Password
System Password                             : <BLANK>
System Username                             : <BLANK>
Connect Using WIA (Yes/No)                  : No

Database_Username can manually log in to the SQL Server management tool and be granted Sysadmin or correct SQL Server privileges.

Windows OS user is configured to run the Enterprise Manager Agent service and is granted advanced privileges.

Example 2-2 Local monitoring with Windows Integrated Authentication (WIA)

EM Agent                                    : MACHINE_1
JDBC URL                                    : jdbc:sqlserver://MACHINE_1:<PORT>
Database Username                           : <BLANK>
Password of Database User                   : <BLANK>
System Password                             : <BLANK>
System Username                             : <BLANK>
Connect Using WIA (Yes/No)                  : Yes

Windows OS User can manually log in to the SQL Server management tool using WIA, is configured to run Enterprise Manager Agent service, is granted advanced privileges, and is granted Sysadmin or correct SQL Server privileges.

Example 2-3 Remote monitoring with SQL Authentication

EM Agent                                  : MACHINE_1
JDBC URL                                  : jdbc:sqlserver://MACHINE_REMOTE:<PORT>
Database Username                         : Database_Username  
Password of Database User                 : Database_Password
System Username                           : REMOTE_Windows_OS_User
System Password                           : REMOTE_Windows_OS_Password 
Connect Using WIA (Yes/No)                : No

Sysadmin or SQL Server privileges are granted to the Database_Username.

Advanced privileges granted to REMOTE_Windows_OS_User within the SQL Server host machine, and can log in to SQL Server host machine.

Example 2-4 Remote monitoring with Windows Integrated Authentication (WIA)

EM Agent                                  : MACHINE_1
JDBC URL                                  : jdbc:sqlserver://MACHINE_REMOTE:<PORT>
Database Username                         : <BLANK>
Password of Database User                 : <BLANK>
System Username                           : REMOTE_Windows_OS_User
System Password                           : REMOTE_Windows_OS_Password
Connect Using WIA (Yes/No)                : Yes

REMOTE_Windows_OS_User can log in to SQL Server host machine, can manually login to the SQL Server management tool using WIA, granted advanced privileges, granted Sysadmin or correct SQL Server privileges, and must be a Windows Domain account with access to OMA host and target database.

The host with the Oracle Management Agent (OMA) must be a member of the same Windows domain as the SQL Server host.

Example 2-5 Cluster remote monitoring with SQL Authentication

Cluster  = SQLServer_Cluster_Hostname
Nodes    = Node1_Hostname
           Node2_Hostname
           etc..

EM Agent                      : ANY MACHINE
JDBC URL                      : jdbc:sqlserver://SQLServer_Cluster_Hostname:<PORT>
Database Username             : Database_Username  
Password of Database User     : Database_Password
System Username               : REMOTE_Windows_OS_User
System Password              : REMOTE_Windows_OS_Password
Connect Using WIA (Yes/No)   : No

Sysadmin or SQL Server privileges are granted to the Database_Username.

REMOTE_Windows_OS_User granted advanced privileges within the SQL Server nodes and can log in to SQL Server cluster hostname. Test the login by using Windows Remote Desktop.

SQLServer_Cluster_Hostname is virtual hostname or IP of the SQL Server Clustered Service and not the Windows Cluster Hostname.

Example 2-6 Cluster remote monitoring with Windows Integrated Authentication

Cluster  = SQLServer_Cluster_Hostname
Nodes    = Node1_Hostname
           Node2_Hostname
           etc..

EM Agent                      : ANY MACHINE
JDBC URL                      : jdbc:sqlserver://SQLServer_Cluster_Hostname:<PORT>
Database Username             : <BLANK>
Password of Database User     : <BLANK>
System Username               : REMOTE_Windows_OS_User
System Password               : REMOTE_Windows_OS_Password
Connect Using WIA (Yes/No)    : Yes

REMOTE_Windows_OS_User can log in to SQL Server cluster hostname, can manually login to the SQL Server management tool using WIA, granted advanced privileges, granted Sysadmin or correct SQL Server privileges, and must be a Windows Domain account with access to OMA host and target database.

SQLServer_Cluster_Hostname is virtual hostname or IP of the SQL Server Clustered Service and not the Windows Cluster Hostname.

Note:

Where a User account requires Advanced Privileges, this includes the following Operation System rights:

• Act as part of the operating system.

• Adjust memory quotas for a process.

• Log on as batch job.

• Replace a process-level token.

The host with the Oracle Management Agent (OMA) must be a member of the same windows domain as the SQL Server host.