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.
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)