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.

Connect to SQL Server with PowerShell and Azure key Vault

-> Requirement will be to query an Azure SQL Database via PowerShell and display the results. We will not list the SQL Server Instance, Username and password as clear text in PowerShell script, instead we use the secret from Azure Key Vault to achieve this.

Create an Azure SQL Database

-> Complete tabs “Networking”, “Security”, “Additional settings” and “Tags” as appropriate.

Create an Azure Key Vault

-> Complete tabs “Access policy”, “Networking” and “Tags” as appropriate.

-> Get into Azure Key Vault “sqlcon-key-vault-eastus”. Click on “Secrets” and then “Generate/Import”,

-> Value as mentioned in above screenshot is as below,

Server=tcp:jbdw.database.windows.net,1433;Initial Catalog=prod-jbsqldb01;Persist Security Info=False;User ID=<username>;Password=<Password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

-> Click Create.

Get the required Data using PowerShell

##Script to display database names available## 
## Version 1.0 
##Author : Vivek Janakiraman## 
##cls ##Clearing Screen 

Connect-AzAccount

##Key in the correct Subscription below##
Set-AzContext -Subscription "00000000-0000-0000-0000-000000000000"

$keyvault = "sqlcon-key-vault-eastus" ##Provide the Azure key Vault name##
$ConnectionString = "prod-jbsqldb01-connection-string" ##Provide the the name of the secret created within Azure key Vault##


$Vault = get-azkeyvault -VaultName $keyvault
$Secret = get-azkeyvaultsecret -Name $ConnectionString -VaultName $keyvault
$SecretValue = $Secret.SecretValue 
$ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecretValue))

$tsqlcommand = "select name from sys.databases"; 

#SQL Connection - connection to SQL server 
$sqlConnection = new-object System.Data.SqlClient.SqlConnection; 
$sqlConnection.ConnectionString = $connectionString; 

#SQL Command - set up the SQL call 
$sqlCommand = New-Object System.Data.SqlClient.SqlCommand; 
$sqlCommand.Connection = $sqlConnection; 
$sqlCommand.CommandText = $tsqlcommand; 

#SQL Adapter - get the results using the SQL Command 
$sqlAdapter = new-object System.Data.SqlClient.SqlDataAdapter 
$sqlAdapter.SelectCommand = $sqlCommand 
$dataSet = new-object System.Data.Dataset 
$recordCount = $sqlAdapter.Fill($dataSet) 

#Close SQL Connection 
$sqlConnection.Close(); 

#Get single table from dataset 
$data = $dataSet.Tables[0] 
$data

-> Below is the output,

-> There is a possibility of below error while executing the script,

Exception calling "SecureStringToBSTR" with "1" argument(s): "Value cannot be null.
Parameter name: s"
At C:\temp\Connection_Template_Working.ps1:16 char:1
+ $ConnectionString = [System.Runtime.InteropServices.Marshal]::PtrToSt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
 
Exception setting "ConnectionString": "Format of the initialization string does not conform to specification starting at index 0."
At C:\temp\Connection_Template_Working.ps1:22 char:1
+ $sqlConnection.ConnectionString = $connectionString;
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationException
    + FullyQualifiedErrorId : ExceptionWhenSetting
 
Exception calling "Fill" with "1" argument(s): "The ConnectionString property has not been initialized."
At C:\temp\Connection_Template_Working.ps1:33 char:1
+ $recordCount = $sqlAdapter.Fill($dataSet)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

Exception calling “SecureStringToBSTR” with “1” argument(s): “Value cannot be null.

-> This error occurs when parameter $ConnectionString is wrong. It should be same as what is seen in below screenshot in Azure key Vault,

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.

Drop dedicated SQL pools in Azure Synapse Analytics

-> Below is the view of the databases on Azure Synapse Analytics on SQL Server Management Studio ,

-> I was trying to drop jbdw_restore_29March2021 from SQL Server management studio and got below error,

TITLE: Microsoft SQL Server Management Studio
Drop failed for Database ‘jbdw_restore_29March2021’. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.41011.9+(SqlManagementObjects-master-APPLOCAL)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Database&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Statement ‘Drop Database’ is not supported in this version of SQL Server. (Microsoft SQL Server, Error: 104454)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2531&EvtSrc=MSSQLServer&EvtID=104454&LinkId=20476
BUTTONS:
OK

-> Open Azure portal and navigate to required Dedicated SQL pool,

-> On the Overview Tab. Click Delete,

-> Click on “Delete”,

-> Below is the view of the databases on Azure Synapse Analytics on SQL Server Management Studio,

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.