How do I ensure the SQL 'Insol2Users' login is set up correctly? Login failed for user ‘Insol2users’

Sometimes when you attempt to connect a workstation to a database, you may receive a message that ‘Insol2Users’ cannot log in.

The connection could not be made for the following reasons:
Login failed for user ‘Insol2users’.

This may indicate that Likely mixed mode is not turned on.

Mixed mode authentication

Ensure you have implemented mixed mode authentication on the SQL Server. check your SQL Server configuration.

The options are Windows mode (a windows user with rights can login), SQL Server user mode (a SQL user is able to login), and Mixed mode (both Windows and SQL users can login).

Below shows the error on start up

Cannot open database "Insol2" requested by the login. The login failed.

This indicates a likelihood that the user mapping is not correct.

To configure login and user mapping

Follow these steps to ensure the 'Insol2Users' logon is correctly configured.  This log in issue may occur where you have previous or other database attached to the SQL Server.

The Insolvency workstation uses a database user login (Insol2Users) to make a connection to the Insolvency database. This login is a member of the application role Insol2_Users. The role has been granted permissions on objects such as stored procedures, queries and functions that allow users to access the data via commands sent from the user interface.

When trying to connect the database you may receive a connection warning ‘Insol2user’ cannot login.

To make sure you have the users and roles set up correctly, on the database server open the SQL Server Management Studio and make sure the ‘Insol2user’ is set up correctly.

Database mapping

Likely to apply when relocating a database form one server to another.

Follow these steps if you cannot see the Login ‘Insol2Users’ in the list of logins in Server\Security\Logins and you receive a warning message “The login ‘Insol2Users’ already exists in the database ‘Insol2’” when you’re trying to add this login.

Step 1. Delete the database user

Open the Database\Security\User user node

The database is usually called Insol2

1. Expand the Object Explorer tree in SQL SMS, as follows:
   
   Server\Database [usually Insol2] \Security\Users\
   
   
   
   A list of users is displayed including the login ‘Insol2Users’.
   
   
2. Right click on the logon and select Delete
   
   The ‘Delete Object’ dialogue appears. If you are prompted "There is a schema that has the same name as the user you are deleting. Do you want to delete the schema?" Click "Yes".
   
   
3. Click OK
   
   The user login is now deleted.
   
   

Step 2. Re-Add the Server-Level Login mappings. 

Open the Server\Security\Logins node

Expand the Object Explorer tree in SQL SMS, as follows:



A list of users is displayed including the login ‘Insol2Users’.  If the login is not present, please contact support for information on how to add the login as a password is required.

Step 3. Database User Mapping

1. Double click on the login Insol2Users
   
   The ‘Database User’ properties dialogue appears.
   
   
2. Click on ‘User Mapping’ from the ‘Select a page’ options
   
   The database mapping appears.
   
   
   
   
3. Map the login to the database
   
   Tick the ‘Map’ tick box next to the ‘Insol2’ database name (this is the default database name).
   
   Tick the ‘insol2_users’ role in the ‘Database role for membership’ list.
   
   
4. Click OK
   
   the Login is now re-mapped back to the database.
   
   Note: You would not have been able to do this until following step 1. Delete the database user
   

Step 4. Test

1. Check the ‘Insol2_User’ role is correctly mapped to the user login ‘Insol2Users’
   
   
1. Expand the Object Explorer tree in SQL SMS, as follows:
   
   Server\Database\Security\Roles\Database Roles\
   
   A list of roles displays including the role ‘Insol2_Users’.
   
   
2. Double click on the role Insol2_Users
   
   The ‘Database Role’ properties dialogue appears.
   
   
3. On the ‘General’ page the ‘Role Members’ are listed.
   
   The role members should include the login ‘Insol2Users’. If this login does not appear click ‘Add’.
   
   The ‘Select Database User or Role’ dialogue appears.
   
   
4. Enter the object name as ‘Insol2Users’ and click OK
   
   Click OK to select the ‘Insol2Users’ login.
   
   The login ‘Insol2Users’ is now a member of the role ‘Insol2_Users’.
   
   
2. Try to reconnect the workstation using the ‘Insol2User’ login
   
   Separate instructions are provided to enter the connection properties for a workstation:

https://exalt.zendesk.com/hc/en-us/articles/229327907-How-to-re-connect-a-workstation-to-a-relocated-database