Availability Group Listeners Creation Issue

I must admit, that I was facing this issue since a while. I was trying to setup a lab to have a hands on with AlwaysOn feature; however the Availability Group Listeners creation was failing. As I started looking into the SQL Server error log & observed the below error message.

Error: 19476, Severity: 16, State: 4.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect
. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.

From the System event log, below error was reported:

Log Name:      System
Source:        Microsoft-Windows-FailoverClustering
Event ID:      1194
Task Category: Network Name Resource
Level:         Error
User:          SYSTEM
Computer:      NODE01.SKGLAB.LOCAL
Cluster network name resource ‘AGDemoTest_AGDemoTest’ failed to create its associated computer object in domain ‘SKGLAB.LOCAL’ during: Resource online.
The text for the associated error code is: A constraint violation occurred.

Please work with your domain administrator to ensure that:
– The cluster identity ‘WINCLUSTER01$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘WINCLUSTER01$’.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘WINCLUSTER01$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

While I was reading the above error message, the highlighted text (in blue) caught my eyes. I remembered that, if the virtual computer object was not prestaged, by default, it used to create the computer object in the default Computers container in AD (till Windows Server 2008 R2). A little search over internet points me to a post on the PFE Platform blog, which states that starting Windows server 2012 (& onwards), all the virtual computer objects are created in the same OU, where the cluster computer object resides. So in my case, the cluster computer object was unable to create the virtual computer object in the same OU due to lack of permission, which in turns failing the Availability Group Listener creation . As suggested in the above reference post, after providing the necessary permission, I was able to create the availability group listener.

Hope, this may help someone. Happy Learning Smile !!!

Posted in AlwaysOn, How To, Just Learned, Setup LAB, SQL Server, SQL Server Configuration, SQL Server HA, SQL2012, TestLab | Tagged , , , , , , | Leave a comment

Removing a SPN Entry

At times, we may require to remove a wrongly created SPN entry. The syntax for removing a SPN entry is:

setspn.exe -D “SPN entry, which needs to be removed” “Service Account or Server Name”

Over the weekend, I was working on my lab to simulate an issue, while I observed that the SPN registration was failing on one of my test server. To fix the issue, I had to remove the SPN entry. I used the below command to remove the SPN entry as it was configured for the SQL server service account.

setspn.exe -D “MSSQLSvc/KRISHNA03.SKGLAB.LOCAL:1433” “SKGLAB\SQLSvc-SQL2012”

Happy Learning :) !!!

Posted in SQL Server | Tagged , , , , , | Leave a comment

SQL Server is consuming more than 17000 handles !!!

Well, these days, this is a topic of discussion most of the time & when this evening I encountered this again, I thought of writing this post for my future reference. While analysing a slow performance issue on a server, a Windows colleague reported that ‘SQLServr.exe is using more than 17000 handles which is causing the server slowness. I am sure most of us, have faced this issue at some point in time.

Over the past few years, I refer to the following blog post, which provides a nice explanation around troubleshooting handle leak issue. According to the above reference post, SQL Server service may consumes 30,000 handles on a 32-bit platform while the exception limit is 50,000 handles on a 64-bit platform. Apart from that, the post also demonstrate how to identify such problems using Sysinternals tools. As always, I have shared the reference article with my colleague and informed him that the SQL Server handle usage is within limit & suggested him to look into other area. Hope, we’ll get a fix for this issue Winking smile.

Happy Learning Smile !!!

Posted in How To, SQL Server | Tagged , | Leave a comment

CHECKDB Failed with 7926 error

During this weekend, I was working on a weekly maintenance job failure issue, where I came across the error 7926. This was the first time I encountered this error. The below error was reported in the job output file:

Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.

From the above error message, I was suspecting that the data drive was running low on space. However, a search on the internet, refer me to the support article https://support.microsoft.com/en-us/kb/928518, which states that the above error can occurred due to two reasons:
1. Another connection exist to the database against which we are running CHECKDB command.
2. The database contains at least one file group that is marked as Read-Only.

However in my case, the database was not having a read-only file group; but the drive, which hosts the data files for the database was running out of free space. Also there were existing connections from the application.

Raised a request for additional storage for the data drive. Hope, that will fix this issue.

Happy Learning Smile !!!

Posted in DatabaseSnapshot, Jobs, Just Learned, SQL Server | Tagged , , , | Leave a comment

SPN Registration Failure Issue

While I was configuring my lab machine, I observed the SPN registration was failed on one of my server with the below error.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/KRISHNA02.SKGLAB.LOCAL ] for the SQL Server service. Windows return code: 0x2098, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos.

Over the last one year, I had worked multiple times on this issue. So when this occurs on my lab machine, I thought of documenting the same. Prior installing SQL Server on my machine, I had created the SPN using the SETSPN tool; however it seems the registration didn’t work as expected. To begin with, I wanted to check the SPN’s currently registered with the SQL Server service account, by executing the command: setspn -l “SKGLAB\SQLSvc-KRISHNA02”


From the above screenshot, I found the SPN are registered with the service account. As a next step, I need to check whether the SQL Server service account is allowed to register & un-register the SPN’s in active directory, which is not a default behaviour.

Steps to Follow

Follow the steps mentioned below to allow the SQL Server service account to register the SPN automatically.

  • Open Active Directory Users and Computers, clicks on Views, select Advanced Features.
  • Open the properties of the service account, & go to Security tab.


  • Click on Advanced tab, click on Add. On the new permission page, select Principal as “Self”, and set Type as “Allow” & Applies to “This object only”, as shown in the below screenshot.


  • From the list of properties available below, select the two properties high lighted below & click Ok twice of apply these properties.

a. Read msDS-PrincipalName
b. Write msDS-PrincipalName

  • Stop the SQL Server service & start it again. Open the SQL Server error log, & verify the status of SPN Registration.

2015-03-26 21:58:27.170    Server    SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2015-03-26 21:58:27.190    Server   
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/KRISHNA02.SKGLAB.LOCAL ] for the SQL Server service.
2015-03-26 21:58:27.190    Server    The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/KRISHNA02.SKGLAB.LOCAL:1433 ] for the SQL Server service.

SPN registration is successful now.

Happy Learning Smile !!!

Posted in Deployment, How To, Security in SQL Server, Setup LAB, SPN, SQL Server | Tagged , , , , | Leave a comment

Setup LAB: 03 Installation of SQL Server 2012

In this post, I will start with the SQL Server 2012 installation. Prior to that, I will create an OU named TestLAB in AD, which will contains all the service accounts, lab users, servers. Apart from that I will also install the management tools for active directory. The below set of scripts will install the AD management tools & create all the OU & sub-OUs.

# Install the Active Directory Tools
Install-WindowsFeature RSAT-ADDS -IncludeAllSubFeature -IncludeManagementTools
# Import Module Active Directory
Import-Module ActiveDirectory
# Create a new OU named TestLAB
New-ADOrganizationalUnit -Name "TestLAB" -Server "Win12DC01" -Verbose
# Create sub-OU ServiceAccounts inside the TestLAB OU
New-ADOrganizationalUnit "ServiceAccounts" -Path "OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Lists all Service Accounts" -Verbose
# Create sub-OU TestServers inside the TestLAB OU
New-ADOrganizationalUnit "TestServers" -Path "OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Lists Test Servers" -Verbose
# Create sub-OU LABUsers inside the TestLAB OU
New-ADOrganizationalUnit "LABUsers" -Path "OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Lists LAB Users" -Verbose

Now that, I have created all the OUs, I am going to create the service accounts for SQL Server installation, along with a group for DBAs & an user account for the SQL Server installation.

# Create a Windows group for the LAB administrators
New-ADGroup -Name "BLRDBA" -Path "OU=LABUsers,OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Members of SQL DBAs" `
-GroupScope DomainLocal -GroupCategory Security -Verbose
# Create a Windows User account
$password = Read-Host ("Enter the password") -AsSecureString
New-ADUser -Name skganguly -GivenName Sudeepta -Surname Ganguly -DisplayName "Sudeepta Ganguly" -SamAccountName "skganguly" `
-Path "OU=LABUsers,OU=TestLAB,DC=SKGLAB,DC=LOCAL" -ChangePasswordAtLogon $false -CannotChangePassword $true `
-Description "MS SQL Server DBA" -PasswordNeverExpires $true -AccountPassword $password -enabled $true -Verbose
# Create a service account for SQL Server 2012 installation
$password = Read-Host ("Enter the password") -AsSecureString
New-ADUser -Name SQLSvc-SQL2012 -GivenName SQLSvc-SQL2012 -Surname SQLSvc-SQL2012 -DisplayName "SQLSvc-SQL2012" -SamAccountName "SQLSvc-SQL2012" `
-Path "OU=ServiceAccounts,OU=TestLAB,DC=SKGLAB,DC=LOCAL" -ChangePasswordAtLogon $false -CannotChangePassword $true `
-Description "SQL Server 2012 Service account" -PasswordNeverExpires $true -AccountPassword $password -enabled $true -Verbose

Configure the VM for SQL Server Installation

Once the VM is started, I have added an additional VHD to the VM, where the SQL binaries will be installed. Added the newly created VM to the SKGLAB domain & added SKGLAB\BLRDBA group as a member of the local administrator group on the server. The user account SKGLAB\skganguly is a member of BLRDBA group. I am going to use this user account to install the SQL Server 2012 on this server.

Configure Local Policies

Open Server Manager, Go to Tools, click on Local Security Policy. Expand Local Policies, select User Rights Assignments. From the list of available policies, add the SQL Server service account to the below two policies as shown in the screenshot.

  • Lock pages in memory
  • Perform Volume Maintenance tasks


Configure SPN for SQL Service Account

Once the local policies are configured, next we are going to configure the SPN for the SQL Server service account. I am going to create the below two SPNs on this server.  



Once the SPN is configured, I can verify it by executing the below command. The desired result is shown in the below screenshot.



Now, I will start the SQL Server 2012 installation.

Installing SQL Server 2012

I am going to use the below script to complete the SQL Server 2012 installation. I have modified a existing configuration file to complete this installation. The SQL Server Media is mounted on the CD-ROM drive of the VM. Update the password for sa & service accounts, and run the below script to start the SQL Server installation.

#  SQL Server 2012 Setup Command  #

# During the installation, the SQL Server media is mounted on Drive D:

# The Base Command for unattended installation of SQL Server 2012

# Define Variables
$SetupLocation = "D:\Setup.exe"
# Provide the Database Engine Service Account Password
$SQLSVCPASSWORD = "xxxxxxxx"
# Provide the SQL Agent Service Account Password
$AGTSVCPASSWORD = "xxxxxxxx"
# Provide the SA Password
$SAPWD = "xxxxxxxx"
$ConfigFileLocation = "c:\TEMP\SQLConfig.ini"

# Change the current folder location to C:\Temp
Set-Location "c:\TEMP"

# To start the SQL Server Installation

The configuration file used during this installation can be found here. The installation of SQL Server 2012 completed successfully. I am going to build two more servers with the same configuration and complete the SQL Server installation.

Happy Learning Smile !!!

Posted in Automation in SQL Server, How To, PowerShell, Setup LAB, SQL Server, SQL2012, TestLab | Tagged , , , , , | Leave a comment

Setup LAB: 02 Install & Configure a Domain Controller

In the previous post, I had installed Windows Server 2012 evaluation on a virtual machine & added Dot Net Framework 3.5. In this post, let’s start with the domain controller installation from a copy of the pre-installed VHD. I had created a VM from the copy of the VHD file, which I created earlier. I have attached a network adapter to the virtual machine & assign it the IP address (with Subnet mask as

Install the Active Directory Domain Services

We can use Add Roles and Features option from Server Manager to add Active Directory Domain Services on this server; however, to simplify the installation process, I am going to use the Windows PowerShell script to complete this installation. This script can be generated as part of AD DS deployment using Add Roles and Features tool.

Right-click on the PowerShell icon, and select Run ISE as Administrator to start the PowerShell ISE.


Copy the below script to a new script window & modify the parameters as required. Once all the required parameters are modified, execute the script by pressing the F5 button on your keyboard.

# To set the script execution as RemoteSigned
Set-ExecutionPolicy RemoteSigned
# Install the Server Role Active Directory Domain Services
Install-WindowsFeature -Name AD-Domain-Services -IncludeAllSubFeature -Restart

# Windows PowerShell script for AD DS Deployment

# Enter your Domain Name
$domainName = "SKGLAB.LOCAL"
# Enter your Domain Netbios Name
$domainNetbiosName = "SKGLAB"

# Domain Functional Level
$domainMode = "Win2012"
# Forest Functional Level
$forestMode = "Win2012"

# Location of AD database file
$databasePath = "C:\Windows\NTDS"
# Location of Log file
$logPath = "C:\Windows\NTDS"
# Location of SysVol
$sysvolPath = "C:\Windows\SYSVOL"

Import-Module ADDSDeployment

Install-ADDSForest -CreateDnsDelegation:$false -DatabasePath $dbLocation `
-DomainMode $domainMode -DomainName $domainName -DomainNetbiosName $domainNetbiosName `
-ForestMode $forestMode -InstallDns:$true -LogPath $logPath -NoRebootOnCompletion:$false `
-SysvolPath $sysvolPath -Force:$true

Once the script is executed, you will prompted to provide the recovery password. The installation will continue & you may see the below screen during the installation.


The server will restart to complete the directory service installation. Once the server is back online, login to the server to configure the DNS server.

Configure the DNS Server

Once the domain controller is installed, we need to configure the DNS server. Open Server Manager & start DNS Manager from the drop down list of tools. We are going to configure the Reverse LookUp Zone, which will provide the resolutions from IP Address to the respective Host name. Once the DNS Manager opens, right click on Reverse LookUp Zone tab, & select New Zone.


Select the zone type as Primary Zone & click Next to continue. On the Active Directory Zone Replication Scope, select the option as shown in the below screenshot.


Select IPv4 Reverse Lookup Zone from the list of Reverse Lookup Zone Name and provide the Network ID as shown in the below screenshot.


On the next page, select the Dynamic Update option & click on finish to complete the DNS configuration.

Create a Pointer Record

Once the reverse lookup zone is configured, let’s create a pointer record (PTR) for the domain controller. Expand the reverse lookup zone node in DNS Manager & select the newly configured zone. Right-click on the zone node & select New Pointer (PTR) as shown in the below screenshot.


Provide the IP Address & browse the Host Name of the domain controller, as shown in the below screenshot.


To check, whether the DNS server is function as required, launch nslookup as shown in the below screenshot. The utility will check the name resolution between hostname & IP address for the domain controller.


In the next post, we will configure the rest of the virtual machines & start with SQL Server 2012 installation.

Happy Learning :) !!!

Posted in How To, PowerShell, Setup LAB, SQL2012, TestLab | Tagged , , , | Leave a comment