I am almost willing to bet my girlfriend, when I’m saying you have seen this alert at least once. The alert is pretty self-explaining. The Run As Account you are using for SQL either doesn’t exist on the SQL server/instance/database or it doesn’t have sufficient permission.

Q: But I’m not using any Run As Accounts for monitoring SQL!

A: Actually you are. Unless else specified, you are using the Action Account.

Now, Local System has been removed in SQL Server and a lot of SQL administrators remove it if it’s there, which means that we can’t monitor the database with SCOM, unless we manually give access. I’ve seen that a lot of costumers just add the Action Account with dbo permissions, simply as a quick and dirty solution. But this is dirty and unnecessary. Instead of giving the Action Account dbo, we have two options:

1. Grant access to Local System

2. Grant access to the SCOM action account/another account

Since option 1 isn’t really an option, we’ll go for option 2. Here’s what to do:

In Active Directory:

1. Create an account named something like OpsMgrSQLRunAs or whatever suits you. You could also use the SCOM action account – it gives the same result. In case you want to do that, skip this step.

In SQL:

1. Create the login for the user in SQL

2. On instance level, grant the user the following permissions:

     a. VIEW ANY DEFINITION

     b. VIEW SERVER STATE

3. On the MSDB database grant the user the following permission:

     a. SQLAgentReaderRole

4. On all system (model, master etc.) and user databases (ie. OperationsManager) grant the user the following permission:

a. Database User

The SQL security part is done – yay.

In SCOM:

1. Install the agent on the server if not already done

2. Create a Run As Account named SQL Run As Account (or whatever suits you), with the credentials from step 1. Choose “More Secure” on the Distribution Security page.

clip_image002[6]

3. Create a group named SQL Servers with Run As Account defined. Go to Explicit Members and add the Windows Computer object of the new SQL server.

    a. You don’t have to create this group, but I find it easier for controlling targeting

4. Go to Administration à Accounts and locate the account named SQL Run As Account. Go to the Distribution pane and add the new SQL server.

5. Go to Profiles and locate the SQL Server Default Action Account profile. Add the SQL Run As Account and use the SQL Servers with Run As Account defined as target

clip_image004[5]

6. Repeat step 5 for SQL Server Discovery Account and SQL Server Monitoring Account

7. Close the “Run As Account does not exist on the target system or does not have enough permissions” alert for the SQL servers you did this for

8. Lean back and enjoy

9. Go home with a smile

Every time you get a new SQL server:

1. Create the login on the SQL server

2. Grant the user the permissions listed above

3. Add the server to the SQL Servers with Run As Account defined group

4. Add the server in the Distribution pane of the SQL Run As Account

Every time you get a new database:

1. Grant the user the permissions listed above

 

Happy monitoring your SQL!