Disabling Deprecated feature sp_configure Remote access

-> We had an audit on the database servers. An auditor advise us that “Remote access” feature should be disabled due to below reasons,

[$] It is an obscure SQL Server to SQL Server communication feature that is deprecated feature. Please refer article for more details.

-> It was decided that we should disable it on our SQL Server instances. We disabled it using below code,

EXEC sp_configure 'remote access', 0 ;  
GO  
RECONFIGURE ;  
GO

OR

-> Right Click SQL Server Instance -> Properties -> Connections -> Uncheck “Allow remote connections to this server”,

-> Please note that a restart of SQL Services is required for the changes to take effect.

-> We faced below issues after disabling sp_configure ‘remote access’ on our SQL Server instances.

Log Shipping

-> Logshipping main functionality as a whole such as backup, copy and restore works fine without issues. But the reporting part utilizing SQL Server Management Studio “Transaction Log Shipping Status” report displays incorrect information.

-> LSAlert job on Monitor servers fails as it is not able to get upto date information from Primary and Standby Instances.

-> The issues related to SSMS “Transaction Log Shipping Status” report and LSAlert job seems to be due to the fact that it is not able to get proper data from Primary and Standby server as the linked server fails with below error,

Could not execute procedure on remote server ‘LOGSHIPLINK_JBSAG4_200666876’ because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.

-> ‘LOGSHIPLINK_JBSAG4_200666876’ is a System Linked server that is created as part of Logshipping. I dropped this linked server and recreated using sp_add_linkedserver and was able to see same behaviour. I tried this step on a test server.

-> I checked the trace further and understood that the linked server errors out when trying to run below command remotely,

select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N’.msdb.sys.sp_processlogshippingmonitorhistory’
exec @retcode = @linkcmd
                    @mode = @mode
                    ,@agent_id = @agent_id
                    ,@agent_type = @agent_type
                    ,@session_id = @session_id
                    ,@session_status = @session_status
                    ,@monitor_server = @monitor_server
                    ,@monitor_server_security_mode = 1
                    ,@database = @database
                    ,@log_time = @log_time
                    ,@log_time_utc = @log_time_utc
                    ,@message = @message

-> This is when I realized that the linked server utilizes RPC Out that is “Remote Procedure call” to execute above function remotely on Primary and DR to fetch the details. Linked servers with “RPC out” true will require Remote access to be turned on and this seems to be the root cause of our issue.

-> Linked Servers with RPC out set to false doesn’t have any issues and will function normally. But in our case, SSMS “Transaction Log Shipping Status” report and LSAlert job needs “RPC Out” set to true on Linked Servers to get the data remotely and display it as appropriate.

Linked Servers

-> The lab utilizes 2 database servers JBSAG1 and JBSAG2. Remote access option is disabled on both servers. A linked server for JBSAG2 is created on SQL Server Instance JBSAG1,

Remote Access

Linked server property

-> Querying a remote object Table1 using Linked server. It works fine,

-> Querying a remote View vw_table1 using Linked server. It works fine,

-> Querying a remote Stored procedure sp_table1 using Linked server. It returns an error that remote access option is required,

-> As a workaround, I will utilize “Execute AT” to execute remote Stored procedure sp_table1 using Linked server. It works,

Always On

I have setup a lab with 3 Always on replicas (2 in East US and 1 in Korea Central), testing was performed on the lab with Remote Access turned off,

[$] Create an Availability group.
[$] Drop an Availability group.
[$] Suspend and resume availability databases.
[$] Load the availability database and create issues such as HADR_SYNC_COMMIT, Redo latency, high log send queue etc.
[$] Check Always On dashboard several times.
[$] Failover and failback manually.
[$] Failover and failback automatically.

I did not see any issues on above scenario with remote access turned off. All above tests with remote access off were exhibiting same behavior as remote access on.

Summary

Discuss above points with your team and decide if things that will break as mentioned in this blog is required for your team and decide further.

In my case, many of our database servers has Logshipping and Linked servers configured. We had issues atleast on Logshipping as we make use of LSAlert and SSMS “Transaction Log Shipping Status” report day in and out. Hence, we provided this justification and left remote access on.

If there are any other features that make use of Remote Access which I have missed in my post. Please advise those in comment section.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Finding a table name from a page ID

-> Consider you are working on a blocking issue where the application team is advising that the blocking is highly intermittent.

-> Application team is advising that they have a third party monitoring solution and that shows that the blocking is happening on a page and below is the detail,

-> We need to find what Table this Page belongs to. We can utilize DMV sys.dm_db_page_info to get this info. Below query can exactly provide tis,

select DB_NAME(database_id) DBName,page_id,OBJECT_NAME(object_id) TableName from sys.dm_db_page_info (8,1,10029,DEFAULT)

--select DB_NAME(database_id) DBName,page_id,OBJECT_NAME(object_id) TableName from sys.dm_db_page_info (DBID,FileID,PAGEID,MODE)

-> Please note that DMV sys.dm_db_page_info is only available in SQL Server 2019 and above.

-> If you are below SQL Server 2019, then you will need to use DBCC PAGE to get the details of table name from Page id. But DBCC PAGE is an undocumented command.

Hope this helps!!!

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Dedicated Admin Connection (DAC) – The Good Samaritan

-> Users can use DAC connection to troubleshoot issues by executing diagnostics queries when normal connections to SQL Server Instance is not working.

-> I used DAC connection recently to solve a blocking issue. Application upgrade was performed and it was running for close to 13 hours. Application team advised that the upgrade is hung since it is not able to make any connections to SQL server. We tried connecting to SQL Server and we were not able to make a connection to SQL Server. I used DAC connection to this SQL Server instance and was able to identify blocking issues involving several processes. I terminated the head blocker and the application upgrade continued without issues and completed. Without DAC, only option would be to restart SQL Server and that would have been very bad as the application upgrade was more than 95% complete.

Enable DAC

-> Lets check if DAC is enabled on the SQL Server Instance.

-> It is not enabled. Lets enable it using below query.


sp_configure 'remote admin connections',1
RECONFIGURE

Connect to SQL Server Instance using DAC via SQLCMD

-> Below query can be used to connect to SQL Server instance using DAC,

sqlcmd -SDESKTOP-H3PO3LJ\IN2019 -E -dmaster -A

-> When executed, I got below error,

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

-> SQL server browser services is not running. Alternatively you can provide the port number directly. Starting the browser services allowed me to connect to SQL Server using DAC,

Connect to SQL Server Instance using DAC via SQL Server Management Studio

-> DAC connection cannot be made using Object Explorer on SQL Server Management studio. You will receive below error,

TITLE: Connect to Server
Cannot connect to ADMIN:DESKTOP-H3PO3LJ\IN2019.
ADDITIONAL INFORMATION:
Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

-> But you can open a single query window and then connect using DAC as below,

How many DAC Connections are possible on a Single SQL Server Instance

-> Lets try connecting to DAC one more time from a different command prompt,

-> You may get below error in Command Prompt when trying to connect to SQL Server using SQLCMD,

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: An existing connection was forcibly closed by the remote host..
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection due to prelogin failure.

-> You may get below error in SQL Server Management Studio ,

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – An established connection was aborted by the software in your host machine.) (Microsoft SQL Server, Error: 10053)

An established connection was aborted by the software in your host machine

-> I get below error on Application log within Eventvwr,

Log Name: Application
Source: MSSQL$IN2019
Date: 08-04-2021 21:05:58
Event ID: 17810
Task Category: Logon
Level: Error
Keywords: Classic
User: N/A
Computer: DESKTOP-H3PO3LJ
Description:
Could not connect because the maximum number of ‘1’ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

-> So it is clear we can have only one DAC connection on a SQL Server Instance.

-> This issue happens so frequently on production environment where someone else has used the DAC connection but forgot to close it after use.

Which User Session is using DAC connection

-> You can use below query to check who has taken the DAC connection,

SELECT syssession.login_name, syssession.session_id, syssession.status, sysend.name
from sys.endpoints as sysend join sys.dm_exec_sessions syssession on sysend.endpoint_id=syssession.endpoint_id
where sysend.name like 'Dedicated%'

Final Thoughts

-> It is recommended to enable DAC on all SQL Server Instances. It is sure to help during emergency.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.