Database Collation and Connection Failures During Setup
An engineer is configuring the Blue Prism database on a SQL Server instance provisioned by a central DBA team. During the configuration wizard the connection test succeeds, but the schema creation step fails partway through with an error referencing an invalid object name, and a second attempt reports that some objects already exist. The DBA confirms the instance uses a case-sensitive collation because a legacy application on the same instance requires it. The engineer's login is a sysadmin on the instance. The database was created with default settings and the engineer does not know whether the failed run left partial objects behind. Separately, the application server's service account can connect to the instance but receives a login failure when the Blue Prism service starts, even though the same account works interactively from the server.
1. Diagnose the schema creation failure and describe a safe recovery path that does not leave the environment in a half-built state.
Read the worked answer
Two distinct problems are in play.
Collation. A case-sensitive collation on the database is a known incompatibility for the Blue Prism schema, which relies on case-insensitive comparisons in places. The connection test passing proves nothing about collation - it only proves network and credentials. The correct fix is to create a dedicated database with a case-insensitive collation rather than changing the instance collation, which would break the legacy application the DBA is protecting. Confirm the intended collation with the vendor's installation guidance and apply it at database creation, not afterwards.
Partial state. A failed schema run can leave objects behind, which is why the second attempt reports existing objects. Do not retry blindly. The safe path is:
- Take a backup or confirm the database is disposable (it should be, pre-go-live).
- Drop and recreate the database with the correct collation.
- Re-run the configuration wizard once, cleanly.
- Verify object counts and a test login before handing to the application tier.
Retrying over a partial schema produces misleading errors and can mask a second, unrelated failure.
2. Explain why the service account authenticates interactively but fails when the Blue Prism service starts, and what to check.
Read the worked answer
Interactive success only proves the account's password is valid and it can reach the instance from that machine. The service runs under a different context:
- Logon rights. The account needs the right to log on as a service on the application server. Without it the service cannot start under that identity at all, and the error surfaces as a logon failure.
- Database user mapping. The login may exist on the instance but lack a user mapped in the Blue Prism database, or lack the role membership the application requires. Interactive tests often use a different login than the service.
- Credential staleness. If the password was changed and the service was configured with the old one, the service fails while an interactive logon with the new password succeeds.
- Protocol and encryption. If the service connection string enforces encryption and the certificate is not trusted by the service account's profile, the connection fails only in the service context.
Check the Windows service logon account, the SQL login-to-user mapping, role membership, and the exact connection string the service uses. Test with the same identity and the same string the service will use, not an interactive tool.