Error while executing SQL command after update to 2021.3.12205

After we upgraded to 2021.3.12205 (on-prem) we are not able to open Library - Packages (err: Object not set…), and seeing this in the logs (we are running Windows Server and SQL Server with Integrated Security, the service account is db_owner on the Octopus Database):
Error while executing SQL command in transaction 'SqlTransactionMetricsProducer.UpdateMetrics': VIEW SERVER STATE permission was denied on object 'server', database 'master'.
The user does not have permission to perform this action.
The command being executed was:
SELECT COUNT(*) FROM sys.dm _tran_active_transactions"
Please advice how to proceed debug and fix this issue.

Hi @bent.kristiansen,

Welcome to the Octopus Deploy community! I’m sorry to see you’re having issues accessing your packages after upgrading. It looks like the errors in your log may be related to this open ticket and possibly not cause the of the error you are seeing in the UI. Would it be possible to have you upload your full server log using this secure upload link so that I can have a closer look?

Are you getting the error in the UI while attempting to access the Packages screen (https://youroctopusurl/app#/Spaces-1/library/builtinrepository) or is it happening when you attempt to access one of your packages?

Is this also happening for all users who have access to the packages?

Last question for now, can you attempt to access the screen that throws the error while using your browser’s dev tools and see a specific API call that may be failing?

I look forward to your response and please let me know if you have any other questions.

Thanks!
Dan

Hi, thanks! It seems like the same case.
Yes it happens when accessing /app#/Spaces-1/library/builtinrepository
image

And, yes. the call to: “/api/Spaces-1/packages?filter=&latest=true&take=50” gives HTTP 500.

If really needed, I will review the 75 MB zip file of logs for sensitive data, and upload it. Thanks for any advice on how to solve this. I guess/hope that granting the Octopus service account server wide permissions, are not the way to go?

Hi again. Logfiles are uploaded. Do we also need to send a separate email to support for this?

Just an interesting discovery/“side note”:

  • works: /api/Spaces-1/packages?filter=&latest=true&take=4
  • gives “Object not set…”: /api/Spaces-1/packages?filter=&latest=true&take=5

Hi @bent.kristiansen,

Thank you for sending over the logs and for the results of those API calls. I will take a look through the logs this morning to see what I can find. I did actually find out a little more information about that SQL error and it wasn’t related to the bug I had linked. I need to follow up with our engineering team, but I still believe it’s not the cause of your error attempting to access the package feed. I will be in touch as soon as I have more information but please feel free to reach out if you have any other questions.

Thanks!
Dan

1 Like

Hi @bent.kristiansen,

Thanks for your patience while I looked into this a bit more. I didn’t see anything glaring in your logs, however, your discovery of the working vs broken API calls will definitely help us narrow this down. My assumption is that there is a package in the feed that has some kind of invalid data which is causing the call to fail.

Since taking the first four packages works and taking five doesn’t, it seems likely that the fifth package in the feed is causing the issue. You could add a skip argument to your API query to see if bypassing that package resolves successfully (/api/Spaces-1/packages?filter=&latest=true&skip=5&take=6). If we can see the first four packages and everything after five then we have our culprit!

If you can find that package the next step would be to delete and upload the package again to see if it resolves the error. If you can’t find the name of that package via the API because of the failed calls you can retrieve it from the database. The API returns the packages in alphabetical order so that should help narrow it down as well. In my example below you can see which package would be fifth in my feed.

select * from NuGetPackage order by PackageId asc

I’m looking forward to hearing what you find and please let me know if you have any other questions.

Thanks!
Dan

Thanks again @dan_close

Does that really select the same order as the UI? (Since PackageId is a string? I thought the default order was newest on top?) I do not see that the 5th row has different pattern form the rows above and below. /api/Spaces-1/packages?filter=&latest=true&skip=7&take=1 gives null pointer, so there are more packages causing the issue. Unfortunately I do not have permission to run SQL Profiler on the database, so if you’re able to provide the exact SQL, that may be helpful.

We are able to publish packages from command line (github action), and deploy them.

Still this is the most noisy part of the log:
The command being executed was:

SELECT COUNT(*) FROM sys.dm_tran_active_transactions
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): VIEW SERVER STATE permission was denied on object 'server', database 'master'.
The user does not have permission to perform this action.

Hi @bent.kristiansen,

The SQL query is a little confusing when attempting to compare against what the UI displays. The UI does indeed return the alphabetical list of package names. The SQL query I threw out there however will just return all packages so you’ll see repeats of the same package for the different versions. In my sandbox, you can see how the query result compares to what I see in my UI.


If you were able to run the select against the packages I’d be curious to compare the JSON between one of the broken packages and a working package. Perhaps if we can identify something in the broken packages it will be easier to find exactly how many there are.

As for the error in your logs, I will let you know when I have more information about that. We don’t think those permissions are something needed for our on-prem clients but I just need to verify that and get a ticket created if it is the case. Please let me know if you have any other questions.

Thanks,
Dan

Ok, It does not look as “systematic” for us as for you when we try to compare the database select and api-queries from the browser. Please see the select top 100 and a few api-queries json results I have uploaded the “safe area”.

Thanks @bent.kristiansen,

I’m just taking a look through the files you sent now. I don’t immediately see any glaring issue with the packages in question, but I will continue with a closer look. I see a few that had null titles, but in my environment, that doesn’t seem to break anything.

One thing I should have had you check out earlier was whether or not your package feed has been re-indexed lately. If you filter your tasks by the ‘Sync built-in package repository’ task type do you see any tasks that have run recently? Also, I’m assuming that because you get the error on the package repository screen you do not see the repository retention or package indexing options on the right-hand side. Is that correct?
image

Thanks!
Dan

Correct

Hi @bent.kristiansen,

I’ve replied to the ticket that you’ve opened in Zendesk.

If possible, can we continue our communication via that platform going forward?
This will help avoid confusion and communication issues down the line.

If you’d prefer to continue the discussion here instead, then please let me know!

Kind Regards,
Adam

Hi again, should I login to Zendesk or just reply on email? :blush:

image002.jpg

Hi @bent.kristiansen,

Apologies! Yes, we use e-mails to communicate via Zendesk.

You can absolutely reply to the e-mail to get a response through to us.

Kind Regards,
Adam

I can see your response in there, now. Thanks a lot!

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.