SQL Server agent job impacted after (hiding the instance) and (disabling shared resources).

Baral, Shakti 1 Reputation point
2021-05-05T18:01:02.507+00:00

Hello,
After an internal audit, the DBA team was asked to hide (SQL server instance) and (disabled shared resources) based on CIS Benchmark SQL Server documentation. We disable the Shared Memory and Hide Instance set to 'Yes'. Now our agent jobs are impacted. Does anyone face similar issues? We have stand-alone environment.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,302 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,521 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 99,296 Reputation points MVP
    2021-05-06T21:02:45.687+00:00

    I was able to repro this. Disabling both shared memory and hiding the instance results in SQL Server Agent not being able to connect to MACHINE\INSTANCE. And it's not unique to Agent. The same type of login also fails from SSMS. I had to to use localhost,nnn, where nnn is the port number.

    When enabled Shared memory but kept the instance hidden, I could connect with MACHINE\INSTANCE and Agent was able to start. I don't about this CIS Benchmark, but either they are saying silly things, or there is some misunderstanding: I see little point in disabling shared memory.

    And hiding the instance? Maybe, but this means that clients will need to connect by port number.

    0 comments No comments

  2. AmeliaGu-MSFT 13,956 Reputation points Microsoft Vendor
    2021-05-07T06:00:32.69+00:00

    Hi @Baral, Shakti ,
    According to some test, it seems SQL Server Agent will be disabled after we disabling the Shared Memory protocol.
    Could you please use the following query check if the SQL Server Agent is disabled:

    use master  
    go  
    select * from sys.configurations where name='Agent XPs'  
    

    If the value of Agent XPs is 0, it means that the Agent XPs component is disabled. We need to enable the SQL Server Agent:

    exec sp_configure 'show advanced options',1  
    go  
    reconfigure with override  
    exec sp_configure 'Agent XPs',1  
    go  
    reconfigure with override  
    exec sp_configure 'show advanced options',0  
    go  
    reconfigure with override  
    

    In addition, Set the HideInstance flag to indicate that SQL Server Browser should not respond with information about this server instance. Please make sure the SQL Server named instance are listening on the specific TCP Port. Then we need to provide the port number in the connection string to connect to the hidden instance, or create an alias on the client, specifying the port number that SQL Server named instance listens to. Please refer to this doc which might help.

    Best Regards,
    Amelia


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.