SQL connectivity issues troubleshooting Steps

As a DBA you may have seen\trying to fix connectivity issues of SQL. So, I thought to put some points here on resolving the connectivity issues to SQL Server, based on my experience I have given below basic steps to follow Below are the points I use to check when you failed to connect SQL Server :

  1. SQL Server configuration Issue
  2. Firewall Issue
  3. Network Issue
  4. Logon Issue
  5. Configuration Issue in Application

SQL Server configuration Issue :
–> Check SQL Server is running on the Machine and is listening on protocols.
–> You can check and enable protocols in SQL Server Configuration Manager.
–> SQL Server to connect remotely Named Pipes and TCP protocols are reQuired, make sure these are enabled
–> In SQL Server Errorlog we can check if server is listening on protocols or not.
–> Also make sure SQL Server Browser is running on the Machine is SQL Instance is a Named Instance.

Firewall Issue:
–> SQL connectivity reQuest can be blocked by Firewall between Client and Server.
–> To fix this we can DISABLE Firewall but it is Temperory Fix as in real time we can disable it Permanentely.
–> The Permanent fix for this is adding SQL Server and SQL Browser in Firewall Exception.
–> While adding the exception for SQL Server DB Engine provide the Port Number in which SQL is listening.
–> While adding exception for SQL Browser provide the UDP port 1434.

Network Issue :
–> The first thing to check for SQL connectivity is Ping response from your SOurce Server to Target Server.
–> You can also do Telnet ‘SQL ServerName’,PortNumber and UDL testing from client machine to SQL Server box.
–> check nslooup, if DNS entry is wrong that may cause the SQL connectivity issue.

logon Issue to SQL Server:
–> This is different from above all cases..This needs to troubleshoot based on the error message which we get from Application team.
–> As it is Login Connectivity issue to SQL, we have to study case by case.
–> If the Login reQuest is coming to SQL box and getting failed then the errors can be recorded in SQL ErrorLogs and event viewer of SQL box.
–> We can go through the logs and fix the issue.

Configuration Issue in Application :
–> If the configuration part in application is wrong then that also can lead to SQL connectivity Issue.
–> Check the connection String Parameters Provided in Application Config File.
–> Also check which Login Account Application is using to connect SQL
–> If it is SQL account check the SQL instance properties which Authentication Mode is Enabled.
–> And check what level of Permissions that Account has.

SQL Profiler Trace Steps to Capture Query Info

We may get the situation where we need to run SQL Profiler Trace to capture information when Query is running on an Instance. We all know that running Profiler on a Production will impact the performance but still sometimes its necessary . Instead of selecting default or all options to run profiler, Please use the following selected options which are sufficient to troubleshoot the Issue which will have little performance effect :

  • Run the SQL Server Profiler
  • Click File -> New Trace
  • Input the server name and click Connect
  • Check “Save to file” and specify the file to store the SQL profiler log
  • In the Events Selection tab, click “Show all columns”
  • Drag the scroll bar and select the items under the column DatabaseName. Make all the items under DatabaseName Checked.
  • Click Show all Events,

–> Expand Errors and Warnings, Select the following items: Attention, Exception, Errorlog, Eventlog, User Error Message
–> Expand Stored Procedures, select the following items: RPC: Completed, RPC: Starting, SP: Completed, SP:Starting, SP: StmtStarting
–> Expand TSQL, select the following item: SQL: BatchCompleted, SQL: BatchStarting; SQL: StmtStarting

  • Click Run
  • Wait for 5 minutes.
  • In the SQL Profile, click File -> Stop Trace

Multiple Listener Creation

We all know that only ONE Availability Group Listener can be created for Availability Group , if we tried to create another Listener using SSMS we will get below error :

As per the above error we can’t create another Listener in an Existing Availability Group.

But sometimes having Multiple Listeners will help when performing Migrations. So, my todays post is how to create Another Listener on Existing Availability Group .

Creating another Listener is not supported by Microsoft that’s the reason we are not able create using SSMS and its undocumented. This can be approached through Failover Cluster Manager.

open your WSFC–> expand your Cluster –>click on Roles–>on Right Pane select the Availability Group –> down at User Setting click on RESOURCES TAB to see existing Listener we have

To create new Listener on the existing Availability Group go t hrough below steps :

Now in Roles on Right pane–>Select your Availability Group for which you want to create another Listener–>Right click on the Availability Group–>go to Add Resources –> click on ‘ Client Access Point

After clicking on Client Access Point–> A Pop Window ‘ New Resource Wizard ‘ will be opened–> Provide your New Listener Name in Name Field–>Also Provide the New Listener IP in ADDRESS Field–> click on NEXT

Your New Listener AGL1 will get created and it will be in OFFLINE Status , right click on AGL1 and click BRING ONLINE as shown below :

As I configured Multisubnet Failover Cluster seeing two IP’s for Listener.

Once new Listener was created –>Right click on Availability Group under User Settings–> AG Properties Pop window opens–> select DEPENDENCIES TAB–>Enter your New Listener Name on Resource Field and Select OR option as shown like below :

Once above all steps are completed we can see your New Listener in SSMS now :

But if you try connecting with New Listener Name AGL1 it will fail as we have not provided the PORT info in above steps .So, go to your New Listener AGL1 Properties in SSMS –> enter the PORT Number –>click OK

Now try connecting with New Listener Name AGL1 and it will get successfully connected.

Listener Creation Fails due to Lack of Permission Issue

This post is continuation of my previous Availability Group Listener creation post

If the domain Administrator is not allowing the Permission to create and read computer Objects then we can use this method for successful creation of Availability Group Listener.

Here we are creating the VCO ( which is Virtual Computer Object ) manually in Active Directory as we don’t have permission to create computer objects Automatically.

For doing this make sure the user who logged in has a permission to create computer objects in domain.

  • Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
  • Click on VIEW and select ‘ Advanced Features 
  • Locate your container where the listener is getting created, in my case it will locate on COMPUTER as shown in below

Now right click on the container where VCO needs to get created in my case it is in COMPUTERS –>click on NEW–>Click on Computer

Here we are creating the Listener Object in COMPUTERS Container

A Pop window ‘ New-Object Computer ‘ will be opened to provide your Listener Name –> Enter your Listener Name in Computer Name Field (AGL)–> click OK.

Now right click on the created VCO which is AGL in my case –click on Properties–>Click ADD button–>enter your cluster name ( in my case AWON$)–>click on Object types–>Enable check box for Computers–> click OK –>Again click OK

Now click on the Cluster Name ( AWON ) and see the permissions below and then click OK.

Allowed To Authenticate
Change Password
Receive As
Reset Password
Send As
Validate write To DNS Host Name
Validate Write To Service Principle Name
Read Account Restrictions
Write Account Restrictions
Read DNS Host Name Attributes
Read MS-TS-GatewayAccess
Read Personal Information
Read Public Information

So, now Virtual Computer Object which is Listener is Created in Active Directory Domain.

Now try to create Listener Using SQL Server Management Studio(SSMS) and it will get created successfully without any Issue.

Availability Group Listener Creation Fails because of CNO Permission

The most common issue we face when creating Availability Group Listner in SQL Server using SQL Server Management Studio(SSMS)

Below is the Error Screenshot we get :

To conclude that the above error is for CNO Permission Issue, please generate the cluster logs on your Primary replica and go through the logs , once it confirms the issue is with Lack of CNO Permission in Active Directory to read and create computer objects for Listener

CNO is nothing but Cluster Name Object which is a Cluster Name in my case it is AWON

To resolve CNO Permission Issue follow below instructions:

To fix we have to GRANT below 2 permissions to CNO :

  1. Read all Properties
  2. Create Computer Objects

For providing above permissions proceed with given steps:

  • Open Active Directory Users and Computers ( shortcut to open from powershell : dsa.msc )
  • Click on VIEW and select ‘ Advanced Features
  • Locate your container where the listener is getting created, in my case it will locate on COMPUTER as shown in below
  • select computer–> right click –>Properties
  • Now click on ADD button and enter your CNO name. In my case it is AWON$
  • Now click on object types and enable check box COMPUTERS then click on OK ->OK
  • And now you will be back to Computer Properties TAB, click on ADVANCED TAB –>Advanced Security Settings for computers Pop window will be opened–>Select your CNO ( Mine is AWON ) from Permission Entries –> Click on EDIT button
  • A pop window ‘ Permission entry for Computers ‘ will be opened , now Enable check boxes for ‘ Read all Properties ‘ and ‘ Create Computer Objects ‘ –>click OK –> Again click OK –> click Apply –> click OK as shown below :

Now try again to create Listener and it will get created without any issue.