Thought I would throw this out there in case anyone else has seen the same issues.
At the beginning of May I upgraded SCA to 1.10.1.48. I also converted all connections from Local System to Windows Authentication using the main Bigfix svc account. This account is MO, and has DBO on all dbs on the remote sql server.
Started getting import failures immediately at datasouce SCM::VulnerabilityResult:
ERROR: Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: User does not have permission to perform this action.
At the suggestion of support, I backup up tem_analytics, renamed and moved the SCA database.yml file, and rebuilt the datasources, which essentially re-indexed the db. Now I am getting the same error but at a different datasource:
2019-06-07 21:34:12 (+0:00:00.000) INFO: ETL after datasource task: from SCM::CheckResult : Start
2019-06-07 21:34:12 (+0:00:00.156) INFO: ETL after datasource task: from SCM::CheckResult : Failed
2019-06-07 21:58:33 (+0:24:21.207) ERROR: Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerException: User does not have permission to perform this action.
It was also suggested to add the sysadmin role NTAUTHORITY\SYSTEM to sql studio and change the Compliance service to Local System. My DBA will not allow that,
If the service account that SCA runs under is a Windows svc account and has dbo on the remote tem_analytics db, shouldn’t that cover all permissions in that db?
I have a pmr open right now but they seem stumped and I do not want to lose my legacy compliance data. Have not had a good import since May 1.
Make sure that the service account is the owner of database - use the sp_changedbowner stored procedure to set that. I ran into this issue recently when setting up BigFix Inventory. The documentation is not clear enough on this requirement.
Yep it was ownership of the db. I’m not a DBA, but I guess the service account had dbo via role and not explicit (?) Works now though after modifying dbo.
Now I just need to figure out how to change the SCA database. Before upgrade we were using a legacy db, tem_SCA. After upgrade and troubleshooting, now database.yml is pointing to tem_analytics, It was recommended to not edit the yml file directly. Does anyone know the procedure to modify the yml file back to the original db?
I actually performed that as part of my troubleshooting. The issue is when you rebuild the database.yml file, it defaults the SCA database to tem_analytics. For whatever reason, my legacy db was tem_SCA.
I also am finding reference to tem_analytics in the server.xml file.
I think my 2 options are to figure out how to point SCA to the old db, or to lay the old db over tem_analytics and roll with the new db name.
Currently on SCA 1.10 so can’t edit the .yml file directly.