Microsoft SQL Server Configurations

Most IBM® AD components connect to an Microsoft SQL Server by using SQL Authentication. For Microsoft SQL Server 2012/2014/2016/2017, perform the following configurations:
  1. Make sure that Microsoft SQL Server instance is configured with a case-insensitive (CI) collation.
    Important: IBM Application Discovery does not support the Microsoft SQL Server instance's collation Latin1_General_CP1254_CI_AS.
  2. Make sure Microsoft SQL Server Agent service is started.
  3. Setting up an SQL user account:
    1. Start SQL Server Management Studio.
    2. Expand Security > Logins then right-click Logins and choose New Login. Add a login name, select SQL Server Authentication, add a password, and make sure that the default database is set to master. This user is referred to as IBM AD SQL Identity.
    3. Expand Databases > System Databases > master > Security > Users then right-click Users and choose New User. Choose the IBM AD SQL Identity user and click OK.
    4. Expand Databases > System Databases > master then right-click master and choose Properties. Go to Permissions tab and for the IBM AD SQL Identity and make sure that permissions are granted for: Create database, Create function, Create procedure, Create table, and Create view.
    5. The following permission must be granted only if the Rename project feature is used in IBM AD Build, otherwise it is not needed. Right-click on the SQL server instance and then select Properties. In the Server Properties window select Permissions: From the roles list, select the IBM AD SQL Identity and then select Grant for Alter any database permission.
  4. Configuring SQL Server to enable it to accept connection over TCP/IP:
    1. Start SQL Server Configuration Manager.
    2. Select SQL Native Client Configuration (32bit) > Client Protocols and then right-click TCP/IP and set it to Enabled.
    3. Select SQL Server Network Configuration > Protocols for <Instance ID> and make sure that Shared Memory and TCP/IP are set to Enabled.
    4. Select SQL Native Client Configuration > Client Protocols and make sure that Shared Memory and TCP/IP are set to Enabled.
    5. Close SQL Server Configuration Manager.
    Important: SQL Server Configuration Manager writes startup parameters to the registry. They take effect upon the next startup of the SQL Server.