Following the recent acquisition of SentryOne by SolarWinds, I’ve decided to write a few special blog posts dedicated to our favorite SQL server monitoring platform.
As part of the managed DBA service that Madeira data solutions provides, we make extensive use of the SentryOne monitoring and alerting platform. As such, we’ve gathered significant experience in using, managing, and maintaining the platform. That also includes quite a few issues that tend to repeat themselves, specifically during SentryOne version upgrades.
SentryOne is a huge monolith of a product. It’s an overall awesome product and the SQL Server monitoring platform of choice for us at Madeira Data Solution. But, it still has its issues and problems here and there (and that’s okay, we’re all human). We’ve been experiencing all kinds of issues during SentryOne version upgrades over the years. But we always pushed through and made it work.
In this post, I’ll describe the common issues that we experienced, and how to resolve them quickly.
- “Please enter the encryption key password from previous installation”
- SELECT denied on column X in table RSAKeyPair
- CryptographicException: The parameter is incorrect.
- Prune Remote History Data – Instance offline
- Capturing Other Database Errors during installation / upgrade
- Other Issues
- Log Files
- See Also
“Please enter the encryption key password from previous installation”
During the setup, we’re suddenly greeted with a weird message requesting us to “Please enter the encryption key password from previous installation“.
When trying to enter anything as the password, setup fails with error 0x80070643.
This sometimes happens when you run the setup on several different monitoring service machines within a short timespan (such as, for example, when upgrading the version of multiple monitoring services).
To resolve the issue, you must run this command in the SentryOne database:
TRUNCATE TABLE RSAKeyPair
After running this command, re-start the installation setup.
I know, it sounds scary and weird, but I assure you that it’s completely harmless, and it’ll solve the problem.
SELECT denied on column X in table RSAKeyPair
Speaking of the RSAKeyPair table, it’s actually a very important table in SentryOne.
Every monitoring service needs to access this table upon initial handshake with the repository.
Which makes it all the more absurd that the built-in so-called “allow_all” database role in the SentryOne database doesn’t have the necessary permissions on this table. On the contrary, there’s an overwhelming amount of DENY policies on this table’s columns, preventing its proper usage from anyone who’s not a sysadmin.
But what are we supposed to do? Give sysadmin permissions on the SentryOne database to all accounts running the SentryOne monitoring service? Bit of an overkill, isn’t it?
The solution? GRANT the necessary permissions to the allow_all database role (or whichever role your monitoring service accounts are members of):
GRANT SELECT ON [dbo].[RSAKeyPair] TO [allow_all] AS [dbo];
GRANT SELECT ON [dbo].[RSAKeyPair] ([ID],[Name],[PublicKey],[PrivateKeyEncrypted],[PrivateKeyHashCode]) TO [allow_all] AS [dbo];
Now there’s no longer a need for all of your monitoring service accounts to be sysadmins on your repository.
NOTE: You may have to re-do this operation after each version upgrade, as these permissions seem to be reset sometimes during installation.
CryptographicException: The parameter is incorrect
After the upgrade, nearly all of the monitoring services report the following error in the “System Status” screen:
Task Faulted Error: An error has occurred:
Message: The parameter is incorrect.
System.Security.Cryptography.CryptographicException: The parameter is incorrect.
at System.Security.Cryptography.CryptographicException.ThrowCryptographicException(Int32 hr)
at System.Security.Cryptography.RSACryptoServiceProvider.DecryptKey(SafeKeyHandle pKeyContext, Byte pbEncryptedKey, Int32 cbEncryptedKey, Boolean fOAEP, ObjectHandleOnStack ohRetDecryptedKey)
at System.Security.Cryptography.RSACryptoServiceProvider.Decrypt(Byte rgb, Boolean fOAEP)
at System.Security.Cryptography.RSACryptoServiceProvider.Decrypt(Byte data, RSAEncryptionPadding padding)
at System.Security.Cryptography.RSAPKCS1KeyExchangeDeformatter.DecryptKeyExchange(Byte rgbIn)
at SentryOne.Security.Cryptography.RsaEncryptor.DecryptMessage(RsaMessage message)
at SentryOne.App.Core.Security.RsaKeyPair.DecryptAndDecodeBase64(String rsaMessageEncoded)
at Intercerve.Monitoring.MultiTargetSmtpClient.ConfigureServer(ISmtpServerConfiguration smtpServers, Int32 currentIndex, SmtpClient smtpClient)
at Intercerve.Monitoring.MultiTargetSmtpClient.AssignSmtpServers(IEnumerable`1 smtpServers)
at SqlSentry.Server.ManagementEngineSettingsUpdater.UpdateInternalCaches(DateTime workLoopStartTimeUtc, Int32 siteID, Int32 managementEngineID, Boolean hasSiteChanged, SiteAccessor site, ManagementEngineSettingsUpdaterTask settingsUpdaterTask)
This error stack (specifically the “AssignSmtpServers” method) is complaining about trying to decrypt something. That is happening in the context of refreshing SMTP settings. That could happen if there are multiple SMTP servers configured, and the encryption has somehow become corrupted.
The solution, therefore, is to re-configure your SMTP settings so that they could be “re-encrypted”.
- In the SentryOne client, go to “General Settings” in the navigator pane.
- In the “Alerting” page, click on the ellipsis […] button next to the selection dropdown for the SMTP servers.
- Edit the SMTP settings that you’re using for alerting, and re-enter the password.
- Click on Save (and send a little test e-mail to make sure that it works).
- After a few moments, the “parameter is incorrect” error should disappear.
Prune Remote History Data – Instance Offline
If in “Show System Status”, seeing an error in the
Prune Remote History Data component with an
Instance Offline error – Connect by RDP to the monitoring server (or whichever server that has direct access to the monitored target), open SentryOne client there, right-click on the target and
Stop Watching the entire target server (both SQL and Windows), and then re-watch it by right-clicking and selecting
Capturing Other Database Errors during installation / upgrade
Sometimes there could be all kinds of unexplainable errors during the SentryOne installation, which results in an un-informative error.
Some of these errors could be the result of something going wrong while running the upgrade on the SentryOne database.
The solution, therefore, is to set up an Extended Events session to (hopefully) capture whatever error that’s being thrown by the setup, and see which T-SQL command is causing it:
CREATE EVENT SESSION [CaptureTSQLErrors] ON SERVER
ADD EVENT sqlserver.error_reported(
WHERE ([severity]>(10) AND [sqlserver].[sql_text]<>N''))
ADD TARGET package0.event_file(SET filename=N'CaptureTSQLErrors.xel',max_file_size=(20),max_rollover_files=(10))
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=PER_CPU,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
For example, using this technique we discovered an error caused by the SentryOne installer trying to create a new CHECK CONSTRAINT on the Advisory Conditions table, but existing data in the table was violating the check. Deleting those advisory conditions resolved the issue in our case.
But your use cases may vary and could be caused by other issues. Either way, knowing the actual SQL Server error and the T-SQL command that’s causing it will help you significantly in troubleshooting it.
If after upgrade, the monitoring service is not connected, try restarting the SentryOne service on the monitoring server.
If monitoring data is not being collected, right-click on a site or All Targets in SentryOne client, and open “Show System Status” to review any errors. What you see there may give you a hint as to what’s happening, and you could use the details there when opening a Support case at support.sentryone.com
SentryOne also has several error log files of its own, and the data in them can often be very helpful (or, at the very least, you could send them to SentryOne Support).
For more details about how to find these log files, visit this page: https://docs.sentryone.com/help/sentryone-troubleshooting
Additional troubleshooting tips can be found here:
- https://support.sentryone.com (either seek help in the community or open a support case)
As I said at the start of this post, SentryOne is a huge, feature-rich product. So, I’d say it’s more than forgivable that some issues exist here and there. And despite all of its shortcomings, it’s still the most powerful and flexible SQL Server monitoring platform that we’ve seen at Madeira Data Solutions.
With that said, SentryOne keep enhancing and improving their products, and they’re very dedicated to their customers.
I expect that all of the issues described above would most likely be fixed at some point, and then this whole blog post would become obsolete.
Until then, I hope that I was able to provide some useful information to whoever may require it.
Pingback: Monitoring SQL Server Version Updates using SentryOne – Eitan Blumin's Blog