Bamboo throws SQL exception "Cannot find the object because it does not exist or you do not have permissions" during startup
プラットフォームについて: Data Center のみ - この記事は、Data Center プラットフォームのアトラシアン製品にのみ適用されます。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
要約
This article covers a scenario where Bamboo server was not able to start and complained about missing objects or missing permissions on the Database objects.
環境
The issue and solution was tested on Bamboo 9.2.7 and Microsoft SQL Server 2019.
診断
Looking at <bamboo-home>logs>atlassian-bamboo.log file we can see the below error.
2024-02-06 22:43:42,474 ERROR [active-objects-init-compatibility-tenant-0] [DatabaseProvider] Exception executing SQL update <ALTER TABLE AO_F36021_SONAR_SERVER_ENTITY ALTER COLUMN ADD_SONAR_PROJECT_LINK BIT>
com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "AO_F36021_SONAR_SERVER_ENTITY" because it does not exist or you do not have permissions.The error is not just limited to AO_F36021_SONAR_SERVER_ENTITY table but there are multiple tables starting with AO_ having this problem.
If we look at <bamboo-install>logs>catalina.out file, we can see Bamboo is not coming up because of error related to AO_4789DD_TASK_MONITOR table.
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find the object "AO_4789DD_TASK_MONITOR" because it does not exist or you do not have permissions.原因
There can be below possible causes for this problem.
Cause 1 : Missing table in Bamboo database.
Cause 2 : Missing tables for any particular plugin.
Cause 3: Missing correct permissions for the DB user used in bamboo.cfg.xml file.
ソリューション
ソリューション 1
This is highly unlikely that few Database table goes missing, but to be 100% sure, you can check your DB and confirm that the tables which are said to be missing in the logs are present. If they are missing you'll need to use DB backup to bring those tables back.
ソリューション 2
In case any particular plugins are causing issues with AO_ tables, you can look to disable that particular plugin, refer How to start Bamboo with all or few external apps/plugins disabled for more details.
ソリューション 3
You need to check the Bamboo DB user which is used to connect to DB is having all the correct privileges, you can refer the below 2 parameters in <bamboo-home>bamboo.cfg.xml to get the DB details.
<property name="hibernate.connection.url"></property>
<property name="hibernate.connection.username"></property>Assign the 'db-owner' role on the database for the user that will access the Bamboo database — the 'db_owner' fixed database role allows the user to perform all configuration and maintenance activities on the database. You need to add this role to the Bamboo user used to access your database by updating the login properties for your database user in SQL Server. Read more about login properties for SQL Server.
Screenshot: Adding the 'db_owner' database role to a database user in SQL Server
You can read more about it Connect Bamboo to a Microsoft SQL Server database
You can use the below query to check the permissions state of the user
SELECT
[UserName] = ulogin.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
WHEN 'G' THEN 'Windows Group'
END,
[DatabaseUserName] = princ.[name],
[Role] = null,
[PermissionType] = perm.[permission_name],
[PermissionState] = perm.[state_desc],
[ObjectType] = CASE perm.[class]
WHEN 1 THEN obj.type_desc -- Schema-contained objects
ELSE perm.[class_desc] -- Higher-level objects
END,
[ObjectName] = CASE perm.[class]
WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
WHEN 3 THEN schem.[name] -- Schemas
WHEN 4 THEN imp.[name] -- Impersonations
END,
[ColumnName] = col.[name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
sys.server_principals ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col.[object_id] = perm.major_id
AND col.[column_id] = perm.[minor_id]
LEFT JOIN
sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN
sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE
princ.[type] IN ('S','U','G') AND
-- No need for these system accounts
princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')The results should look like below, if you see in the example below Permissiontype should be CONNECT and PermissionsState should be GRANT
