Installing and configuring SQL Server for SQL Server authentication

You typically install Microsoft SQL Server and Content Manager OnDemand on the same system. The database that is used by the Content Manager OnDemand instance is created locally and is on the local file system.

Before you begin

Although it is not preferred, it is possible to run the Content Manager OnDemand server against a remote SQL Server database. In this case, the database is on a different server than the one on which the Microsoft SQL Server is installed.

Procedure

Note that these instructions apply to SQL Server 2017, 2019, and 2022. SQL Server Installation wizard panels might change in future versions.

To install Microsoft SQL Server on the library server:

  1. Click setup.exe to start the SQL Server Installation Center.
  2. Select Installation on the left pane.
  3. Select New SQL Server standalone installation or add features to an existing installation.
    Make sure that there are no failures on the Setup wizard panels.
  4. On the Setup Role pane, select SQL Server Feature Installation.
  5. On the Feature Selection screen, select Database Engine Services and Client Tools Connectivity. Note that SQL Server 2022 does not include or require Client Tools Connectivity.
    Make sure that there are no failures on the Installation wizard panels.
  6. On the Instance Configuration pane, select Default instance.
  7. On the Database Engine Configuration pane, select Mixed Mode (SQL Server authentication and Windows authentication) and specify a password for the Microsoft SQL Server admin ID named sa.
    The Mixed Mode authentication is needed to create a new SQL Server user ID later to be used by the Content Manager OnDemand instance owner.
  8. Click Add Current User to also add the logged on Windows user ID to the Microsoft SQL Server and click Next.
    Make sure that there are no failures on the Installation Configuration Rules pane.
  9. When the Microsoft SQL Server installation completes, make sure that there are no errors, close the SQL Server Installation Center, and restart the server, if needed.
  10. From the SQL Server Installation Center, select Install SQL Server Management Tools to access the download link for SQL Server Management Studio (SSMS).
  11. Download and install SSMS.
  12. Start the Microsoft SQL Server Management Studio and connect to Database Engine.
  13. Click Security and expand Logins on the left pane.
    The Windows user ID used to install Microsoft SQL Server is listed but it is in the form of <domainname>\user_name. You need to create an SQL Server login without the host name or domain name.
  14. Right-click Logins and select New Login...
  15. Enter the name of an existing Windows user ID with administrator rights, such as odadmin.
    This name is the user ID that is used to create the Content Manager OnDemand instance.
  16. Select SQL Server Authentication and enter a password for the new Microsoft SQL Server user ID.
  17. Accept the default database name master.
  18. On the Server Roles page, make sure that the dbcreator checkbox is checked.
  19. Click OK to create the new login.

What to do next

Before you can create the Content Manager OnDemand instance with Microsoft SQL Server, you must verify that a correct version of the regasm.exe file exists. Content Manager OnDemand is released with an SMO (SQL Server Management Objects) DLL that needs to be registered by regasm at installation time. Content Manager OnDemand V10.5 uses a 64-bit v4.0.30319 RegAsm.exe file.

A v4.0.30319 RegAsm.exe file is available on Windows Server 2012 and later.

On systems without a v4.0.30319 RegAsm.exe file, you can install .NET Framework 4 or 4.5. If .NET Framework 4 is not installed, run the Windows Update to install it. To find out which version of the regasm.exe file is installed, run the following commands from a Windows command prompt:

C:\>CD C:\Windows\Microsoft.NET

C:\Windows\Microsoft.NET>DIR regasm.exe /s/b

It should display the following results:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe
      C:\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe
If Content Manager OnDemand V10.5 is installed without a 64-bit v4.0.30319 RegAsm.exe file, the Content Manager OnDemand SMO DLL cannot be registered. Any attempt to run the arsdb command to create an SQL Server database might result in a system crash. Make sure that you have a 64-bit v4.0.30319 RegAsm.exe file before you install Content Manager OnDemand V10.5.
  • If Content Manager OnDemand V10.5 is installed without a 64-bit v4.0.30319 RegAsm.exe file, install .NET Framework 4.5.1 manually or by using the Windows Update.
  • After you verify that you have a 64-bit v4.0.30319 RegAsm.exe file, reinstall Content Manager OnDemand V10.5.
  • Alternatively, you can apply the most recent Content Manager OnDemand V10.5 fix pack if one exists, such as 10.5.0.x, to register Content Manager OnDemand SMO during the installation.
To verify whether Content Manager OnDemand SMO is successfully registered, look for the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{ECC6E2B2-9901-4A26-
AEF0-085507A375EF}\v.r
where v.r should be a.5 for Content Manager OnDemand V10.5, for example. In the example, the lowercase 'a' is the hexadecimal equivalent of the decimal number '10'.
If you want to enable the SQL Server authentication only for your Database Engine connection, perform the following steps:
  1. Start the OnDemand Configurator.
  2. Click the '+' beside the Content Manager OnDemand instance that uses SQL Server.
  3. Click Services.
  4. Right-click OnDemand Libsrvr (instance_name).
  5. Select Stop to stop the Content Manager OnDemand server.
  6. Start the Microsoft SQL Server Management Studio.
  7. Connect to the Database Engine.
  8. Find the SQL Server user, such as odadmin. Right-click Properties and set the password.
  9. Disconnect from the Database Engine and reconnect using the SQL Server ID, such as odadmin. Change the Authentication to be SQL Server, and specify the new password. Select Connect.
  10. If that connection is successful, then locate and run ODBC Data Sources (64-bit) on the System DSN tab and select your SQL Server DSN. Select Configure.
  11. On the Microsoft SQL Server DSN Configuration pane, change the How should SQL Server verify the authenticity of login ID to be With SQL Server authentication using a login ID and password entered by the user. Enter the SQL Server user ID and password and click Next.
  12. Continue to click Next on each pane until the last one. Click Finish. On the ODBC Microsoft SQL Server Setup pane, click Test Data Source to test the Data Source connection.
  13. If the test of the connection is successful, return to the OnDemand Configurator. Select your Content Manager OnDemand SQL Server instance and right-click Properties. Select the Server tab, click the Communications button, and make sure that a stash file is specified.
  14. On the Database page for the instance, enter the SQL user ID and password under the Database Login section.
  15. As an alternative, instead of using the OnDemand Configurator in the two previous steps, you can bring up the OnDemand Command Prompt and run the arsstash -a 12 -s <SQL stashfile> -u <SQL Login ID> command to add the SQL Server Login to the stash file. Use the -c option if the stash file does not exist. If you perform this step, ensure the stash file that is used by the arsstash command is the same as the file name specified on the Server tab, Communications pane of the Content Manager OnDemand instance.
  16. Use the OnDemand Configurator to start the Content Manager OnDemand server.
Following these steps allows Content Manager OnDemand administrators to set up their Content Manager OnDemand SQL Server instance to use only an SQL Server login ID without having to create a Windows ID. This approach is especially helpful if the SQL Server is remote to the Content Manager OnDemand server.