Say Hey Kid
Comments posted to this topic are about the item SCOM Alert: MSSQL 2014: SQL Server cannot authenticate using Kerberos
Could you please start this with a brief explanation of what SCOM is?
This could help... at least me!
I had the same question, but found the following : https://squaredup.com/content/guides/what-is-scom/
It is a bit sales-y, but has enough technical detail to serve as a starting point.
Disclosure: I have no connection, commercial or otherwise, with SquaredUp.
I believe that SCOM stand for System Center Operation Manager, a Microsoft centralized operation and administration suite.
If it is the case, why tend to be bloated by our SCOM, misconfigured with a lot (lot) of false alarms due to just thrown in the default templates for SQL Server. The second parragraf made a lot of sense to me (check if it is something wrong with SCOM, no with the alarm)
Hall of Fame
at https://www.microsoft.com/en-us/download/details.aspx?id=39046 you can download the Kerberos Configuration Manager for SQL Server. It helped me a lot with all this SPN stuff, since it finds out by itself, which entries has to added and which should be removed.
BTW: to run the SPN commands you'll need to have some domain admin permissions. If you don't have them (which would be a good sign, except you are working for a very small shop) you can either give the tool to your co-workers or just copy out the necessary commands.
God is real, unless declared integer.
[NT AUTHORITY\SYSTEM] should not be sysadmin. You can use a specific account for the SCOM healtservice with just the permissions needed for the specific SCOM managementpack for SQLServer.
There should be a scripted action prepared for it that can be run from inside the SCOM console.
The SCOM team will need your help as they need an user with sysadmin permissions to execute the action that creates the lovpriv account.
Hall of Fame
You'll want to go thru the SCOM Management Pack for SQL guide and probably configure a 'run as' profile. (And you can configure that profile to only be distributed to your SQL servers.)
So why did 90% of the SPNs need to be re-registered? Do you have a network with a lot of domain controllers? If so we've seen a problem where it's caused by SQL automatically deleting the SPN when it shuts down and recreates it when it starts up. That causes a problem if not all the domain controllers process those requests in the right order. Sometimes they get the create SPN command first (which obviously fails), followed by the delete which leaves you with no SPN!
The solution is to not grant the SQL Service account permission in active directory to create/drop SPNs. When you install SQL just manually register them once. Problem solved.
As already mentioned, SCOM can be configured to use a service account to connect to MSSQL instances; not a good idea to use the (default) SYSTEM account.
In SCOM, you need to configure the Agent, the Subscriber, the Subscription, the Authoring Group, and finally the service account.
Then, in each MSSQL instance you want SCOM to monitor, create the Login, and grant the required permissions (such as VIEW SERVER STATE in [master] database).
The entire setup can be scripted, and is part of our installation process. Let's stop using the local SYSTEM account, please!
Viewing 9 posts - 1 through 8 (of 8 total)