UIM database authentication SQL user and permissions requirements
Article ID: 280937
Updated On:
Products
Issue/Introduction
This article provides guidance for the following:
- Database server authentication
- Users and permission requirements
- data_engine authentication
- UIM DB authentication options
- MS SQL Server
- How to switch to to using Windows authentication
Environment
- UIM 20.4 or higher
- data_engine
Cause
- Guidance
Resolution
Microsoft SQL Server Enterprise Edition (most common)
data_engine supports authentication to SQL Server using either Mixed mode (SQL Server and Windows Auth), or just Windows Authentication.
Requirements for SQL Server Authentication on Microsoft SQL Server
- If you are not using the actual System Administrator(sa) login and require SQL Authentication on MS SQL Server, your ‘user’ account must have the following permissions:
- The db_owner database role for the UIM/NimsoftSLM database
- Read and update permissions on the master and tmpdb system databases
- The serveradmin database role to create and execute stored procedures properly
MS SQL Server Authentication modes
Mixed Mode
- Enables both Windows Authentication AND SQL Server Authentication.
- Windows Authentication is always available, and cannot be disabled.
- sa account connects by using SQL Server Authentication.
Windows Authentication mode
- Enables Windows Authentication and disables SQL Server Authentication.
- Its setup creates the ‘sa’ account for SQL Server Authentication but it is disabled.
- If you later change to Mixed Mode Authentication and you want to use the sa account, you must enable the account.
Most DBAs prefer to avoid using the sysadmin (sa) account.
Note that ANY Windows or SQL Server account can be configured as a system administrator so they can use another account.
Windows Authentication requirements
Before installing UIM, customers must:
- Add a domain administrator with permission to 'Log on as a Service' on both the UIM system and the database system.
- For instructions, please refer to:
Configure the MS SQL server to use Windows Authentication
- For instructions, go to: http://msdn.microsoft.com/en-us/library/aa337562.aspx
- Note: The user installing UIM must have the same administrative rights as those used to install the SQL Server, and supply those credentials during installation.
- Specifically, the data_engine probe must have identical administrative rights on both the UIM system and the Database Server machine.
Post-installation requirements for Windows Authentication
- The 'Nimsoft Robot Watcher' NT service Logon User will be the same as used during UIM Server installation.
- This service starts the robot and thus the new user needs to have full permissions to the UIM Server installation folder and logon privileges to the SQL Server.
- We caution against changing the logon user value which could result in the robot either not being able to start or unable to function normally due to file permission issues.
- If a change is made, be sure the login user is specified in the <domain>\<nt_username> format so that the user has full permissions to the contents of the installation directory.
Steps required for the data_engine to use Windows Authentication
- Add the "Integrated Security=SSPI" parameter to the data_engine connection parameters
- Example of parameters used for Windows Authentication-> Network Library=dbmssocn;Language=us_english;Integrated Security=SSPI
- Set the Windows Auth credentials in the data_engine connection.
- Set the ‘Nimsoft Robot Watcher service’ to use the Windows Auth credentials.
- Stop and start the Nimsoft Robot Watcher service.
- If you need to ‘switch’ from SQL Auth to Windows Authentication, please refer to the Knowledge Article link below for all details:
How to switch from Microsoft SQL Server Authentication to Windows/Integrated Authentication
Additional Information
- UIM supports authentication for 3 different types of databases: MS SQL Server, Oracle & MySQL
- If you are changing database vendors, we recommend reinstalling UIM.
Feedback
