Configure Microsoft SQL Server for Authentication

5/13

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 base with enabling and finding TCP/IP port information, the chapter ends with a put of authentication configuration scenarios that you can modify for your own environment .
The follow topics are provided :

2.1 Enabling and Finding TCP/IP Port Information

The follow sections provide information you require to enable the TCP/IP larboard and to find the TCP/IP port for a detail SQL server exemplify :

  • 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 allow SQL Server Network Configuration in the leave empanel and navigate to the SQL Server case .
    The proper panel displays all protocols for the stipulate SQL Server example and their condition .
  2. guarantee that TCP/IP is enabled .
  3. If TCP/IP is disabled, right-click TCP/IP and blue-ribbon Properties. The TCP/IP Properties dialogue box appears .
  4. In the Protocol tab key, blue-ribbon enabled, and snap Apply .
  5. Restart the SQL Server exemplify .

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 allow SQL Server Network Configuration in the leftover empanel and navigate to the SQL Server example :
The correct control panel displays all protocols for the specify SQL Server example and their status .
In the IP Addresses yellow journalism, TCP Dynamic Ports row of IP All will give the TCP/IP port of exemplify .

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 function for the database exploiter that you are going to use for discovering the target and running jobs .
On the SQL Server, for the exploiter 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 startle, select Settings, and then Control Panel. In the Control Panel, double-click Users and Passwords and click Add in the Users tab. If you do not have a exploiter for Windows Authentication, then create one. To do thus, from the task banish, go to, select, and then. In the Control Panel, double-clickand clickin 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 ) :
    calculate 2-1 Log In to Microsoft SQL Server
    Surrounding text describes Figure 2-1 .
  2. Right-click Logins and choose New Login… ( Figure 2-2 ) :
    Figure 2-2 New Login Menu
    Surrounding text describes Figure 2-2 .
  3. Select either Windows authentication and select a predefined drug user, or choice SQL Server authentication to specify a new user ( Figure 2-3 ) :
    figure 2-3 Select User
    Surrounding text describes Figure 2-3 .
  4. Under the Server Roles page, click the check mark box for the sysadmin server function ( Figure 2-4 ) :
    figure 2-4 Select sysadmin Server Role
    Surrounding text describes Figure 2-4 .
  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 report, 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 hope authentication : Windows Authentication or SQL Server and Windows Authentication mode.

  5. Click OK .
  6. At this point the SQL waiter must be restarted. To do sol, right-click the waiter you have just modified and choose Restart .
  7. If SQL Server Agent is running, it must besides be restarted .

2.4 Authentication Configuration Scenarios

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

  • local monitor with SQL Authentication
  • local monitoring with Windows Integrated Authentication ( WIA )
  • outside monitoring with SQL Authentication
  • distant monitor 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. Before proceeding with aim discovery, manually verify the authentication modality 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 monitor with SQL Authentication

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

Database_Username can manually log in to the SQL Server management cock and be granted Sysadmin or correct SQL Server privileges .
Windows OS drug user is configured to run the Enterprise Manager Agent serve and is granted advanced privileges .
Example 2-2 Local monitor with Windows Integrated Authentication ( WIA )

EM Agent                                    : MACHINE_1
JDBC URL                                    : jdbc:sqlserver://MACHINE_1:
Database Username                           : 
Password of Database User                   : 
System Password                             : 
System Username                             : 
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 serve, is granted advance privileges, and is granted Sysadmin or correct SQL Server privileges .
case 2-3 Remote monitoring with SQL Authentication

EM Agent                                  : MACHINE_1
JDBC URL                                  : jdbc:sqlserver://MACHINE_REMOTE:
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 horde car .
exemplar 2-4 Remote monitor with Windows Integrated Authentication ( WIA )

EM Agent                                  : MACHINE_1
JDBC URL                                  : jdbc:sqlserver://MACHINE_REMOTE:
Database Username                         : 
Password of Database User                 : 
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 server machine, can manually login to the SQL Server management creature using WIA, granted advanced privileges, granted Sysadmin or decline SQL Server privileges, and must be a Windows Domain history with access to OMA host and prey database .
The host with the Oracle Management Agent ( OMA ) must be a member of the lapp Windows domain as the SQL Server horde .
case 2-5 Cluster distant 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:
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 promote 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 .
exemplar 2-6 Cluster outside monitor 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:
Database Username             : 
Password of Database User     : 
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 score with entree 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 .
eminence :
Where a User account requires Advanced Privileges, this includes the following Operation System rights:

Where a User score requires Advanced Privileges, this includes the come Operation System rights : The server with the Oracle Management Agent ( OMA ) must be a member of the like windows sphere as the SQL Server host .

generator : https://thefartiste.com
Category : Tech

About admin

I am the owner of the website thefartiste.com, my purpose is to bring all the most useful information to users.

Check Also

articlewriting1

Manage participants in a zoom meeting webinar

Call the people who attend the meet as follows Alternate host host Who scheduled the …

Leave a Reply

Your email address will not be published.