DiscoverSQL2012FileGroups.js : Cannot login to database – Why?

Forum: Operations Manager4
Viewing 15 posts - 1 through 15 (of 20 total)
  • #219129
    Profile photo of
    Anonymous

    I am at my wits end trying to figure out what is causing these errors and I hope someone can help me figure out what I am missing.

    • Per the latest SQL Server MP Guide, we are configuring our environment to use specific windows accounts for SQL Discovery, Monitoring, and Default Action accounts. I have followed the instructions found in the guide, and also here and here.  I have verified every setting on the server multiple times and every time I close the alert, it just reopens after about 30 minutes – 1 hour.
    • Management Group: MGMTGOUP. Script: DiscoverSQL2012FileGroups.js: Cannot login to database [SERVERNAME.DOMAIN.com][MSSQLSERVER:Social DB]
    • The other thing I don’t understand, if I go into the Monitoring view in SCOM, under Microsoft SQL Server – > Database -> Database State, and search for this particular database, its state is Healthy, and its SQL Server 2012 DB File Group and DB Log File are in a healthy state as well.
    • In the OperationsManager log on the server the sequence looks like this:
    • DatabaseDiscovery:MSSQLSERVER : Database for SQL instance ‘MSSQLSERVER’ discovers successfully.
    • Management Group: MGMTGROUP. Script: DiscoverSQL2012FileGroups.js : Cannot login to database [SERVER.DOMAIN.com][MSSQLSERVER:DATABASE]  (And then it cycles through each database in the instance)
    • DatabaseFileGroupDiscovery:MSSQLSERVER : Database File groups for SQL instance ‘MSSQLSERVER’ discovers successfully.

    Any ideas as to what is going on here that I am missing or that I can check?

    #219134
    Profile photo of ARentsch
    ARentsch
    Participant

    I know it from the past – the SQL MP runas-config is tricky (especially when the runas acounts are not “super-sql-admin-accounts ” 😉 )

    It seems that your configuration is still not sufficient to let all scripts in the background run successfully.
    Maybe Kevin’s post is a kind of help: https://blogs.technet.com/b/kevinholman/archive/2013/10/24/opsmgr-sql-mp-version-6-4-1-0-capabilities-and-configuration.aspx

    #219136
    Profile photo of
    Anonymous

    Yeah, I hear ya.  I have been through that document several times.  I will go over it again and see if something sticks out that I may have missed before.

    #219164
    Profile photo of Nick Alston
    Nick Alston
    Participant

    I have the same issue, and i’m not sure why. All my SQL action accounts have sysadmin permissions to all the databases, and yet for some reason i get this error.

    #219168
    Profile photo of
    Anonymous

    Well maybe we can try to figure this out together.  What version of Ops Manager are you on?  I am on 7.1.102260.0 .

    #219170
    Profile photo of Nick Alston
    Nick Alston
    Participant

    I am running the same version.

    #219173
    Profile photo of ARentsch
    ARentsch
    Participant

    Have you also configure “LogonTo” settings at the AD account level for the RunAsAccounts? Additionally in our case the RunAs account has to be local admin on the SQL nodes. What was also tricky is the account distribution – we are using “more secure” and fine granular permissions on each database (so we are not using sysadmin) that will be automatically configured on a stored procedure concept.

    #219179
    Profile photo of
    Anonymous

    What do you mean by “Have you also configure “LogonTo” settings at the AD account level for the RunAsAccounts? ” I have granted the RunAs accounts log on locally rights on each individual server as specified in the management pack guide.  Does this need to be done at the domain level?

    And I am doing the same thing, using “more secure” and granting the RunAs accounts only the minimum access required for each database

    #219181
    Profile photo of ARentsch
    ARentsch
    Participant

    I don’t know if your runas account do have LogonTo restrictions (at AD account level), but maybe they have.

    #219197
    Profile photo of
    Anonymous

    I double checked this morning but there are no LogonTo restrictions at the AD account level.  They are all set to All Computers.

    Any other ideas?

    #219235
    Profile photo of
    Anonymous

    So, what I have figured out is that when I run the scripts provided to me in the Management Pack documentation (modified to use my service name), everything works fine and the script says that it creates the individual database logins.  However, it doesn’t.  I have to go back in and create the SQL login for each database.  Once I do that, everything works great.

    Is this the normal, expected behavior?

    #221319
    Profile photo of Michael H
    Michael H
    Participant

    I implemented the low-privilege setup from the SQL MP guide because on a few standalone SQL instances and one SQL 2012 cluster, not all scripts were running.  The SQL cluster is a two node cluster with 2 SQL instances in and active/active config (One instance is active on each node in the cluster).  On the standalone SQL instances, the low-privilege setup is working great.  On the SQL cluster it is not.  Reading the latest SQL MP guide, it appears that SQL 2012 cluster are supported for monitoring.  I’ve made the additional configuration changes that the MP guide says is required for cluster monitoring.  I am getting PowerShell script execution error messages in the OpsMgr console stating a lack of permissions.  When I look at the SQL error logs, I see that something is trying to log into the databases as NT AUTHORITY\SYSTEM and can’t because access is denied.  That login is not mapped to any databases – obviously.  Why would these workflows be trying to execute as NT AUTHORITY\SYSTEM and not one of the the action accounts in the SQL RunAs profiles that I’ve setup and deployed to the two cluster nodes?

    #221332

    i have sometimes same alert, but only for some databases,

    check if scom account have all needed permission on this DB, mentioned in alert

    checl sql error log for unsuccessiful logins

    #221368
    Profile photo of Michael H
    Michael H
    Participant

    I ran the scripts provided by the MP on the database server.  I then verified that the action accounts have logins on the databases.  The SQL logs show that NT AUTHORITY\SYSTEM is trying to log into the databases though.  It should be using the action accounts I deployed.  I deployed the action accounts to the Windows Operating system object.  Maybe I need to deploy it to another object.

    #221369

    u run script under your or SCOM account?

     

    i created the group for SQL servers and set  account to that group.

    also mapped account to each server (low privelege)

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic.