SQL Run As Account Configuration for SCOM Made Easy


SQL RunAs account configuration is one of those SCOM topics that seem to be more on the tricky side of things. There are a number of articles hanging around the "innerwebs" that provide some level of instruction on ways that one might go about it. Kevin Holman offers an excellent article that explains much of what you would want to know about RunAs accounts and security profiles. He really goes into depth on how these things work. There are also some hints mentioned here. However, after searching around there don't seem to be any clear instructions on how one might configure an account to have the least privileges needed to correctly discover and monitor SQL components on a SQL server; a very common request in my field.

Question: How can you configure SQL Server permissions for a "least privileged" scenario?

Even Better Question: How can you configure SQL Server permissions for a "least privileged" scenario in just seconds?

The first part of the answer is easy, you pester your buddy Kyle (Microsoft Premier Services Field Engineer and SQL expert), asking again and again and again and again for weeks to write a script for you. When he sends you a half-baked mess transcribed from a cocktail napkin, you test it, finding the weak spots. Then badger him again and again and again until he turns it into something absolutely beautiful as shown below. Configuring the actual SQL permissions just went from awful and confusing to a copy/paste and a click.

Overview:

  1. Create a domain user/service account in Active Directory
  2. Run the script on your SQL instance. (Previously this was the hardest part but now takes only seconds)
  3. Create the Windows account credential in the SCOM Console.
  4. Distribute the account to the correct SQL servers.
  5. Add the account to the correct SQL profile(s) (Discovery and/or Monitoring and/or Default).

 

1) Create a domain account in Active Directory:

May I suggest: "SCOMSQLMONITOR"

sqlrunasscript_ad1

 

2) Run the script on your SQL instance:

Setting SQL permissions would ordinarily be the awful part. Copy the script from below or download here.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- Configure SQL Discovery/Monitoring Permissions

SET NOCOUNT ON

--DECLARE VARIABLES 
DECLARE @accountname nvarchar(128)
DECLARE @command1 nvarchar(MAX)
DECLARE @command2 nvarchar(MAX)
DECLARE @command3 nvarchar(MAX)

--ENTER DOMAIN ACCOUNT HERE
SET @accountname = 'CONTOSO\SCOMSQLMONITOR'

--CREATE LOGIN
SET @command1 = 'USE [master];
CREATE LOGIN ['+@accountname+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
'

--CREATE A USER FOR THE LOGIN IN EACH DATABASE
SET @command2 = ''
SELECT @command2 = @command2 + 
'
USE ['+name+'];
CREATE USER ['+@accountname+'] FOR LOGIN ['+@accountname+'];
'
FROM sys.databases WHERE database_id <> 2 and state = 0;

--GRANT REQUIRED PERMISSIONS TO THE LOGIN IN EACH DATABASE
SET @command3 = 
'
USE [master];
GRANT VIEW ANY DATABASE TO ['+@accountname+'];
GRANT VIEW ANY DEFINITION TO ['+@accountname+'];
GRANT VIEW SERVER STATE TO ['+@accountname+'];
USE [msdb];
EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+''';
EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';
'
--PRINT AND/OR EXECUTE THE COMMANDS.
--UNCOMMENT BELOW TO PRINT QUERY. USE "RESULTS TO TEXT" OPTION.

-- PRINT @command1;
-- PRINT @command2;
-- PRINT @command3;

EXECUTE sp_executesql @command1;
EXECUTE sp_executesql @command2;
EXECUTE sp_executesql @command3;

(SQL converted to HTML here)

This script is easy to use. It really doesn't get much easier than this. This will configure ALL of your databases (except TempDB) with "least privileges" for your designated RunAs account to discover and monitor.

Open a New Query window on your targeted SQL instance. Paste the query text. Enter YOUR account name on line 12,  then Execute (F5).

sqlrunasscript_runscript1

 

 

If you first want to see what the query will actually do, simply comment out the EXECUTE statements and UNcomment the PRINT statements. Then select the "Results To Text" icon in the toolbar:

sqlrunasscript_printscript1

 

3) Create the Windows account credential in the SCOM Console.

sqlrunasscript_account1

 

4) Distribute the account to the correct SQL servers.

sqlrunasscript_account2

 

5) Add the account to the correct SQL profile(s) (Discovery and/or Monitoring and/or Default).

In my lab I have used the same account for both Discovery and Monitoring profiles. The screenshot below shows the Discovery profile only. I'll not go into much detail of  the various SQL profiles because Kevin already covered it magnificently here.

sqlrunasscript_profile1

 

Enjoy.

Let me know if you have any comments on how I can improve this article.


Comments (12)

  1. LeAnne says:

    The SQL instance script and SQL MP Run As account configuration walk-through is a super timesaver. Thanks to you and Kyle for building and double-thanks for sharing with the community, Tyson!

  2. Ralph Kyttle says:

    Great work Kyle, and thanks for sharing Tyson! I wanted to share some new developments around SQL Run As accounts that may make this process even easier. The SQL commands you have above look great in terms of speeding up the process in granting the proper permissions to the Run As account, but we have also found that it is possible to grant those same permissions on the SCOM service itself, removing the need for any Run As accounts all together. See here for posts describing this process: https://blogs.technet.microsoft.com/kevinholman/2016/04/26/sql-mp-run-as-accounts-no-longer-required/ and http://blogs.technet.com/b/ralphkyttle/archive/2016/04/27/sql-mp-run-as-accounts-no-longer-required-the-thought-process.aspx

  3. Ken Rappold says:

    Would this article, in your opinion, improve upon the method above? More security? Less administrative overhead?
    https://blogs.technet.microsoft.com/ralphkyttle/2016/04/27/sql-mp-run-as-accounts-no-longer-required-the-thought-process/

    1. Ken Rappold says:

      When I wrote my comment, I didn't see Kyttle's response... 🙁

  4. Nicole says:

    Thank you both for providing this information, it's invaluable in offering customers new ways to address run-as-account configuration. Not all customers are the same so we appreciate this contribution to the community!

  5. Denis says:

    Is there any way to modify the above query so that it excludes secondary DBs participating in an availabity group? It errors on these which results in some of the permissions not correctly propagating.

    1. Tyson.Paul says:

      @Denis,
      You can modify the script so that it will only print out the final query to the screen so that you can modify it before actually running the query. Simply remove or modify the lines that affect those other databases.

  6. Rinku says:

    Small question - is this SQL instance query need to run on SCOM DB and DW? or All SQL servers and instances which will be getting monitor by SCOM?

    1. Tyson.Paul says:

      Run the query on any SQL instance which requires a RunAs account for discovery/monitoring; this may include the SQL instance(s) which host the Ops and DW databases.
      Don't forget to customize your RunAs account name in the script before executing it.

  7. Correction:
    Change the line number 26
    from

    FROM sys.databases WHERE database_id 2;

    to
    FROM sys.databases WHERE database_id 2 and state = 0;

    otherwise you will get an error during execution as some databases might be offline.

    1. Tyson.Paul says:

      Excellent! Thank you.

  8. jalekz says:

    This script is great, I added two modifications

    1) Updateability of the DB, so skip read ony DBs from availability groups.
    2) Grant permissions to table sys.database_mirroring_witnesses, I got an error because of this and it's documented here https://technet.microsoft.com/en-us/library/dd767431.aspx

    -- Configure SQL Discovery/Monitoring Permissions
    SET NOCOUNT ON

    --DECLARE VARIABLES
    DECLARE @accountname nvarchar(128)
    DECLARE @command1 nvarchar(MAX)
    DECLARE @command2 nvarchar(MAX)
    DECLARE @command3 nvarchar(MAX)

    --ENTER DOMAIN ACCOUNT HERE
    SET @accountname = 'CONTOSO\SCOMSQLMONITOR'

    --CREATE LOGIN
    SET @command1 = 'USE [master];
    IF NOT EXISTS (SELECT [name] FROM sys.server_principals WHERE [name] = ''' + @accountname +''')
    CREATE LOGIN ['+@accountname+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
    '

    --CREATE A USER FOR THE LOGIN IN EACH DATABASE
    SET @command2 = ''
    SELECT @command2 = @command2 +
    '
    USE [' + [name] + '];
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = '''+@accountname+''')
    CREATE USER ['+@accountname+'] FOR LOGIN ['+@accountname+'];
    '
    FROM sys.databases WHERE database_id 2 and state = 0 AND DATABASEPROPERTYEX([name], 'Updateability') = N'READ_WRITE'

    --GRANT REQUIRED PERMISSIONS TO THE LOGIN IN EACH DATABASE
    SET @command3 =
    '
    USE [master];
    GRANT VIEW ANY DATABASE TO ['+@accountname+'];
    GRANT VIEW ANY DEFINITION TO ['+@accountname+'];
    GRANT VIEW SERVER STATE TO ['+@accountname+'];
    GRANT SELECT ON sys.database_mirroring_witnesses TO ['+@accountname+'];

    USE [msdb];
    EXEC sp_addrolemember @rolename=''PolicyAdministratorRole'', @membername='''+@accountname+''';
    EXEC sp_addrolemember @rolename=''SQLAgentReaderRole'', @membername='''+@accountname+''';
    '

    PRINT @command1;
    PRINT @command2;
    PRINT @command3;

    --EXECUTE sp_executesql @command1;
    --EXECUTE sp_executesql @command2;
    --EXECUTE sp_executesql @command3;

Skip to main content