Sometimes when we do some unexpected things with SQL Server, you would get messages as well which are never seen earlier. One of my client was trying to implement STIG for SQL 2016 and then someone reported below error in SSMS while connecting to SQL. Let us learn about how to fix the error Accessing this Server via SQL Management Objects.
Accessing this server via SQL Management Objects (SMO) or Distributed Management Objects (DMO) is currently not permitted. Would you Ike to enable functionality?
Here is the screenshot of the error message which came up while connecting to SQL.
Once we hit “Yes” we could connect via SSMS and things were looking OK. They were more interested to know the cause
When I checked ERRORLOG, I found below
2017-12-03 23:42:44.720 spid70 Configuration option ‘SMO and DMO XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.
WORKAROUND/SOLUTION
When I was talking to my client, they informed that here is the action which they were taking.
As per article they ran below command
EXEC SP_CONFIGURE 'show advanced options', 1 EXEC SP_CONFIGURE 'SMO and DMO XPs', 0 RECONFIGURE
But SSMS needs the setting to be “ON” so it was asking to turn it ON. As soon as we hit “Yes” on the message, it enables the setting “’SMO and DMO XPs” by making value as 1 in sp_configure.
I did some more research and found that one is for SQL 2005 and I couldn’t find the same for any version above SQL Server 2005. So, I asked them to ignore this. Even the article itself mentions “If you are using SQL Server Management Studio to administer the SQL Server DBMS, document, approve and enable this option in the System Security Plan.”
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
I’d hope your customer, if they work for the same employer I do, realizes that you’re supposed to apply the most current applicable STIG to a server. Otherwise, you’re potentially in a world of hurt when the auditors drop by.
(Side note, the current SQL Server 2016 Instance STIG is v1r6 and does NOT have the aforementioned check)
I always find it fun to come across someone else dealing with the STIGs.
Although, in the case of your client, I have to wonder why they were trying to apply a SQL2005 STIG to a SQL2016 system, when there’s a SQL2016 STIG (v1r6 currently.)
With the requirements being to apply the most current STIG within 30 days of release, they’re not going to have a good time when the auditors roll around…