Windows Firewall, SQL Windows Authentication, Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

1) Use FQDN on the DB Server Instead of IP. If server is not domain based, then add a host entry in ETC\Hosts file and then use the same instead of IP

If that doesn't work, additionally try below

1) Open Windows Firewall on SQL Server and add a rule to unblock inbound port 1433
2) Enable IPV6 in NIC properties of SQL Server.

SQL Best Practices for SharePoint

Select Latin1_General_CI_AS_KS_WS Collation for Database Engine 

Remember to set Latin1_General_CI_AS_KS_WS collation,  when you install DB server for your Sharepoint 2010 and Sharepoint 2013 infra.

CI - Case-insensitive
AS - Accent-sensitive
KS - Kana-sensitive
WS - Width-sensitive

If SQL is already installed, or you are planning to make use of an existing SQL Cluster, please be sure to check collation by opening Properties of DB instance from SQL Management Studio.

MS KB here for reference -: https://support.microsoft.com/en-us/kb/2008668

Change Default collection to Latin1_General_CI_AS_KS_WS , if SQL Server was installed.

1) Stop all SQL Services

2)  Open CMD

3) CD "Program Files\Microsoft SQL Server\<SQL Server Folder for the version installed>\MSSQL\Binn"

4) sqlservr -m -T4022 -T3659 -s"<SQLInstanceName>" -q"Latin1_General_CI_AS_KS_WS"    If only once instance is running, then -s is not required and command will be like the below one
    sqlservr -m -T4022 -T3659 -q"Latin1_General_CI_AS_KS_WS "


5) Once Done, Start SQL services and check Instance Properties to confirm the change.
 
Maximum Degree of Parallelism in SQL  

In SharePoint 2010, setting Maximum Degree of Parallelism option was recommended to be set as 1 , but is was optional.
But, in case of SharePoint 2013, it is required to set this to 1 otherwise the configuration wizard will fail.

SQL 2000 Service not starting after P2V / VMware tool Install or Update - SQL 2000 - An error 1053 - (the service did not respond to the start or control request in a timely fashion) occurred while performing this service operation on the MSSQLServer service

SQL 2000 Service not starting after P2V / VMware tool Install or Update -

An error 1053 - (the service did not respond to the start or control request in a timely fashion) occurred while performing this service operation on the MSSQLServer service.

Error 1053: The service did not respond to the start or control request in a timely fashion.

I have faced this issue a couple of times. When you get this error, usually you need to check for Service Accounts, try to turn on "Interact With Desktop" etc.

But in this case,

1) Service was working fine on a physical server. After P2V, it is not starting on VM

2) Service was working fine on a VM, and it failed after recent VMware tool upgrade/change. 

Fix

All that you need to do is to make sure that you have the below two DLLs 

msvcr71.dll 
msvcp71.dll

They should be be present under %systemroot%\system32 

If not, you can copy these DLLs from another server running same SQL version, or restore them from a backup. It is noticed that VMware tools sometimes mess up these two DLLs, which make SQL to fail. 

All the best! 

R.Hari

A network-related or instance-specific error occurred while establishing a connection to SQL Server

Error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Fix-:

The error refers that the SQL Remote connection is not working.

1) Check if SQL Server and SQL services are up and running. From the server where SQL is installed, try to connect to SQL using SQL Management Studio. 

2) If that works, from a remote server, try to connect to SQL Server/SQL instance using SQL Server Management Studio. 

You might get this error - i my case, SQL is SQL 2005

Here is what you have to do......

If you are running SQL 2005

a) Check if "SQL Server Browser" server is running. Start it if it is now.

b) Open "SQL Server Surface Area Configuration" and check if both 

    > Click "Surface Area Configuration for Services and Connections"

    > Click on "Remote Connections"

    > Local and Remote Connection should be enabled

c) Check SQL Server Configuration Manager for TCP/IP and Port configurations

If you are running SQL 2008 or SQL 2012

a) Check if "SQL Server Browser" server is running. Start it if it is now.

b) Check SQL Server Configuration Manager for TCP/IP and Port configurations

R.Hari.

SQL 2012 Upgrade - "Rule 'Previous Upgrade' failed." Error

SQL upgrade to SQL 2012 had failed at the first attempt and it gives error- "Rule 'Previous Upgrade' failed. A previous upgrade that failed was detected. To retry the upgrade, remove the upgraded feature that failed, and then run the upgrade again."  -on re-attempt.

Fix-1 - correct and the best way

1) Open a command prompt and navigate to 2012 install directory
For me it is D:\SQL2012Sp1\
2) Run the command “setup.exe /Action:RunDiscovery” as per the screenshot below

3) It opens the report in a web page like the one below

4) So here it shows
Database Engine Services = Version 11.0.2100.60
Full-Text and Semantic Extractions for Search = Version 11.0.2100.60
Installed but same time they do exist in Windows 2008 also because last upgrade failed.

So we need to un-install these two before we attempt to upgrade Windows 2008 again.
You can do it via below mentioned commands or using the GUI un-install window.

5) Setup.exe /Action=Uninstall /Instanceid=MSSQL11.MSSQLSERVER /Features=SQL /Q
Make sure that you change /Incanceid and /Features based on your Discovery Report.
In the command above command “/Features  = SQL” will remove Engine, Replication, Full Text and Data Quality this is what I had to run as Database Engine and Full-Text Search are the once listed in my Discovery Report.
You can use like “/FEATURES=SQL,AS,RS,IS,Tools” if you also have SSAS, SSRS, SSIS or SQL Tools listed in the discovery report.
6) Once it is removed, run the discovery report again to make sure that the 2012 features are completely removed and run the upgrade again.

Fix-2 (If Fix 1 is't helpful)

Here is what you have to do

1) Open Regedit
2) Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall
3) Check though the Keys and look for "SQL Server 2012 Database Engine Service"

4) Once found, match "DisplayVersion" and "InstanceId" with the discovery report to double check.
5) Once that's confirmed, open "ModifyPath" and copy the value to a note pad

6) Add /X in the string
Note: /X is for uninstall
7) Open a command prompt and run the command

8) Yes to confirm and uninstall the product.

I my example, have to uninstall Full-Text Search also so,

1) Refresh the regedit screen and you will not be able to see "SQL Server 2012 Database Engine Service" now
2) I have to look for "SQL Server 2012 Full text search" and i found it here

3) I have copied the Modify path and ran the uninstall command with /X switch to uninstall Full Text search also.

That's it!
Now we should be able to run 2012 upgrade again.

Good Luck!

R.Hari.

SQL 2005 Cluster Full Text Search Resource erorr

Generic service 'SQL Server Fulltext (MSSQL2K5)' could not be brought online (with error '1075') during an attempt to start the service.  Possible cause: the specified service parameters might be invalid.

=======================================================================
Fix1
1) Assuming that Node A is now active and Node B is passive
2) Install SP4 on Node A (Active Node First) and reboot it. Resources will fail over to Node B now.
3) One Node A comes back, move resources to Node A, and then install SP4 on Node B and reboot.
4) Once it comes back, check if the issue is resolved.
5) If not Follow Fix2

Fix2
1) Assuming Node A is Active, Open Regedit on Node B (Passive Node)  and follow below mentioned Reg Changes (Screenshot below)
Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSFTESQL$InstanceName\
Multi-String Value: DependsOnService
Value Changed from …
NTLMSSP
RPCSS
to
RPCSS
(Delete NTLMSSP)

2) Restart Node B
3) Once Node B comes back, Fail Over resources to Node B and try to start the Full Test Search Cluster resource. You should be able to start it now.
4) Do the Same Reg Change in NodeA (Passive now) and reboot that too 


R.hari

SQL 2005 Integration Service Installation Error

Failed to install and configure assemblies C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -xxxxx
Error message: Unspecified error

Error HRESULT E_FAIL has been returned from a call to a COM component

 

 

--------------------------------------------------------------------------------------------------------------------------

 

Fix

 

If you  getting this error in between installing a cluster, you can skip it and install SSIS on the node where you are getting

 

On the node where you have issue -

 

1) Deleting MSMQTask COM+ Components from Component Services:
- Open: Start\Programs\Administrative Tools\Components Services,
- Expand "Component Services" -> "My Computer" -> "COM+ Applications",
- You'll see "Microsoft.SqlServer.MSMQTask" component. Right click on it and Delete it.
- If that dialog box is open which poped up during SQL Server Setup then click Retry, otherwise start over and this problem will be gone.

Try to restart the installation

 

2) If that dosent work - ignore the error and install SSIS

3) Reboot Server

4) Once it comes back, un-install SSIS

5) Reboot the Server

6) Install SSIS again this time it shoud get installed with out issues!

 

R.hari

SQL 2005 Cluster Installation Issue on Windows 2008 R2 Failover Clueter

SQLSetup0012_XXXX_Core(Local) Error
Error: Action "InstallDTSAction.11" failed during execution.  Error information reported during run:
Action: "InstallDTSAction.11" will be marked as failed due to the following condition:
Condition "Package "11" either passed when it was last installed, or it has not been executed yet" returned false. Condition context:
Prereq package will be failed due to the previous installation attempt returning: 1602
Installation of package: "sqlsupport"  failed due to a precondition.

SQLSetup0012_XXXX_Core Error
Error: Action "LaunchLocalBootstrapAction" threw an exception during execution.  Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe" finished and returned: 1602
Aborting queue processing as nested installer has completed
Message pump returning: 1602

=========================================================================

Fix

1. Make sure task scheduler service is running on Remote Server
2. Make sure Remote registry service is runnning on Remote Server
3. Try if you can access \\<RemoteServerHostname>\C$ and \\<RemoteServerHostname>\Admin$

and if all looks good login to remote computer

1. Click Start, click Run,
2. Type secpol.msc and then click OK.
2. Under Computer Configuration -> Windows Settings -> Security Settings ->  Local Policies Click on Security Options.
3. In the Policy pane, right-click Network access: Do not allow storage of credentials or .NET Passports for network authentication, click Properties, click to select Disabled, and then click OK.
4. Click Start, click Run,
5. Type "Gpupdate /force" with out quots and then click OK.

R.hari

SQL Script to find instance name

DECLARE @GetInstances TABLE 

( Value nvarchar(100),  

  InstanceNames nvarchar(100),  

  Data nvarchar(100))  

Insert into @GetInstances 

EXECUTE xp_regread   

    @rootkey = 'HKEY_LOCAL_MACHINE',   

    @key = 'SOFTWARE\Microsoft\Microsoft SQL Server',   

    @value_name = 'InstalledInstances'  

Select InstanceNames from @GetInstances

How to use DTCPing and DTCTester? Difference between “DTCTester and DTCPing”. “How DTCPing and DTCTester work”

 The basic difference between DTCPing and DTCTester is as follows:

1. DTCPing need to be started on both the machines for testing communication between them.

2. You don’t need to give password in DTCPing whereas you type password on DTCTester. The password is visible to others on DTCTester so be careful.

3. You need to configure ODBC connection while using DTCTester whereas there is no need to configure ODBC connection while using DTCTester.

4) DTC Ping tests connectivity issues due to firewall and RPC, where as DTCTester can even connect to the DTC and commit transactions to test its working. So DTCTester can also find configuration and Authentication problems

DTCPing Download Link: http://download.microsoft.com/download/b/8/8/b8841bfc-8bd3-4fea-a5f5-06e1f162bd9a/dtctest.exe

DTCTester Download Link: http://www.microsoft.com/en-us/download/details.aspx?id=2868 

How to run DTCPing:

1. Login to m\c A and run DTCPing.exe.

2. Login to m\c B and run DTCPing.exe.

Screen Shot:1

3. On m\c A, type the NetBIOS name of m\c B, and then click Ping.

4. On m\c B, type the NetBIOS name of m\c A, and then click Ping.

5. The log is created and the file name is displayed on the DTCPing screen as follows:

The DTCPing performs following functions:

1. Tests name resolution.

2. Tests Remote Procedure Call (RPC) communication.

3. Detects and display all the registry key settings touched by MS DTC.

4. DTCPing Tests DTC communication between the hosts.

5. Logs the connection communication in the log file.

How to use DTCTester:

This is explained as follows using screenshots. To use DTCTester you need to configure ODBC and then run a simple command. Go through the screenshots step by step.

1.         Screen Shot:1

2.        User 'User DSN' click Add and then select SQL Server- Finish.

Screen Shot:2

3.        Type in a Name for the DSN, Tuition for Example.. a description

             Now, most importent: Type in your SQL Server name / Instance name

In the scrren shot below, TuitionSQL is my SQL server name and SQL2008 is the instance name. 
Click here for SQL Script that will display Instance name.

Screen Shot:3

4.       Use Below mentioned settings if you are using Windows Authentication in SQL, else you can set it to SQL authentication.

        Screen Shot:4

5.        Screen Shot:5

6.         Screen Shot:6

7.        Once the ODBC connection is configured and tested, you can use DTCTester tool for testing the connectivity to MSDTC.

Command: dtctester <dsn name><user name><password>

If you are extracting DTCTester tool to C:\Dtc folder, and using SQL authentication with SA with password Welcome@123 , you need to run the following command

C:\Documents and Settings>c:

C:\Documents and Settings>cd\

C:\>cd Dtc

C:\Dtc> dtctester Tution sa Welcome@123

Screen Shot:7

Issue with Incoming Caller Authentication for MS DTC on Windows 2008 and Windows 2008 R2 Cluster-

Errors: Issues like,

a) Local transactions will get committed, but network transactions will be aborted.

b) MSDTC Trace when extracted using TraceFmt.exe (or msdtcvtr.bat) showing

eventid=RECEIVED_ABORT_REQUEST_FROM_BEGINNER  

(How to read MSDTC Trace)

c) DTCPing giving RPC Errors (How to use DTCPing)

d) DTCTester giving errors like (How to use DTCTester)

SQLSTATE=25S12,Native error=-2147168242,msg='[Microsoft][ODBC SQL Server Driver] Distributed transaction error'
SQLSTATE=24000,Native error=0,msg=[Microsoft][ODBC SQL Server Driver]Invalid cursor state

SQLSTATE=37000,Native error=8509,msg='[Microsoft][ODBC SQL Server Driver][SQL S
rver]Import of Microsoft Distributed Transaction Coordinator (MS DTC) transacti
n failed: 0x8004d00e(failed to retrieve text for this error. Reason: 15100).'

Fix1: Check Firewall

1) Check if Windows firewall is disabled, if you are using it, makesure that MSDTC is in exception list.

Ref:: http://technet.microsoft.com/en-us/library/cc725913(v=ws.10).aspx

2)  If the cluster node and the remote system accessing it are in different network and seperated with a fireall, then makesure that the MSDTC ports are enabled.
Ref:: http://www.lewisroberts.com/2009/08/16/msdtc-through-a-firewall-to-an-sql-cluster-with-rpc/

Fix2: NetBios names

Check if the remote host is able to ping SQL Cluster NetBios name, Node1 NetBios name and Node2 NetBios name. If not add NetBios name and IP address in C:\system32\drivers\etc\hosts file to enable it.

Sameway try to ping Remote Host NetBios name from Node1 and Node2, if not pinging, then make respective entries in C:\system32\drivers\etc\hosts file.

Fix3: Check if network DTC is enabled and Authentication mehod is set correct.

Open "Component Services" MMC, you can find this MSDTC instance on the left tree. Right click the clustered MSDTC instance and select "Properties", then check the following items.

The MSDTC transaction mode must be set to either No Authentication Required or Incoming Caller Authentication Required to function correctly on a Windows Server cluster. Incoming Caller Authentication Required is the recommended option because this option is more secure.

Fix4: Check SQL Cluster Resources 

1)  Check MSDTC resource and makesure that the dependency is set to Cluster Group Name and MSDTC disk.



2) Open the Properties page for the Name resource which is consifured as a dependency on MSDTC in this case -the name CLUS1Dtc - and check Resource Display Name, here in the below mentioned example, it is MSDTC Network Name (Dev01). Same time the DNS Name is CLUS1Dtc.

3) To use Incoming Caller Authentication Required for MS DTC on a Windows 2008 or R2 cluster, the display name of the network name resource for MS DTC must be same as its DNS name.

4) The following command will change the display name of the server network name resource from MSDTC Network Name (Dev01) to CLUS1Dtc:

Cluster.exe res “MSDTC Network Name (Dev01)” /ren:CLUS1Dtc

5) Check properties again and confim the name change.

6) Restart the cluster group and test MSDTC transactions again....

----------------------------------------

R Hari